how to use deadlock graph in the sql server 2000 ?

Question

how to use deadlock graph in the sql server 2000 ?

nivedita.kathal

Right there with Babe

Points: 742
More actions
December 29, 2008 at 4:15 am

#199404

Hi,
I am working on a SQL job which generally fails once in two weeks (approx.). the job is running in SQL server 2000.while opening the sql profiler i can see only deadlock and deadlock chain options under lock of event class.but i need to add the deadlock graph option to trace out the deadlock.
could any one please suggest me the way to add the deadlock graph option in event class of sql server 2000 ?
Thanks ,
Nivedita

Viewing 7 posts - 1 through 6 (of 6 total)

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

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

nivedita.kathal (12/29/2008)
Hi,
I am working on a SQL job which generally fails once in two weeks (approx.). the job is running in SQL server 2000.while opening the sql profiler i can see only deadlock and deadlock chain options under lock of event class.but i need to add the deadlock graph option to trace out the deadlock.
could any one please suggest me the way to add the deadlock graph option in event class of sql server 2000 ?
Thanks ,
Nivedita

Deadlock graph as an event class is only availbale in SQL 2005 or higher. All you can do in SQL 2000 is enabling trace flag 1204, to capture the deadlock information in the SQL Errorlog.

[font="Verdana"]Markus Bohse[/font]

nivedita.kathal Right there with Babe Points: 742 More actions · Answer 2

Hi Markus,

I am very new to SQL profiler and i don't know whether i need to run the "DBCC TRACEON (1204)" statement in the SQL profiler in query analyzer or in command prompt(To the to switch on the flag).

Suresh B. SSC-Insane Points: 22986 More actions · Answer 3

i don't know whether i need to run the "DBCC TRACEON (1204)" statement in the SQL profiler in query analyzer or in command prompt(To the to switch on the flag).

Query Analyzer

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

Alos you need to enable the traceflag globally. If you just do TRACEON(1204) then it's only active for that session, which means you'll never see a deadlock graph as it's the system processes that catch deadlocks.

DBCC TRACEON (1204,-1)

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

nivedita.kathal Right there with Babe Points: 742 More actions · Answer 5

Hi Markus,

Thanks, Now i am able to trace out the deadlock through errorlogs.

nivedita.kathal Right there with Babe Points: 742 More actions · Answer 6

The DBCC TRACEON (1204,-1) statement is not showing the information about the deadlock.

but through DBCC TRACEON (3605,1204) we can trace the information related to the deadlock.