Deadlocks

Question

Post reply

Deadlocks

jjmraz1

SSC Rookie

Points: 38
More actions
August 12, 2008 at 7:33 pm

#191229

Ok I know theres a command(system sproc) to check for deadlocks and to remove them. Can someone refresh my memory plz?
JM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

moh SSC-Addicted Points: 466 More actions · Answer 1

in sql 2005

Use sys.dm_tran_locks DMV in SQL Server 2005 to get the locks information.

for more information:

http://technet.microsoft.com/en-us/library/ms190345.aspx

example:

SELECT resource_type, resource_associated_entity_id,

request_status, request_mode,request_session_id,

resource_description

FROM sys.dm_tran_locks

WHERE resource_database_id = DB_ID ('AdventureWorks')

-- Get Number of locks in database

SELECT DB_NAME(resource_database_id) dbName, COUNT(*) Lock_number

FROM master.sys.dm_tran_locks

GROUP BY resource_database_id;

also you can benfit from activity monitor Locks by Object Page)

in sql 2000/ 2005

Set trace 1204 and the 3605 on , and all deadlock information will be written to error log. review it and take corrective action.

step by step procedure in: http://support.microsoft.com/kb/832524

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

SQL Server has a built-in deadlock detector. When it notices a deadlock it will automatically select one of the processes involved in the deadlock and kill it. There's no need for manual intervention and, in reality, it's unlikely that you will spot the deadlock before the deadlock monitor does.

If you're looking for the cause of deadlocks, use either traceflag 1204 or 1222 and the deadlock graph will be written into the error log. You can also use SQL Profiler and capture the deadlock graph event.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

free_mascot One Orange Chip Points: 27169 More actions · Answer 3

Visit following link for more detail:

http://support.microsoft.com/kb/832524

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

jjmraz1 SSC Rookie Points: 38 More actions · Answer 4

Ah thanks all I will definitely check these out. 🙂

JM