Why SQL Server SPID is blocking itself


Hi All

Issue: SPID is blocking itself

Description: On one of our server SQL 2005 i have run one maintainence (update statistics)job in morning hours on a businessday and as it should not create any issues i was monitoring server health. After executing job i found spid 64(belongs to job)  is blocking itself.and after some time it went off

How to restore system databases on lower build

knowledge sharing : Restoring System databases on different build

Issue :

Datafiles and log files are lost on Uat server  due to OS issue
------> Sql server is down
------> All data and log files lost including system database
------> Only backups are available

Affects of Snapshot Isolation Level and Tempdb datafile full


Affects of Snapshot Isolation Level on Tempdb

Last weekend we face an issue where we got alert due tempdb datafile is 95.05% full, which is as usual for weekend but there is one thing unusual we found that log file was only 4 % full.

We thought there could be 2 possible reasons.

1. Sorting.  
2. Row versioning

Database cannot be shrunk as it is either being shrunk by another process or is empty.

Hi, Today we faced below error on one of the servers while shrinking data file on user request.

Error :
/*************************************************************************************************/

File ID 1 of database ID 6 cannot be shrunk as it is either being shrunk by another process or is empty.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
/*************************************************************************************************/

Suspect Pages in database


On Sunday evening we got an opportunity to run DBC checkdb on one of our suspected database (Of course UAT). And as usual we started checkdb from one of sessions on SQL box query analyzer. By the end of the day we realize it will take some more time to complete and we were expecting that by next day some one will kill our connection , while searching on internet we came to know about a table which gives us report of corruption “msdb..suspect_pages”

How Sql server Deadlock Mechanism works

Deadlock Detection


Note: To enable deadlock , Please refer : How To Enable Deadlock Trace in Sql Server

 Deadlock detection is performed by a lock monitor thread that periodically initiates a search through all of the tasks in an instance of the Database Engine. The following points describe the search process: