There are a number of good scripts on the internet that will list the blocking chain in order and also tell you the blocking header query. Here's one I use...

The actual backup may not be taking 7+ hours. If they have Verify backup enabled, then SQL does a "logical" restore, reading through the whole backup and checking each page....

As with a lot of things,  "Hash Matches are often better than Loop Joins" has caveats. For small tables a Loop Join may be better, particularly if the inner loop...

The fact the restores take twice as long makes me think the drives may be incorrectly formatted. Assuming NTFS disks, check they are formatted with 64K allocation units and not...

Not sure if people actually read the question.

Best practice for TEMPDB is still to have multiple files. The general rule is one file per physical CPU up to 8 (not...

• This reply was modified 5 years, 5 months ago by  Leo.Miller.

To optimize the reporting database we removed all indexes except the required Primary Key (for Transactional Replication). We then reviewed the report queries and designed a few select indexes specific...

I've found the default trace useful, and have had clients who want to keep more than 5 files. I've built a simple Agent job that runs on a scheduled basis...

I've used this and it certainly wasn't overkill. The client had a lot of resources on the SQL Server, but the main database was highly transactional, and performed very poorly...

Thanks, we've looked at both these and found nothing. I have found the NICs are set to AutoDetect, and an FTP on the problem server is only doing 33Kb per...

"When I open SSRS config on Server 1, the server name value that I connect to is SomeServer, as expected. I go ahead and connect and look at the database...

• This reply was modified 5 years, 8 months ago by  Leo.Miller. Reason: Quotes didn't come out correctly

To answer your main questions:
If we stop the log shipping during the drill, will we be able to bring up the secondary in a read/write mode ? 

• You...

These may seem like random questions, but:
1) Is the database set for Read Committed Snapshot Isolation?
2) Are there any deletes on the table?
3) Are there any old...

It almost looks like the Job History data is causing this. How many rows are there in the Job History? I've found if the Job History gets too big, the clean...

If you use backup and restore then the database level permissions are identical, and the issue can't be at this level. That is unless the user running reports needs permissions...

You could possibly get it more efficient by using a single CTE that numbers the rows, much like your  #unpivot_step2 CTE does, then join to itself, based on reqid and row...