March 2, 2012 at 8:09 am
Hi All,
I am trying to extract all info regarding a deadlock from the event log utilising XP_readerrorrlog. I want to get all the relevant info including initial blocking process and object and the final blocking process along with owner and waiter information and it is proving to be a bit tricky. Anyone have anything that will help?
Thanks in advance
Elliot
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
March 2, 2012 at 8:14 am
THis blog may help to understand the deadlock graph.
March 2, 2012 at 8:17 am
Not all of that is included in the output of traceflag 1222, the initial blocking statement is not, so you have to check back through procedures (names are listed) to see what started the loop.
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
March 2, 2012 at 8:22 am
this will help to capture the deadlock graph 24/7 incase one happens during the night
create a table to store the xml
CREATE TABLE DeadlockDetails
(
ID BIGINT IDENTITY(1,1),
DateCollected DATETIME DEFAULT GETDATE(),
DeadlockGraph XML,
CONSTRAINT PK_DeadlockDetails PRIMARY KEY CLUSTERED (ID)
)
then create an alert and job to log to the tables, where ~~~~~~~ is the name of the database the table has been created in, and '\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER' is changed to suite your server (so if its a cluster replace the \\. with \\Servername and if its a named instance replace MSSQLSERVER with the instance name
USE MSDB
GO
EXEC msdb.dbo.sp_add_job @job_name=N'Capture Deadlock Graph',
@enabled=1,
@description=N'Job for responding to DEADLOCK_GRAPH events' ;
GO
EXEC msdb.dbo.sp_add_jobstep
@job_name = N'Capture Deadlock Graph',
@step_name=N'Insert graph into LogEvents',
@step_id=1,
@on_success_action=1,
@on_fail_action=2,
@subsystem=N'TSQL',
@command= N'INSERT INTO
DeadlockDetails
(
DeadlockGraph
)
VALUES
(
N''$(ESCAPE_SQUOTE(WMI(TextData)))''
)',
@database_name=N'~~~~~~~~' ;
GO
EXEC msdb.dbo.sp_add_jobserver @job_name = N'Capture Deadlock Graph', @server_name = @@SERVERNAME ;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Respond to DEADLOCK_GRAPH',
@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
@wmi_query=N'SELECT * FROM DEADLOCK_GRAPH',
@job_name='Capture Deadlock Graph'
March 2, 2012 at 8:31 am
Thanks for the responses but maybe I didn't explain my situation very well.
I am currently running the global traceflags 1204 & 1222 which give the nodes involved and all of the resources . My difficulty is that I want to extract all of that useful info into a table.
I know that I can use deadlock graphs but they do not give you the execution stack and are not as helpfull as this method (if I could get the info out of the log in a readable format afterwards)
Any help on that would be well recieved
Thanks
Elliot
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
March 2, 2012 at 8:34 am
On 2008 what I would recommend is turning both traceflags off and looking into the system health event session instead.
http://sqlskills.com/BLOGS/PAUL/post/Getting-historical-deadlock-info-using-extended-events.aspx
If you wish to stay with traceflags, turn 1204 off. The info it provides is a subset of what 1222 provides and it will just confuse the issue.
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
March 2, 2012 at 8:55 am
Many thanks
Always helpful
Cheers
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply