The easy way to find deadlocks

A quick, simple and “easy” way to find deadlocks I’ve found is to do the following:

1. Turn on the trace flag 1222
2. Wait for a deadlock to happen
3. Open query analyzer and run the following script:

Create Table #ErrorLogTable
(
LogDate DATETIME NOT NULL,
ProcessInfo VARCHAR(75),
LogInfo VARCHAR(MAX)
)
SET NOCOUNT ON
INSERT INTO #ErrorLogTable
EXEC xp_readerrorlog
Select
*
-- , Left(right(loginfo, Len(loginfo) - 20), charindex(' ',(right(loginfo, Len(loginfo) - 20))))
from #ErrorLogTable
where Logdate > getdate() - 1 --'2/11/09 12:00 AM'
and ProcessInfo not in ('Backup', 'Logon')
and loginfo not like 'This instance of SQL Server has been using a process ID %'
--and loginfo like '%procname%'
--and LogDate > '2010-05-06 11:25:15.780'
order by Logdate

--Drop table #ErrorLogTable

 

To Get the Objects involved:

1. Uncomment the line “and loginfo like ‘%procname%’”
2. Uncomment the line ”Left(right(loginfo, Len(loginfo) – 20), charindex(‘ ‘,(right(loginfo, Len(loginfo) – 20))))”
3. Use the Logdate to limit your results.

 

 

http://www.sqlfeatherandquill.com/2010/05/06/the-easy-way-to-find-deadlocks/