Tag Info

Hot answers tagged

5

There are two types of activity to consider here: reads and writes. As you correctly point out, MDF/NDF writes are done through the checkpoint process, and users shouldn't have their transaction time affected. Reads happen when the data that is needed is not yet in RAM (in the buffer cache). So ideally, user activity shouldn't be affected often. But when ...


4

Native tooling approach Grab a newer copy of Management Studio (2012 SP2 and 2014 are both free, fully functional, and can co-exist with your 2008 R2 tools). Then you can do this. Put your cursor right before the first leading ABC. Hold Shift+Alt, then hit the down arrow three times. Type '. It's subtle, but you should see a faint blue vertical line ...


4

I read somewhere that it's not relevant to have more than 10 indexes No hard-and-fast rules like that are generally applicable. If you have more than ten columns (or combinations of columns as an index can cover several) that are regularly searched by then you probably need more than 10 indexes. The key problems with too many indexes which you need to ...


3

Non-clustered indexes use the clustered index key value as a pointer back to the data. If you create a compound clustered index, you are making your non-clustered indexes that much larger. When choosing a clustered index, especially on tables with hundreds of millions of rows, try to choose a single narrow column if possible. i.e. choose an integer over a ...


2

Ensure that the secondary instance (in this case SQL Express) has a TCP port set. Under SQL Server Configuration Manager, go to SQL Server Network Configuration, then Protocols for [INSTANCENAME]. Right click TCP/IP and under the IP Addresses tab make sure the field "TCP Port" has a value (for example 1533). Then try to connect to the instance with the ...


2

The MSSQLSERVER name in the services does not mean anything other than SQL Server (any edition) was installed as the default (unnamed) instance. It does NOT give you any information as to what version or edition it is! This can be SQL Server Express! You need to connect to that database instance and run SELECT @@VERSION to find out what exact ...


2

How about something like below : change your output in SSMS to text and once you are happy run the generated sql statements in another query window : use master go select 'Alter database '+ quotename(name) + char(10) +'set single user with rollback immediate' +char(10)+ 'go'+ char(10)+ 'Drop database '+quotename(name) + char(10)+ 'go' from ...


1

Would you like to try this? DECLARE @cmd1 nvarchar(2000) SET @cmd1 = 'IF ''?'' NOT IN(''Name of your database'')' + 'BEGIN ' + 'drop DATABASE [?] ' + 'END' EXEC sp_MSForEachdb @command1 = @cmd1 GO it worked here. PAY ATTENTION TO NOT DROP MASTER AND ETC. Put then on that list.


1

After setting the db to singleuser you have to switch back to master. I added a use master statement but I don't want to test this.... EXEC sp_MSforeachdb ' USE [?] IF (DB_ID(''?'') > 4 AND DB_NAME()!=''ABC'') BEGIN ALTER DATABASE ''?'' SET SINGLE_USER WITH ROLLBACK IMMEDIATE **use master;** DROP DATABASE ''?'' END


1

It depends a little on what storage system you have behind the scenes. You see, read and write IO operations are very different. On a RAID 5 to perform a single block write you must: Read the update block. Read the parity block. Write the new block. Write the new parity block. So for a single random write, RAID 5 needs 4 operations per write. This is ...


1

Instead of having the trigger rely on the linked server being available, use a queue table. Have a background job that can use sp_testlinkedserver and process all of the items in the queue only when the server is available. It can use TRY/CATCH as well, and only mark those as processed when they are successfully updated, otherwise it will continue trying ...


1

I have personally had experience with Transactional Replication. In my case, I configured the Distributor at the Report Server, and then created the Publication on Live server to be controlled from the Report Server. The Subscription from the Report Server worked perfectly. With this configuration, I was successfully able to replicate data from the Live ...



Only top voted, non community-wiki answers of a minimum length are eligible