Tuesday, March 13, 2012

DeadLocks in SQL Server

Dead Lock: when two transactions are holding resources and both of them require before completion.
Once the dead lock situation is detected lock manager make one of the transactions as victim for rollback and raise error 1205 which is captured/handled properly

we can check Dead Lock , Blocking and Locking processes using bleow:
1. sp_who
2. sp_who2
3. select * from sys.sysprocesses
4. select  *  from      Sys.dm_tran_locks
5. Using activity montor(in sql server 2008 only)
6. Using  SQL Server Profiler(using template TSQL_Locks)
7. sp_lock
8. select * from master.dbo.syslockinfo

No comments :

Post a Comment