A simple SQL2000 Deadlock script
Analysing SQL Server deadlocks can be a tedious process. I'm currently working on a server that's experiencing a few & I wanted a script that could easily pull out the deadlock events from a sql error log, to save me from fishing between all of the backup records & other superfluous information in the log file.
This script assumes you're logging detailed deadlock graph reports to the sql error log first. If you suspect that you're experiencing deadlocks (or positively confirm this from a SQL Profiler trace, which I do by using SQLBenchmarkPro - [url]http://www.GajSoftware.com[/url]), you run this command from the Query Analyser:
DBCC TRACEON (1204, 3605, -1)
1204 represents "record deadlock graph reports when deadlocks occur"
3605 represents "log these reports to the sql error log, rather than the console"
-1 represents "log events for ALL user connections"
Once you've set trace flag 1204 on as outlined above, deadlock graph reports appear in the SQL event log whenever a deadlock occurs. Hence, you need to have set this trace flag on in advance of the deadlock problem to give you much chance of solving the problem..
You can then poll the SQL error log for deadlock information using the script below. Solving them, however, is a far lengthier topic for another day when I've got a bit more time (c:
create table #errorlog (
rowid int identity (1, 1),
errorlog varchar (8000),
ContinuationRow int)
insert into #errorlog (errorlog, ContinuationRow)
exec master.dbo.sp_readerrorlog
select distinct el3.*
from #errorlog el3
join (select errorlog
from #errorlog el1
join (select rowid
from #errorlog
where errorlog like 'Deadlock encountered%') el2 on el1.rowid = el2.rowid - 1) el4
on convert(varchar(27), el3.errorlog) = convert(varchar(27), el4.errorlog)
where (el3.errorlog like '%Node%'
or el3.errorlog like '%Input Buf%'
or el3.errorlog like '%Mode%'
or el3.errorlog like '%Statement%'
or el3.errorlog like '%ResType%')
--and convert(varchar(10), el3.errorlog) > '2006-02-08'
order by rowid