June 26, 2013 at 9:46 am
I'm not taking credit for this, I just took some code WayneS posted a couple years back here: http://www.sqlservercentral.com/Forums/Topic955603-360-1.aspx and updated it / modified it some to work with the XML that SQL2008 spits out when you query the Extended Events...
declare @deadlock xml
set @deadlock = 'put your deadlock graph here'
select
[PagelockObject] = @deadlock.value('/deadlock[1]/resource-list[1]/pagelock[1]/@objectname', 'varchar(200)'),
[DeadlockObject] = @deadlock.value('/deadlock[1]/resource-list[1]/objectlock[1]/@objectname', 'varchar(200)'),
[KeyLockObject] = @deadlock.value('/deadlock[1]/resource-list[1]/keylock[1]/@objectname', 'varchar(200)'),
[KeyLockIndex] = @deadlock.value('/deadlock[1]/resource-list[1]/keylock[1]/@indexname', 'varchar(200)'),
[Victim] = case when Deadlock.Process.value('@id', 'varchar(50)') = @deadlock.value('/deadlock[1]/victim-list[1]/victimProcess[1]/@id', 'varchar(50)') then 1 else 0 end,
[ProcessID] = Deadlock.Process.value('@id', 'varchar(50)'),
[Procedure] = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)'),
[LockMode] = Deadlock.Process.value('@lockMode', 'char(5)'),
[Code] = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)'),
--[ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'),
[HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'),
[LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'),
[TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'),
[BatchTime] = Deadlock.Process.value('@lastbatchstarted', 'datetime'),
[InputBuffer] = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)')
from @deadlock.nodes('/deadlock/process-list/process') as Deadlock(Process)
Just had to change some little things, SQL no longer wraps the XML in "<deadlock-list>" blocks, and the victim information is now a couple more layers deep.
And yes, I'm working with a dev right now trying to troubleshoot deadlocks in an application...
Thanks WayneS!
July 29, 2013 at 4:53 pm
Hi,
Can you please help me on this. I have used WayneS script to see the result for below deadlock trace. but I am not getting any result. Is I am using the script correctly?. Please help.
Thank you
-- Start from here
declare @deadlock xml
set @deadlock = 'deadlock-list
deadlock victim=processc4e748
process-list
process id=processc4e748 taskpriority=0 logused=652 waitresource=KEY: 8:72057594039566336 (8aa1578ec4ac) waittime=669 ownerId=1807497947 transactionname=user_transaction lasttranstarted=2013-07-27T18:00:01.553 XDES=0x189cc2080 lockMode=S schedulerid=3 kpid=8408 status=suspended spid=135 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2013-07-27T18:00:01.550 lastbatchcompleted=2013-07-27T18:00:01.550 clientapp=SQLAgent - TSQL JobStep (Job 0xEC8D5C047ED7DE45862C3E7A12933F7B : Step 1) hostname=SHAIDOSQLGRP02 hostpid=504 loginname=coe\_svcngcsql isolationlevel=read committed (2) xactid=1807497947 currentdb=8 lockTimeout=4294967295 clientoption1=539099168 clientoption2=128024
executionStack
frame procname=mssqlsystemresource.sys.sp_MSdrop_subscription line=173 stmtstart=12028 stmtend=12940 sqlhandle=0x0300ff7f599f97102d5a38016c9f00000100000000000000
if not exists (select * from dbo.MSsubscriptions where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
(publication_id = @publication_id or
@publication_id is NULL or
@independent_agent = 0 ) and
independent_agent = @independent_agent and
subscriber_id = @subscriber_id and
subscriber_db = @subscriber_db and
subscription_type = @subscription_type)
frame procname=mssqlsystemresource.sys.sp_MSrepl_changesubstatus line=1228 stmtstart=90862 stmtend=91122 sqlhandle=0x0300ff7fb822203d535738016c9f00000100000000000000
EXEC @retcode = @distproc @publisher_local<c/> @pub_db<c/> @sub_name<c/> @artid<c/> @dest_db<c/> @current_publication
frame procname=mssqlsystemresource.sys.sp_changesubstatus line=52 stmtstart=3496 stmtend=5162 sqlhandle=0x0300ff7fa215a108825537016c9f00000100000000000000
EXEC @retcode = @cmd
@publication<c/>
@article<c/>
@subscriber<c/>
@status<c/>
@previous_status<c/>
@destination_db<c/>
@frequency_type<c/>
@frequency_interval<c/>
@frequency_relative_interval<c/>
@frequency_recurrence_factor<c/>
@frequency_subday<c/>
@frequency_subday_interval<c/>
@active_start_time_of_day<c/>
@active_end_time_of_day<c/>
@active_start_date<c/>
@active_end_date<c/>
@optional_command_line<c/>
@distribution_jobid OUTPUT<c/>
@from_auto_sync<c/>
@ignore_distributor<c/>
#NAME?
@offloadagent<c/>
@offloadserver<c/>
@dts_package_name<c/>
@dts_package_password<c/>
@dts_package_location<c/>
@skipobjectactivation<c/>
@distribution_job_name<c/>
@publisher<c/>
@publisher_type
<c/>@ignore_distributor_failure
frame procname=Group_FSS00.dbo.psp_GroupCleanupTemporal line=155 stmtstart=11520 stmtend=11790 sqlhandle=0x030007002e48035309a5ce006da100000100000000000000
*sp_changesubstatus-------------------------------------------------------------------------------------------------------------
frame procname=adhoc line=1 sqlhandle=0x0100040027128c16605a9d75020000000000000000000000
exec [Group_FSS00].dbo.psp_GroupCleanupTemporal
inputbuf
exec [Group_FSS00].dbo.psp_GroupCleanupTemporal
process id=process57a748 taskpriority=0 logused=664 waitresource=KEY: 8:72057594039631872 (655da49a216d) waittime=666 ownerId=1807498019 transactionname=user_transaction lasttranstarted=2013-07-27T18:00:01.560 XDES=0x2995eab50 lockMode=S schedulerid=1 kpid=10132 status=suspended spid=127 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2013-07-27T18:00:01.527 lastbatchcompleted=2013-07-27T18:00:01.527 clientapp=SQLAgent - TSQL JobStep (Job 0x103D44BDCB714E4D8959CA804A99738C : Step 1) hostname=SHAIDOSQLGRP02 hostpid=504 loginname=coe\_svcngcsql isolationlevel=read committed (2) xactid=1807498019 currentdb=8 lockTimeout=4294967295 clientoption1=539099168 clientoption2=128024
executionStack
frame procname=mssqlsystemresource.sys.sp_MSadd_subscription line=289 stmtstart=20816 stmtend=21438 sqlhandle=0x0300ff7f4288ff07305a38016c9f00000100000000000000
if exists (select * from dbo.MSsubscriptions where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication_id = @publication_id and
article_id = @article_id and
subscriber_id = @subscriber_id and
subscriber_db = @subscriber_db)
frame procname=mssqlsystemresource.sys.sp_MSrepl_changesubstatus line=1176 stmtstart=85454 stmtend=88860 sqlhandle=0x0300ff7fb822203d535738016c9f00000100000000000000
EXEC @retcode = @distproc @publisher_local<c/> @pub_db<c/> @sub_name<c/>
@artid<c/> @dest_db<c/> @statusid<c/> @sub_ts<c/>
@current_publication<c/>
@null_char<c/> -- Pass null to @article<c/> we already gave @artid
@subscription_type<c/>
--@immediate_sync<c/>
@sync_type<c/>
@zero_bit<c/>
@frequency_type<c/>
@frequency_interval<c/>
@frequency_relative_interval<c/>
@frequency_recurrence_factor<c/>
@frequency_subday<c/>
@frequency_subday_interval<c/>
@active_start_time_of_day<c/>
@active_end_time_of_day<c/>
@active_start_date<c/>
@active_end_date<c/>
@optional_command_line = @optional_command_line<c/>
#NAME?
frame procname=mssqlsystemresource.sys.sp_changesubstatus line=52 stmtstart=3496 stmtend=5162 sqlhandle=0x0300ff7fa215a108825537016c9f00000100000000000000
EXEC @retcode = @cmd
@publication<c/>
@article<c/>
@subscriber<c/>
@status<c/>
@previous_status<c/>
@destination_db<c/>
@frequency_type<c/>
@frequency_interval<c/>
@frequency_relative_interval<c/>
@frequency_recurrence_factor<c/>
@frequency_subday<c/>
@frequency_subday_interval<c/>
@active_start_time_of_day<c/>
@active_end_time_of_day<c/>
@active_start_date<c/>
@active_end_date<c/>
@optional_command_line<c/>
@distribution_jobid OUTPUT<c/>
@from_auto_sync<c/>
@ignore_distributor<c/>
#NAME?
@offloadagent<c/>
@offloadserver<c/>
@dts_package_name<c/>
@dts_package_password<c/>
@dts_package_location<c/>
@skipobjectactivation<c/>
@distribution_job_name<c/>
@publisher<c/>
@publisher_type
<c/>@ignore_distributor_failure
frame procname=Group_FSS01.dbo.psp_GroupCleanupTemporal line=178 stmtstart=13536 stmtend=13802 sqlhandle=0x030005002e4803532d82ce006da100000100000000000000
*sp_changesubstatus-----------------------------------------------------------------------------------------------------------
frame procname=adhoc line=1 sqlhandle=0x0100040027038731903ac864020000000000000000000000
exec [Group_FSS01].dbo.psp_GroupCleanupTemporal
inputbuf
exec [Group_FSS01].dbo.psp_GroupCleanupTemporal
resource-list
keylock hobtid=72057594039566336 dbid=8 objectname=WebstoreDistrib.dbo.MSsubscriptions indexname=ucMSsubscriptions id=lock80ea6680 mode=X associatedObjectId=72057594039566336
owner-list
owner id=process57a748 mode=X
waiter-list
waiter id=processc4e748 mode=S requestType=wait
keylock hobtid=72057594039631872 dbid=8 objectname=WebstoreDistrib.dbo.MSsubscriptions indexname=iMSsubscriptions id=lock37b33d400 mode=X associatedObjectId=72057594039631872
owner-list
owner id=processc4e748 mode=X
waiter-list
waiter id=process57a748 mode=S requestType=wait'
select
[PagelockObject] = @deadlock.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/pagelock[1]/@objectname', 'varchar(200)'),
[DeadlockObject] = @deadlock.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/objectlock[1]/@objectname', 'varchar(200)'),
[Victim] = case when Deadlock.Process.value('@id', 'varchar(50)') = @deadlock.value('/deadlock-list[1]/deadlock[1]/@victim', 'varchar(50)') then 1 else 0 end,
[Procedure] = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)'),
[LockMode] = Deadlock.Process.value('@lockMode', 'char(1)'),
[Code] = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)'),
[ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'),
[HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'),
[LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'),
[TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'),
[InputBuffer] = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)')
from @deadlock.nodes('/deadlock-list/deadlock/process-list/process') as Deadlock(Process)
--End here
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply