Tag Info

New answers tagged

0

Yes, this is possible. Here is how Create a copy of the table (you can use the partition management tool for that: https://sqlpartitionmgmt.codeplex.com/) Switch the partition you want to "compress" into the copy Rebuild the indexes on the copy with FF = 100. Switch the partition back into the original table It should be pretty trivial to automate this ...


2

Yes, you can install an additional instance of SQL Server 2012 (you'll need a named instance if your 2005 instance is a default), and it can happily co-exist without interfering with your existing 2005 instance. None of your existing databases will be touched, and you will still be able to have both 2005 and 2012 versions of the client tools, too (this isn't ...


1

In my experience, you definitely install many SQL Server versions on the same machine. It depends on your OS which support all versions? You can refer link to understand limitation for each version Thanks,


0

I recently had the need to have SQL 2005, 2008, 2012 and 2014 all installed on the same machine at the same time. I did not notice any ill effect. Except for Win7 complaining about SQL 2005 being a little on the incompatible side of things though it still seemed to work fine for me. Typically, I'd only enable the services of the versions I needed at any ...


1

Unless you have cross-database ownership chaining enabled (off by default) and have the same User in both databases, then giving access to an object in one database does not imply anything permission-wise to other databases. Of course, I am not recommending that cross-database ownership chaining be enabled, and neither is Microsoft. Instead, you should ...


0

The problem came from an insufficient privileges on the server where the Access file is stored. Here's how you should configure your server depending of the login type that you are using: If you are using SQL Server login: Give to the SQL Server Database Engine service account permissions to read and write (depending if you want to make CRUD on that file) ...


2

Following can be the reason behind the error message. The account which user selected on Server Configuration page window ( during installation) is somehow not able to bring SQL Server database engine services online. Either is lacks privilege or it is corrupted. During installation of database engine services SQL Server tries to bring online database ...


3

Strictly speaking (T-SQL subroutine): No. Technically speaking (a means to abstract formulas to be defined once): Yes. Pragmatically speaking: It depends :). Here are the issues that are currently impeding you regarding restrictions on T-SQL functions: They cannot be declared dynamically They cannot access temporary tables They cannot be aggregate ...


3

You need CROSS APPLY not join. The definition of table expressions involved in joins must be stable. I.e. They can't be correlated such that the table expression means something different dependant on the value of a row in another table. select f.ID, f.Desc, u.Field1, u.Field2 from Foo f Cross apply ut_FooFunc(f.ID, 1) u where f.SomeCriterion = ...


3

No this isn't possible. A permanent TVF or view isn't an option due to the reference to #MyTempTable I've seen a connect item request for temporary views and agree sometimes they would be useful. This was closed as duplicate of one requesting Module-level table expressions. You might be able to rewrite as SELECT A, B, C, CASE ...


3

Identical tables, identical columns, identical records Not entirely true. At first they were different, and row 1 was added to [mybit2] when they were different. Your second image, the DBCC PAGE output for [mybit2], clearly indicates this fact. Why different record sizes? Simply put: you changed the table definition and didn't rebuild it. Some ...


4

Thomas Cleberg is correct. The first row you insert into bit2 shows up in DBCC page as 10000500 ff080000 63 This breaks down as follows +-------+-----------------+--------+-------------------------------------------------------+ | Bytes | Hex (LSB order) | Hex | Comments | ...


0

Maybe I am missing something, but you realize that start off with 8 columns, do an insert then alter the table and add a 9th column and then do another insert. Of course the size is going to be different when the first insert was into 8 columns and the second was into 9 columns.


1

Your second table has 8 columns, the first has 9. As the data type is "Bit", that accounts for exactly the difference.


2

The database on the end of log shipping has to be in NORECOVERY to accept the log backups being shipped over. A database involved in AlwaysOn Availability Group has to be removed from the group in order to be restored or set to any restoring state. So setting up log shipping to that database would require it to no longer being replicated. I would go back to ...


4

I imagine you'll probably need a secondary ordering column, as Col1 is not a unique value, however this should get you started. WITH sampledata AS ( SELECT ROW_NUMBER() OVER(ORDER BY Col1) as row_num, ID, Col0, Col1, Col2 FROM Table1 ) SELECT row_num FROM sampledata WHERE ID = 263;


1

The current T-SQL I'm using to evaluate PLE vs max server memory is: /* Purpose: Returns a resultset describing various server level stats including PLE Max and Min Server Memory, etc. By: Max Vernon Date: 2014-12-01 */ SET NOCOUNT ON; /* wait stats for PAGELATCH_IO */ DECLARE ...


2

Hopefully with that size you are on Enterprise edition? If not, good luck :) (just kidding see bottom of answer) If you are on Enterprise, I would suggest looking into partitioning. This has saved my butt a time or two when dealing with this same kind of scenario. Create your partitions on the date field and you will have to best determine how big you want ...


0

Depends a bit on the data access patterns. I assume it's written sequentially. Is that true? How about reads? Sequential or random? If everything is sequential and you've got a robust I/O pipe you'll be fine. If it's random or you have a poor I/O setup you'll run into problems without archiving data off. Mind, you've given no hardware detail so I'm making ...


0

Regarding running reports from an Availability Group secondary server, you will need to license that instance of SQL Server. Since the AG requirements require identical equipment, your secondary's licensing cost will reflect the primary server's licensing cost Using the secondary server strictly for High Availability/Disaster Recovery does not require ...


4

I am unsure if its necessary to add the TABLOCK table hint to an empty temporary table, defined with a clustered index in order to get minimal logging. No. Local temporary tables (#temp) are private to the creating session, so a table lock hint is not required. A table lock hint would be required for a global temporary table (##temp) or a regular table ...


1

The challenge here is that numbers don't take into account end user experience. Great example: I have database server used to track every web site that the company employees visit. I don't care if it can't keep up with inserts during peak loads because the front end app batches the inserts off periodically, and slow inserts don't cause a problem for users. ...


4

You should consider refactoring to use the new paging clause in SQL Server 2012, ( OFFSET...FETCH ), rather than that old-fashioned 'roll your own paging' eg SELECT ... FROM TableC WHERE AccountID = @P3 AND Processed = @P4 AND Deleted = 0 AND Source = @P5 ORDER BY ProcessedDate DESC OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY It's potentially ...


0

USE master GO SELECT s.UseCounts,s.RefCounts, s.Cacheobjtype, s.Objtype, ISNULL(DB_NAME(t.dbid),'ResourceDB') AS DatabaseName, TEXT AS SQL , query_plan FROM sys.dm_exec_cached_plans s CROSS APPLY sys.dm_exec_sql_text(plan_handle) t cross apply sys.dm_exec_query_plan(s.plan_handle) as q where TEXT like '%Your query text (all or part)%' ORDER BY ...


9

I don't believe that result was incorrect at all, your query is ambiguous. Semantically your subquery enumerates all rows and then you pick the top 25 rows without specifying an order by. That means that SQL server is at liberty to return any rows it feels like and those rows do not have to be the rows enumerated 1 to 25. It can be just any random rows and ...


-1

I liked the CPU query from sys.dm_exec_query_stats so much that I extended it. It is still ranked by CPU but I added other totals and percents to get a better server profile. This copies nicely into Excel and with conditional color formatting on the Percent columns, the worst numbers stand out nicely. I used the 'Graded Color Scale' with 3 colors ; a rose ...


0

Personally, I'd use SQL to do a local dump; with compression enabled [better]. Then I'd use Robocopy to copy the [smaller] file to the network location [faster]. This is pretty much what Robocopy is designed to do [stronger]. The backup should be striped across a number of files to allow maximum write speed possible: Backup database X to disk = ...


1

I suspect this is more to do with the data type of AcctYear (DECIMAL(4,0)). When you simply specify WHERE AcctYear = 2014 SQL Server (in my test rig) believes this to be a SMALLINT. You can see this with a few trace flags: Effective partition elimination relies on data types lining up (amongst other things). Workarounds include changing the datatype of ...


6

Whether or not you are using SQLCMD mode, you can always avoid the need for a GO (which enforces batch separation) by wrapping the calls in an EXEC(). For example: IF ($(EnableDBMail) = 1 ) BEGIN EXEC('sp_configure ''show advanced options'', 1;'); EXEC('RECONFIGURE;'); EXEC('sp_configure ''Database Mail XPs'', 1;'); EXEC('RECONFIGURE;'); END; ...


0

Unfortunately, you are correct the suspect that the partitioning you created can get you in trouble. First of all, partition elimination only happens if the partitioning key is in the query you execute. In other words, any query that does NOT contain AcctYear will do (at least) 31 seeks. SQL Server has no way to know which partition to search if you don't ...


3

I did some searching myself, since I noticed that you're running this query in SQLCMD mode, and I didn't know if it would be any different. Did a quick Google search, and found: http://stackoverflow.com/questions/3988199/how-to-use-sp-configure-in-another-stored-procedure Basically, "GO" is just the batch separator within SSMS, and you should be able to ...


2

I found my problem. I was working under the wrong assumption that the objects were automatically refreshed in Powershell. I added a "$NewPrimary.Refresh()" insinde of the while loop and that did the trick. I hope this helps someone with the same wrong assumption in the future.


2

trace flag 2505 can be used. Wrote a blog post with experiment http://sqlserver-help.com/2014/12/09/help-how-to-suppress-dbcc-traceonoff-messages-in-the-error-log


7

This isn't as easy to achieve as you might think. One way is to create a new user-defined database role, give all the permissions needed to that role, then add users to the new role. This at least makes it easier to give users (or other user-defined roles) this set of permissions in future. The following steps are a good start: -- The user-defined role ...


4

A multiple-column foreign key constraint must be declared at the table level, not on each column: CREATE TABLE dbo.t1 ( id int NOT NULL, name nchar(10) NOT NULL, CONSTRAINT PK_dbo_t1_id_name PRIMARY KEY CLUSTERED (id,name) ); CREATE TABLE dbo.t2 ( id int NOT NULL, name nchar(10) NOT NULL, CONSTRAINT PK_dbo_t2_id_name ...


0

Yes, this was fixed recently in Service Pack 2, Cumulative Update 3. Here's the KB article: http://support.microsoft.com/kb/3012182 "FIX: Log_Send_Rate column in sys.dm_hadr_database_replica_states cannot reflect the rate accurately in SQL Server 2012"


3

The optimizer makes choices based on costing estimates. The cost model is generic, and may not always choose optimal plans for your particular hardware, and its assumptions may not always be valid for your circumstances. In this case, the optimizer assesses a hash join as the cheaper option over nested loops when the estimated number of rows to be joined is ...


2

I think you are already on the right track here - and I encourage you to keep experimenting with different options and see for yourself what the differences to performance are. But, here are my thoughts below: Columnstore Indexes These perform really well when you are genuinely interested in all (or most) of the data in a column. If you are keen to see the ...


2

Views do not have datatypes; they reference columns in tables that do have datatypes. You can do an explicit CONVERT() in a View, but just like any query, it will return the underlying type. Now, doing what amounts to a "default" (especially inline with your request to use DATETIME2 even if someone tries to use another type) is a bit complicated and shown ...


2

No, there isn't a way to automatically change a datetime field to a datetime2 field if the former is specified in a table creation. To do it manually: ALTER TABLE [table] MODIFY COLUMN [column] DATETIME2; If the issue pertains to end users, education is your best bet to make the creation of tables more uniform and compliant with using datetime2.


7

The 64GB limit (and 128GB in 2014) applies only to buffer pool. The KB article "Memory configuration and sizing considerations in SQL Server 2012" says: Starting with SQL Server 2012, these memory limits are enforced only for the database cache (buffer pool). And as we all know, max server memory also does not control all of SQL Server's memory. The ...


4

SYNCHRONIZED state only ensures that the writes are hardened by the secondary (log written to disk). It says nothing about them being applied (data changed). can I expect consistent results to be returned from both replicas, every time? Yes. The reads are consistent, always. But keep in mind that in relational parlance consistency (ACID) has a ...


0

If the tables were in a user defined schema, say data and not the default dbo then you can GRANT select on the schema GRANT SELECT ON SCHEMA::Data TO MyRoleOrUser I regard this as best practice. It also means any new tables inherit SELECT permisisons from the schema and don't need explicit grants SQL Server: hierarchy of permissions for schema? Decision ...


0

Run this query: select 'Grant select on ['+t.name+'] to [MyRole];' from sys.tables as t; Copy the output to a new SSMS tab, edit as required and submit for processing.


0

There are a couple of alternatives. You could set the Select permission to the Schema that owns the tables. (dbo?) grant select on schema :: dbo to myUser --or myDBRole or use query to generate the 100 grant select statements and execute. SELECT 'grant select on ' + TABLE_NAME + ' to myUser' --or myDBRole FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE ...


0

Use GRANT statement with tables delimited by comma like below. Grant select on tbl_1, tbl2, tbl3, tbl4, ...., tb1100 to [MyRole];


0

The solution was to set the DacDeployOptions.ScriptDatabaseOptions property to false. This removed the full-text search logic from the deployment execution (both the SQLCMD script and the DacServices.Deploy(...) method).


0

Just take the database offline and bring it online again. It worked for me (SQL Server 2012).


1

Edited: It looks like the answer is: No, you cannot create a full-text index in a system database. You can, of course, run a script to turn on a full-text indexing after the new database is created from model, if that is necessary. But by default all user databases in the more recent versions of SQL Server have full-text indexing turned on automatically. ...


3

Per Aaron Bertrand's answer (Quick look at how much RAM is allocated to SQL Server?). Task Manager shouldn't be used to ascertain how much memory SQL is actually using. I prefer to use the following query (Again, thanks Aaron): SELECT object_name, cntr_value FROM sys.dm_os_performance_counters WHERE counter_name IN ('Total Server Memory (KB)', 'Target ...



Top 50 recent answers are included