Search

Deadlock in MDW Upload Purge Logs Job Step by Kunk

Closed
as Won't Fix Help for as Won't Fix

1
0
Sign in
 to vote
Type: Bug
ID: 778450
Opened: 2/4/2013 2:53:20 AM
Access Restriction: Public
0
Workaround(s)
0
User(s) can reproduce this bug
This Feedback refer to Feedback ID 539547 (I found some other feedbacks for the same problem). In 539547 Sudarshan Narasimhan [MSFT] stated that this problem will be fixed in SQL Server 2008 R2. I activated Data collection on SQL Server 2008 R2 SP1 (10.50.2772.0) a few weeks ago. And now I also found several deadlocks, let me assuming that the problem isn't fixed yet!

Regards
Wolfgang Kunk
Details (expand)

Product Language

English

Version

SQL Server 2008 R2 SP1

Category

SQL Engine

Operating System

Windows Server 2008 (all editions)

Operating System Language

US English

Steps to Reproduce

activate MDW and Data collection. Wait for the end of the retention time auf data collections (14 days default für server activity ans query statistics)

Actual Results

Deadlocks

Expected Results

no deadlocks

Platform

 

Virtualization

 
File Attachments
0 attachments
Sign in to post a comment.
Posted by Kunk on 5/31/2013 at 12:22 AM
First, the version of the SQL Server generating the following deadlock : SQL Server 2012 SP1 Standard Edition (64-bit) (11.0.3339.0 )

Und hier die Deadlockinformationen :

2013-03-06 10:16:47.57 spid71     DBCC TRACEON 3604, server process ID (SPID) 71. This is an informational message only; no user action is required.
2013-03-06 10:18:01.81 spid71     DBCC TRACEON 1222, server process ID (SPID) 71. This is an informational message only; no user action is required.
2013-03-06 10:30:06.93 spid9s     deadlock-list
2013-03-06 10:30:06.93 spid9s     deadlock victim=process8fe013c38
2013-03-06 10:30:06.93 spid9s        process-list
2013-03-06 10:30:06.93 spid9s         process id=process8fe013c38 taskpriority=0 logused=0 waitresource=PAGE: 4:1:7719 waittime=6247 ownerId=281367477 transactionname=DELETE lasttranstarted=2013-03-06T10:30:00.473 XDES=0x8fe959920 lockMode=U schedulerid=1 kpid=7056 status=suspended spid=74 sbid=0 ecid=6 priority=0 trancount=0 lastbatchstarted=2013-03-06T10:30:00.370 lastbatchcompleted=2013-03-06T10:30:00.370 lastattention=1900-01-01T00:00:00.370 clientapp=SQLAgent - TSQL JobStep (Job 0xC555FD2BADD4954E8C7D241E749B9668 : Step 1) hostname=SENTIA hostpid=2068 isolationlevel=read committed (2) xactid=281367477 currentdb=4 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2013-03-06 10:30:06.93 spid9s         executionStack
2013-03-06 10:30:06.93 spid9s         frame procname=msdb.dbo.sp_syscollector_purge_collection_logs line=45 stmtstart=3600 stmtend=3988 sqlhandle=0x03000400899c5b48eac994005da1000001000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.93 spid9s     DELETE FROM dbo.sysssislog
2013-03-06 10:30:06.93 spid9s             FROM dbo.sysssislog AS s
2013-03-06 10:30:06.93 spid9s             INNER JOIN #purged_log_ids AS i ON i.package_execution_id = s.executionid
2013-03-06 10:30:06.93 spid9s         -- Then delete the actual logs    
2013-03-06 10:30:06.93 spid9s         frame procname=adhoc line=2 stmtstart=28 sqlhandle=0x0100040034b4980cb0306bf70600000000000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.93 spid9s     EXEC [dbo].[sp_syscollector_purge_collection_logs]    
2013-03-06 10:30:06.93 spid9s         inputbuf
2013-03-06 10:30:06.93 spid9s                 EXEC [dbo].[sp_syscollector_purge_collection_logs]
2013-03-06 10:30:06.93 spid9s         process id=process8fe024928 taskpriority=0 logused=0 waitresource=PAGE: 4:1:14367 waittime=6151 ownerId=281367481 transactionname=DELETE lasttranstarted=2013-03-06T10:30:00.473 XDES=0x8fe961110 lockMode=U schedulerid=3 kpid=11944 status=suspended spid=72 sbid=0 ecid=12 priority=0 trancount=0 lastbatchstarted=2013-03-06T10:30:00.370 lastbatchcompleted=2013-03-06T10:30:00.370 lastattention=1900-01-01T00:00:00.370 clientapp=SQLAgent - TSQL JobStep (Job 0x7474D1FB8D371743BF2BC2E7DCAB9ECD : Step 1) hostname=SENTIA hostpid=2068 isolationlevel=read committed (2) xactid=281367481 currentdb=4 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2013-03-06 10:30:06.93 spid9s         executionStack
2013-03-06 10:30:06.93 spid9s         frame procname=msdb.dbo.sp_syscollector_purge_collection_logs line=45 stmtstart=3600 stmtend=3988 sqlhandle=0x03000400899c5b48eac994005da1000001000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.93 spid9s     DELETE FROM dbo.sysssislog
2013-03-06 10:30:06.93 spid9s             FROM dbo.sysssislog AS s
2013-03-06 10:30:06.93 spid9s             INNER JOIN #purged_log_ids AS i ON i.package_execution_id = s.executionid
2013-03-06 10:30:06.93 spid9s         -- Then delete the actual logs    
2013-03-06 10:30:06.93 spid9s         frame procname=adhoc line=2 stmtstart=28 sqlhandle=0x0100040034b4980cb0306bf70600000000000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.93 spid9s     EXEC [dbo].[sp_syscollector_purge_collection_logs]    
2013-03-06 10:30:06.93 spid9s         inputbuf
2013-03-06 10:30:06.93 spid9s                 EXEC [dbo].[sp_syscollector_purge_collection_logs]
2013-03-06 10:30:06.93 spid9s         process id=process8fe05d868 taskpriority=0 logused=0 waitresource=PAGE: 4:1:14367 waittime=6151 ownerId=281367481 transactionname=DELETE lasttranstarted=2013-03-06T10:30:00.473 XDES=0x8fe7a9c50 lockMode=U schedulerid=10 kpid=5520 status=suspended spid=72 sbid=0 ecid=9 priority=0 trancount=0 lastbatchstarted=2013-03-06T10:30:00.370 lastbatchcompleted=2013-03-06T10:30:00.370 lastattention=1900-01-01T00:00:00.370 clientapp=SQLAgent - TSQL JobStep (Job 0x7474D1FB8D371743BF2BC2E7DCAB9ECD : Step 1) hostname=SENTIA hostpid=2068 isolationlevel=read committed (2) xactid=281367481 currentdb=4 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2013-03-06 10:30:06.93 spid9s         executionStack
2013-03-06 10:30:06.93 spid9s         frame procname=msdb.dbo.sp_syscollector_purge_collection_logs line=45 stmtstart=3600 stmtend=3988 sqlhandle=0x03000400899c5b48eac994005da1000001000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.93 spid9s     DELETE FROM dbo.sysssislog
2013-03-06 10:30:06.93 spid9s             FROM dbo.sysssislog AS s
2013-03-06 10:30:06.93 spid9s             INNER JOIN #purged_log_ids AS i ON i.package_execution_id = s.executionid
2013-03-06 10:30:06.93 spid9s         -- Then delete the actual logs    
2013-03-06 10:30:06.93 spid9s         frame procname=adhoc line=2 stmtstart=28 sqlhandle=0x0100040034b4980cb0306bf70600000000000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.93 spid9s     EXEC [dbo].[sp_syscollector_purge_collection_logs]    
2013-03-06 10:30:06.93 spid9s         inputbuf
2013-03-06 10:30:06.93 spid9s                 EXEC [dbo].[sp_syscollector_purge_collection_logs]
2013-03-06 10:30:06.93 spid9s         process id=process8fe013868 taskpriority=0 logused=0 waitresource=PAGE: 4:1:12407 waittime=6247 ownerId=281367481 transactionname=DELETE lasttranstarted=2013-03-06T10:30:00.473 XDES=0x855536880 lockMode=U schedulerid=1 kpid=10164 status=suspended spid=72 sbid=0 ecid=6 priority=0 trancount=0 lastbatchstarted=2013-03-06T10:30:00.370 lastbatchcompleted=2013-03-06T10:30:00.370 lastattention=1900-01-01T00:00:00.370 clientapp=SQLAgent - TSQL JobStep (Job 0x7474D1FB8D371743BF2BC2E7DCAB9ECD : Step 1) hostname=SENTIA hostpid=2068 isolationlevel=read committed (2) xactid=281367481 currentdb=4 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2013-03-06 10:30:06.93 spid9s         executionStack
2013-03-06 10:30:06.93 spid9s         frame procname=msdb.dbo.sp_syscollector_purge_collection_logs line=45 stmtstart=3600 stmtend=3988 sqlhandle=0x03000400899c5b48eac994005da1000001000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.93 spid9s     DELETE FROM dbo.sysssislog
2013-03-06 10:30:06.93 spid9s             FROM dbo.sysssislog AS s
2013-03-06 10:30:06.93 spid9s             INNER JOIN #purged_log_ids AS i ON i.package_execution_id = s.executionid
2013-03-06 10:30:06.93 spid9s         -- Then delete the actual logs    
2013-03-06 10:30:06.93 spid9s         frame procname=adhoc line=2 stmtstart=28 sqlhandle=0x0100040034b4980cb0306bf70600000000000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.93 spid9s     EXEC [dbo].[sp_syscollector_purge_collection_logs]    
2013-03-06 10:30:06.93 spid9s         inputbuf
2013-03-06 10:30:06.93 spid9s                 EXEC [dbo].[sp_syscollector_purge_collection_logs]
2013-03-06 10:30:06.93 spid9s         process id=process38adfe188 taskpriority=0 logused=0 waitresource=PAGE: 4:1:14191 waittime=6162 ownerId=281367477 transactionname=DELETE lasttranstarted=2013-03-06T10:30:00.473 XDES=0x8fe95d920 lockMode=U schedulerid=2 kpid=5020 status=suspended spid=74 sbid=0 ecid=8 priority=0 trancount=0 lastbatchstarted=2013-03-06T10:30:00.370 lastbatchcompleted=2013-03-06T10:30:00.370 lastattention=1900-01-01T00:00:00.370 clientapp=SQLAgent - TSQL JobStep (Job 0xC555FD2BADD4954E8C7D241E749B9668 : Step 1) hostname=SENTIA hostpid=2068 isolationlevel=read committed (2) xactid=281367477 currentdb=4 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2013-03-06 10:30:06.93 spid9s         executionStack
2013-03-06 10:30:06.93 spid9s         frame procname=msdb.dbo.sp_syscollector_purge_collection_logs line=45 stmtstart=3600 stmtend=3988 sqlhandle=0x03000400899c5b48eac994005da1000001000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.93 spid9s     DELETE FROM dbo.sysssislog
2013-03-06 10:30:06.93 spid9s             FROM dbo.sysssislog AS s
2013-03-06 10:30:06.93 spid9s             INNER JOIN #purged_log_ids AS i ON i.package_execution_id = s.executionid
2013-03-06 10:30:06.93 spid9s         -- Then delete the actual logs    
2013-03-06 10:30:06.93 spid9s         frame procname=adhoc line=2 stmtstart=28 sqlhandle=0x0100040034b4980cb0306bf70600000000000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.93 spid9s     EXEC [dbo].[sp_syscollector_purge_collection_logs]    
2013-03-06 10:30:06.93 spid9s         inputbuf
2013-03-06 10:30:06.93 spid9s                 EXEC [dbo].[sp_syscollector_purge_collection_logs]
2013-03-06 10:30:06.93 spid9s         process id=process89a42e188 taskpriority=0 logused=0 waitresource=PAGE: 4:1:14223 waittime=6160 ownerId=281367481 transactionname=DELETE lasttranstarted=2013-03-06T10:30:00.473 XDES=0x8fe9655d0 lockMode=U schedulerid=4 kpid=4948 status=suspended spid=72 sbid=0 ecid=4 priority=0 trancount=0 lastbatchstarted=2013-03-06T10:30:00.370 lastbatchcompleted=2013-03-06T10:30:00.370 lastattention=1900-01-01T00:00:00.370 clientapp=SQLAgent - TSQL JobStep (Job 0x7474D1FB8D371743BF2BC2E7DCAB9ECD : Step 1) hostname=SENTIA hostpid=2068 isolationlevel=read committed (2) xactid=281367481 currentdb=4 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2013-03-06 10:30:06.93 spid9s         executionStack
2013-03-06 10:30:06.93 spid9s         frame procname=msdb.dbo.sp_syscollector_purge_collection_logs line=45 stmtstart=3600 stmtend=3988 sqlhandle=0x03000400899c5b48eac994005da1000001000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.93 spid9s     DELETE FROM dbo.sysssislog
2013-03-06 10:30:06.93 spid9s             FROM dbo.sysssislog AS s
2013-03-06 10:30:06.93 spid9s             INNER JOIN #purged_log_ids AS i ON i.package_execution_id = s.executionid
2013-03-06 10:30:06.93 spid9s         -- Then delete the actual logs    
2013-03-06 10:30:06.93 spid9s         frame procname=adhoc line=2 stmtstart=28 sqlhandle=0x0100040034b4980cb0306bf70600000000000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.93 spid9s     EXEC [dbo].[sp_syscollector_purge_collection_logs]    
2013-03-06 10:30:06.93 spid9s         inputbuf
2013-03-06 10:30:06.93 spid9s                 EXEC [dbo].[sp_syscollector_purge_collection_logs]
2013-03-06 10:30:06.93 spid9s         process id=process8fe025c38 taskpriority=0 logused=0 waitresource=PAGE: 4:1:11103 waittime=6248 ownerId=281367477 transactionname=DELETE lasttranstarted=2013-03-06T10:30:00.473 XDES=0x8fe960200 lockMode=U schedulerid=3 kpid=3904 status=suspended spid=74 sbid=0 ecid=11 priority=0 trancount=0 lastbatchstarted=2013-03-06T10:30:00.370 lastbatchcompleted=2013-03-06T10:30:00.370 lastattention=1900-01-01T00:00:00.370 clientapp=SQLAgent - TSQL JobStep (Job 0xC555FD2BADD4954E8C7D241E749B9668 : Step 1) hostname=SENTIA hostpid=2068 isolationlevel=read committed (2) xactid=281367477 currentdb=4 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2013-03-06 10:30:06.93 spid9s         executionStack
2013-03-06 10:30:06.93 spid9s         frame procname=msdb.dbo.sp_syscollector_purge_collection_logs line=45 stmtstart=3600 stmtend=3988 sqlhandle=0x03000400899c5b48eac994005da1000001000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.93 spid9s     DELETE FROM dbo.sysssislog
2013-03-06 10:30:06.93 spid9s             FROM dbo.sysssislog AS s
2013-03-06 10:30:06.93 spid9s             INNER JOIN #purged_log_ids AS i ON i.package_execution_id = s.executionid
2013-03-06 10:30:06.93 spid9s         -- Then delete the actual logs    
2013-03-06 10:30:06.93 spid9s         frame procname=adhoc line=2 stmtstart=28 sqlhandle=0x0100040034b4980cb0306bf70600000000000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.93 spid9s     EXEC [dbo].[sp_syscollector_purge_collection_logs]    
2013-03-06 10:30:06.93 spid9s         inputbuf
2013-03-06 10:30:06.93 spid9s                 EXEC [dbo].[sp_syscollector_purge_collection_logs]
2013-03-06 10:30:06.93 spid9s         process id=process89a42fc38 taskpriority=0 logused=0 waitresource=PAGE: 4:1:11103 waittime=6248 ownerId=281367477 transactionname=DELETE lasttranstarted=2013-03-06T10:30:00.473 XDES=0x8fe965d40 lockMode=U schedulerid=4 kpid=10228 status=suspended spid=74 sbid=0 ecid=4 priority=0 trancount=0 lastbatchstarted=2013-03-06T10:30:00.370 lastbatchcompleted=2013-03-06T10:30:00.370 lastattention=1900-01-01T00:00:00.370 clientapp=SQLAgent - TSQL JobStep (Job 0xC555FD2BADD4954E8C7D241E749B9668 : Step 1) hostname=SENTIA hostpid=2068 isolationlevel=read committed (2) xactid=281367477 currentdb=4 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2013-03-06 10:30:06.93 spid9s         executionStack
2013-03-06 10:30:06.93 spid9s         frame procname=msdb.dbo.sp_syscollector_purge_collection_logs line=45 stmtstart=3600 stmtend=3988 sqlhandle=0x03000400899c5b48eac994005da1000001000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.93 spid9s     DELETE FROM dbo.sysssislog
2013-03-06 10:30:06.93 spid9s             FROM dbo.sysssislog AS s
2013-03-06 10:30:06.93 spid9s             INNER JOIN #purged_log_ids AS i ON i.package_execution_id = s.executionid
2013-03-06 10:30:06.93 spid9s         -- Then delete the actual logs    
2013-03-06 10:30:06.93 spid9s         frame procname=adhoc line=2 stmtstart=28 sqlhandle=0x0100040034b4980cb0306bf70600000000000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.93 spid9s     EXEC [dbo].[sp_syscollector_purge_collection_logs]    
2013-03-06 10:30:06.93 spid9s         inputbuf
2013-03-06 10:30:06.93 spid9s                 EXEC [dbo].[sp_syscollector_purge_collection_logs]
2013-03-06 10:30:06.93 spid9s         process id=process7b5794928 taskpriority=0 logused=0 waitresource=PAGE: 4:1:5583 waittime=6226 ownerId=281367477 transactionname=DELETE lasttranstarted=2013-03-06T10:30:00.473 XDES=0x38a76e880 lockMode=U schedulerid=7 kpid=9500 status=suspended spid=74 sbid=0 ecid=12 priority=0 trancount=0 lastbatchstarted=2013-03-06T10:30:00.370 lastbatchcompleted=2013-03-06T10:30:00.370 lastattention=1900-01-01T00:00:00.370 clientapp=SQLAgent - TSQL JobStep (Job 0xC555FD2BADD4954E8C7D241E749B9668 : Step 1) hostname=SENTIA hostpid=2068 isolationlevel=read committed (2) xactid=281367477 currentdb=4 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2013-03-06 10:30:06.93 spid9s         executionStack
2013-03-06 10:30:06.93 spid9s         frame procname=msdb.dbo.sp_syscollector_purge_collection_logs line=45 stmtstart=3600 stmtend=3988 sqlhandle=0x03000400899c5b48eac994005da1000001000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.93 spid9s     DELETE FROM dbo.sysssislog
2013-03-06 10:30:06.93 spid9s             FROM dbo.sysssislog AS s
2013-03-06 10:30:06.93 spid9s             INNER JOIN #purged_log_ids AS i ON i.package_execution_id = s.executionid
2013-03-06 10:30:06.93 spid9s         -- Then delete the actual logs    
2013-03-06 10:30:06.93 spid9s         frame procname=adhoc line=2 stmtstart=28 sqlhandle=0x0100040034b4980cb0306bf70600000000000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.93 spid9s     EXEC [dbo].[sp_syscollector_purge_collection_logs]    
2013-03-06 10:30:06.93 spid9s         inputbuf
2013-03-06 10:30:06.93 spid9s                 EXEC [dbo].[sp_syscollector_purge_collection_logs]
2013-03-06 10:30:06.94 spid9s         process id=process38a652188 taskpriority=0 logused=0 waitresource=PAGE: 4:1:14295 waittime=6156 ownerId=281367481 transactionname=DELETE lasttranstarted=2013-03-06T10:30:00.473 XDES=0x8fe969ca0 lockMode=U schedulerid=5 kpid=3688 status=suspended spid=72 sbid=0 ecid=10 priority=0 trancount=0 lastbatchstarted=2013-03-06T10:30:00.370 lastbatchcompleted=2013-03-06T10:30:00.370 lastattention=1900-01-01T00:00:00.370 clientapp=SQLAgent - TSQL JobStep (Job 0x7474D1FB8D371743BF2BC2E7DCAB9ECD : Step 1) hostname=SENTIA hostpid=2068 isolationlevel=read committed (2) xactid=281367481 currentdb=4 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2013-03-06 10:30:06.94 spid9s         executionStack
2013-03-06 10:30:06.94 spid9s         frame procname=msdb.dbo.sp_syscollector_purge_collection_logs line=45 stmtstart=3600 stmtend=3988 sqlhandle=0x03000400899c5b48eac994005da1000001000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.94 spid9s     DELETE FROM dbo.sysssislog
2013-03-06 10:30:06.94 spid9s             FROM dbo.sysssislog AS s
2013-03-06 10:30:06.94 spid9s             INNER JOIN #purged_log_ids AS i ON i.package_execution_id = s.executionid
2013-03-06 10:30:06.94 spid9s         -- Then delete the actual logs    
2013-03-06 10:30:06.94 spid9s         frame procname=adhoc line=2 stmtstart=28 sqlhandle=0x0100040034b4980cb0306bf70600000000000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.94 spid9s     EXEC [dbo].[sp_syscollector_purge_collection_logs]    
2013-03-06 10:30:06.94 spid9s         inputbuf
2013-03-06 10:30:06.94 spid9s                 EXEC [dbo].[sp_syscollector_purge_collection_logs]
2013-03-06 10:30:06.94 spid9s         process id=process841fde558 taskpriority=0 logused=0 waitresource=PAGE: 4:1:14223 waittime=6247 ownerId=281367481 transactionname=DELETE lasttranstarted=2013-03-06T10:30:00.473 XDES=0x8fe971d40 lockMode=U schedulerid=7 kpid=8872 status=suspended spid=72 sbid=0 ecid=7 priority=0 trancount=0 lastbatchstarted=2013-03-06T10:30:00.370 lastbatchcompleted=2013-03-06T10:30:00.370 lastattention=1900-01-01T00:00:00.370 clientapp=SQLAgent - TSQL JobStep (Job 0x7474D1FB8D371743BF2BC2E7DCAB9ECD : Step 1) hostname=SENTIA hostpid=2068 isolationlevel=read committed (2) xactid=281367481 currentdb=4 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2013-03-06 10:30:06.94 spid9s         executionStack
2013-03-06 10:30:06.94 spid9s         frame procname=msdb.dbo.sp_syscollector_purge_collection_logs line=45 stmtstart=3600 stmtend=3988 sqlhandle=0x03000400899c5b48eac994005da1000001000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.94 spid9s     DELETE FROM dbo.sysssislog
2013-03-06 10:30:06.94 spid9s             FROM dbo.sysssislog AS s
2013-03-06 10:30:06.94 spid9s             INNER JOIN #purged_log_ids AS i ON i.package_execution_id = s.executionid
2013-03-06 10:30:06.94 spid9s         -- Then delete the actual logs    
2013-03-06 10:30:06.94 spid9s         frame procname=adhoc line=2 stmtstart=28 sqlhandle=0x0100040034b4980cb0306bf70600000000000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.94 spid9s     EXEC [dbo].[sp_syscollector_purge_collection_logs]    
2013-03-06 10:30:06.94 spid9s         inputbuf
2013-03-06 10:30:06.94 spid9s                 EXEC [dbo].[sp_syscollector_purge_collection_logs]
2013-03-06 10:30:06.94 spid9s         process id=process8fe035868 taskpriority=0 logused=0 waitresource=PAGE: 4:1:17519 waittime=6248 ownerId=281367477 transactionname=DELETE lasttranstarted=2013-03-06T10:30:00.473 XDES=0x8fe968d90 lockMode=U schedulerid=5 kpid=4700 status=suspended spid=74 sbid=0 ecid=9 priority=0 trancount=0 lastbatchstarted=2013-03-06T10:30:00.370 lastbatchcompleted=2013-03-06T10:30:00.370 lastattention=1900-01-01T00:00:00.370 clientapp=SQLAgent - TSQL JobStep (Job 0xC555FD2BADD4954E8C7D241E749B9668 : Step 1) hostname=SENTIA hostpid=2068 isolationlevel=read committed (2) xactid=281367477 currentdb=4 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2013-03-06 10:30:06.94 spid9s         executionStack
2013-03-06 10:30:06.94 spid9s         frame procname=msdb.dbo.sp_syscollector_purge_collection_logs line=45 stmtstart=3600 stmtend=3988 sqlhandle=0x03000400899c5b48eac994005da1000001000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.94 spid9s     DELETE FROM dbo.sysssislog
2013-03-06 10:30:06.94 spid9s             FROM dbo.sysssislog AS s
2013-03-06 10:30:06.94 spid9s             INNER JOIN #purged_log_ids AS i ON i.package_execution_id = s.executionid
2013-03-06 10:30:06.94 spid9s         -- Then delete the actual logs    
2013-03-06 10:30:06.94 spid9s         frame procname=adhoc line=2 stmtstart=28 sqlhandle=0x0100040034b4980cb0306bf70600000000000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.94 spid9s     EXEC [dbo].[sp_syscollector_purge_collection_logs]    
2013-03-06 10:30:06.94 spid9s         inputbuf
2013-03-06 10:30:06.94 spid9s                 EXEC [dbo].[sp_syscollector_purge_collection_logs]
2013-03-06 10:30:06.94 spid9s         process id=process38a632188 taskpriority=0 logused=0 waitresource=PAGE: 4:1:3319 waittime=6187 ownerId=281367481 transactionname=DELETE lasttranstarted=2013-03-06T10:30:00.473 XDES=0x841f8e040 lockMode=U schedulerid=8 kpid=11580 status=suspended spid=72 sbid=0 ecid=5 priority=0 trancount=0 lastbatchstarted=2013-03-06T10:30:00.370 lastbatchcompleted=2013-03-06T10:30:00.370 lastattention=1900-01-01T00:00:00.370 clientapp=SQLAgent - TSQL JobStep (Job 0x7474D1FB8D371743BF2BC2E7DCAB9ECD : Step 1) hostname=SENTIA hostpid=2068 isolationlevel=read committed (2) xactid=281367481 currentdb=4 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2013-03-06 10:30:06.94 spid9s         executionStack
2013-03-06 10:30:06.94 spid9s         frame procname=msdb.dbo.sp_syscollector_purge_collection_logs line=45 stmtstart=3600 stmtend=3988 sqlhandle=0x03000400899c5b48eac994005da1000001000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.94 spid9s     DELETE FROM dbo.sysssislog
2013-03-06 10:30:06.94 spid9s             FROM dbo.sysssislog AS s
2013-03-06 10:30:06.94 spid9s             INNER JOIN #purged_log_ids AS i ON i.package_execution_id = s.executionid
2013-03-06 10:30:06.94 spid9s         -- Then delete the actual logs    
2013-03-06 10:30:06.94 spid9s         frame procname=adhoc line=2 stmtstart=28 sqlhandle=0x0100040034b4980cb0306bf70600000000000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.94 spid9s     EXEC [dbo].[sp_syscollector_purge_collection_logs]    
2013-03-06 10:30:06.94 spid9s         inputbuf
2013-03-06 10:30:06.94 spid9s                 EXEC [dbo].[sp_syscollector_purge_collection_logs]
2013-03-06 10:30:06.94 spid9s         process id=process8fe04d868 taskpriority=0 logused=0 waitresource=PAGE: 4:1:14343 waittime=6152 ownerId=281367477 transactionname=DELETE lasttranstarted=2013-03-06T10:30:00.473 XDES=0x8fe975920 lockMode=U schedulerid=8 kpid=6708 status=suspended spid=74 sbid=0 ecid=5 priority=0 trancount=0 lastbatchstarted=2013-03-06T10:30:00.370 lastbatchcompleted=2013-03-06T10:30:00.370 lastattention=1900-01-01T00:00:00.370 clientapp=SQLAgent - TSQL JobStep (Job 0xC555FD2BADD4954E8C7D241E749B9668 : Step 1) hostname=SENTIA hostpid=2068 isolationlevel=read committed (2) xactid=281367477 currentdb=4 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2013-03-06 10:30:06.94 spid9s         executionStack
2013-03-06 10:30:06.94 spid9s         frame procname=msdb.dbo.sp_syscollector_purge_collection_logs line=45 stmtstart=3600 stmtend=3988 sqlhandle=0x03000400899c5b48eac994005da1000001000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.94 spid9s     DELETE FROM dbo.sysssislog
2013-03-06 10:30:06.94 spid9s             FROM dbo.sysssislog AS s
2013-03-06 10:30:06.94 spid9s             INNER JOIN #purged_log_ids AS i ON i.package_execution_id = s.executionid
2013-03-06 10:30:06.94 spid9s         -- Then delete the actual logs    
2013-03-06 10:30:06.94 spid9s         frame procname=adhoc line=2 stmtstart=28 sqlhandle=0x0100040034b4980cb0306bf70600000000000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.94 spid9s     EXEC [dbo].[sp_syscollector_purge_collection_logs]    
2013-03-06 10:30:06.94 spid9s         inputbuf
2013-03-06 10:30:06.94 spid9s                 EXEC [dbo].[sp_syscollector_purge_collection_logs]
2013-03-06 10:30:06.94 spid9s         process id=process75a717c38 taskpriority=0 logused=0 waitresource=PAGE: 4:1:14343 waittime=6156 ownerId=281367477 transactionname=DELETE lasttranstarted=2013-03-06T10:30:00.473 XDES=0x8fe979920 lockMode=U schedulerid=9 kpid=5960 status=suspended spid=74 sbid=0 ecid=2 priority=0 trancount=0 lastbatchstarted=2013-03-06T10:30:00.370 lastbatchcompleted=2013-03-06T10:30:00.370 lastattention=1900-01-01T00:00:00.370 clientapp=SQLAgent - TSQL JobStep (Job 0xC555FD2BADD4954E8C7D241E749B9668 : Step 1) hostname=SENTIA hostpid=2068 isolationlevel=read committed (2) xactid=281367477 currentdb=4 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2013-03-06 10:30:06.94 spid9s         executionStack
2013-03-06 10:30:06.94 spid9s         frame procname=msdb.dbo.sp_syscollector_purge_collection_logs line=45 stmtstart=3600 stmtend=3988 sqlhandle=0x03000400899c5b48eac994005da1000001000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.94 spid9s     DELETE FROM dbo.sysssislog
2013-03-06 10:30:06.94 spid9s             FROM dbo.sysssislog AS s
2013-03-06 10:30:06.94 spid9s             INNER JOIN #purged_log_ids AS i ON i.package_execution_id = s.executionid
2013-03-06 10:30:06.94 spid9s         -- Then delete the actual logs    
2013-03-06 10:30:06.94 spid9s         frame procname=adhoc line=2 stmtstart=28 sqlhandle=0x0100040034b4980cb0306bf70600000000000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.94 spid9s     EXEC [dbo].[sp_syscollector_purge_collection_logs]    
2013-03-06 10:30:06.94 spid9s         inputbuf
2013-03-06 10:30:06.94 spid9s                 EXEC [dbo].[sp_syscollector_purge_collection_logs]
2013-03-06 10:30:06.94 spid9s         process id=process7785190c8 taskpriority=0 logused=0 waitresource=PAGE: 4:1:5583 waittime=6173 ownerId=281367477 transactionname=DELETE lasttranstarted=2013-03-06T10:30:00.473 XDES=0x8fd901920 lockMode=U schedulerid=11 kpid=7044 status=suspended spid=74 sbid=0 ecid=1 priority=0 trancount=0 lastbatchstarted=2013-03-06T10:30:00.370 lastbatchcompleted=2013-03-06T10:30:00.370 lastattention=1900-01-01T00:00:00.370 clientapp=SQLAgent - TSQL JobStep (Job 0xC555FD2BADD4954E8C7D241E749B9668 : Step 1) hostname=SENTIA hostpid=2068 isolationlevel=read committed (2) xactid=281367477 currentdb=4 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2013-03-06 10:30:06.94 spid9s         executionStack
2013-03-06 10:30:06.94 spid9s         frame procname=msdb.dbo.sp_syscollector_purge_collection_logs line=45 stmtstart=3600 stmtend=3988 sqlhandle=0x03000400899c5b48eac994005da1000001000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.94 spid9s     DELETE FROM dbo.sysssislog
2013-03-06 10:30:06.94 spid9s             FROM dbo.sysssislog AS s
2013-03-06 10:30:06.94 spid9s             INNER JOIN #purged_log_ids AS i ON i.package_execution_id = s.executionid
2013-03-06 10:30:06.94 spid9s         -- Then delete the actual logs    
2013-03-06 10:30:06.94 spid9s         frame procname=adhoc line=2 stmtstart=28 sqlhandle=0x0100040034b4980cb0306bf70600000000000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.94 spid9s     EXEC [dbo].[sp_syscollector_purge_collection_logs]    
2013-03-06 10:30:06.94 spid9s         inputbuf
2013-03-06 10:30:06.94 spid9s                 EXEC [dbo].[sp_syscollector_purge_collection_logs]
2013-03-06 10:30:06.94 spid9s         process id=process875c5a928 taskpriority=0 logused=0 waitresource=PAGE: 4:1:10327 waittime=6248 ownerId=281367477 transactionname=DELETE lasttranstarted=2013-03-06T10:30:00.473 XDES=0x38b78cbd0 lockMode=U schedulerid=12 kpid=7540 status=suspended spid=74 sbid=0 ecid=3 priority=0 trancount=0 lastbatchstarted=2013-03-06T10:30:00.370 lastbatchcompleted=2013-03-06T10:30:00.370 lastattention=1900-01-01T00:00:00.370 clientapp=SQLAgent - TSQL JobStep (Job 0xC555FD2BADD4954E8C7D241E749B9668 : Step 1) hostname=SENTIA hostpid=2068 isolationlevel=read committed (2) xactid=281367477 currentdb=4 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2013-03-06 10:30:06.94 spid9s         executionStack
2013-03-06 10:30:06.94 spid9s         frame procname=msdb.dbo.sp_syscollector_purge_collection_logs line=45 stmtstart=3600 stmtend=3988 sqlhandle=0x03000400899c5b48eac994005da1000001000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.94 spid9s     DELETE FROM dbo.sysssislog
2013-03-06 10:30:06.94 spid9s             FROM dbo.sysssislog AS s
2013-03-06 10:30:06.94 spid9s             INNER JOIN #purged_log_ids AS i ON i.package_execution_id = s.executionid
2013-03-06 10:30:06.94 spid9s         -- Then delete the actual logs    
2013-03-06 10:30:06.94 spid9s         frame procname=adhoc line=2 stmtstart=28 sqlhandle=0x0100040034b4980cb0306bf70600000000000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.94 spid9s     EXEC [dbo].[sp_syscollector_purge_collection_logs]    
2013-03-06 10:30:06.94 spid9s         inputbuf
2013-03-06 10:30:06.94 spid9s                 EXEC [dbo].[sp_syscollector_purge_collection_logs]
2013-03-06 10:30:06.94 spid9s         process id=process8fe05d0c8 taskpriority=0 logused=0 waitresource=PAGE: 4:1:14191 waittime=6162 ownerId=281367477 transactionname=DELETE lasttranstarted=2013-03-06T10:30:00.473 XDES=0x8fe97cd90 lockMode=U schedulerid=10 kpid=504 status=suspended spid=74 sbid=0 ecid=7 priority=0 trancount=0 lastbatchstarted=2013-03-06T10:30:00.370 lastbatchcompleted=2013-03-06T10:30:00.370 lastattention=1900-01-01T00:00:00.370 clientapp=SQLAgent - TSQL JobStep (Job 0xC555FD2BADD4954E8C7D241E749B9668 : Step 1) hostname=SENTIA hostpid=2068 isolationlevel=read committed (2) xactid=281367477 currentdb=4 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2013-03-06 10:30:06.94 spid9s         executionStack
2013-03-06 10:30:06.94 spid9s         frame procname=msdb.dbo.sp_syscollector_purge_collection_logs line=45 stmtstart=3600 stmtend=3988 sqlhandle=0x03000400899c5b48eac994005da1000001000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.94 spid9s     DELETE FROM dbo.sysssislog
2013-03-06 10:30:06.94 spid9s             FROM dbo.sysssislog AS s
2013-03-06 10:30:06.94 spid9s             INNER JOIN #purged_log_ids AS i ON i.package_execution_id = s.executionid
2013-03-06 10:30:06.94 spid9s         -- Then delete the actual logs    
2013-03-06 10:30:06.94 spid9s         frame procname=adhoc line=2 stmtstart=28 sqlhandle=0x0100040034b4980cb0306bf70600000000000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.94 spid9s     EXEC [dbo].[sp_syscollector_purge_collection_logs]    
2013-03-06 10:30:06.94 spid9s         inputbuf
2013-03-06 10:30:06.94 spid9s                 EXEC [dbo].[sp_syscollector_purge_collection_logs]
2013-03-06 10:30:06.94 spid9s         process id=process5c7917c38 taskpriority=0 logused=0 waitresource=PAGE: 4:1:3319 waittime=6237 ownerId=281367481 transactionname=DELETE lasttranstarted=2013-03-06T10:30:00.473 XDES=0x3db58d790 lockMode=U schedulerid=6 kpid=6080 status=suspended spid=72 sbid=0 ecid=11 priority=0 trancount=0 lastbatchstarted=2013-03-06T10:30:00.370 lastbatchcompleted=2013-03-06T10:30:00.370 lastattention=1900-01-01T00:00:00.370 clientapp=SQLAgent - TSQL JobStep (Job 0x7474D1FB8D371743BF2BC2E7DCAB9ECD : Step 1) hostname=SENTIA hostpid=2068 isolationlevel=read committed (2) xactid=281367481 currentdb=4 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2013-03-06 10:30:06.94 spid9s         executionStack
2013-03-06 10:30:06.94 spid9s         frame procname=msdb.dbo.sp_syscollector_purge_collection_logs line=45 stmtstart=3600 stmtend=3988 sqlhandle=0x03000400899c5b48eac994005da1000001000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.94 spid9s     DELETE FROM dbo.sysssislog
2013-03-06 10:30:06.94 spid9s             FROM dbo.sysssislog AS s
2013-03-06 10:30:06.94 spid9s             INNER JOIN #purged_log_ids AS i ON i.package_execution_id = s.executionid
2013-03-06 10:30:06.94 spid9s         -- Then delete the actual logs    
2013-03-06 10:30:06.94 spid9s         frame procname=adhoc line=2 stmtstart=28 sqlhandle=0x0100040034b4980cb0306bf70600000000000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.94 spid9s     EXEC [dbo].[sp_syscollector_purge_collection_logs]    
2013-03-06 10:30:06.94 spid9s         inputbuf
2013-03-06 10:30:06.94 spid9s                 EXEC [dbo].[sp_syscollector_purge_collection_logs]
2013-03-06 10:30:06.94 spid9s         process id=process5c7917498 taskpriority=0 logused=10000 waittime=4491 schedulerid=6 kpid=10300 status=suspended spid=74 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2013-03-06T10:30:00.370 lastbatchcompleted=2013-03-06T10:30:00.370 lastattention=1900-01-01T00:00:00.370 clientapp=SQLAgent - TSQL JobStep (Job 0xC555FD2BADD4954E8C7D241E749B9668 : Step 1) hostname=SENTIA hostpid=2068 loginname=NETRTLSRV\SvcSQLServerAgent isolationlevel=read committed (2) xactid=281367477 currentdb=4 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2013-03-06 10:30:06.94 spid9s         executionStack
2013-03-06 10:30:06.94 spid9s         frame procname=msdb.dbo.sp_syscollector_purge_collection_logs line=45 stmtstart=3600 stmtend=3988 sqlhandle=0x03000400899c5b48eac994005da1000001000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.94 spid9s     DELETE FROM dbo.sysssislog
2013-03-06 10:30:06.94 spid9s             FROM dbo.sysssislog AS s
2013-03-06 10:30:06.94 spid9s             INNER JOIN #purged_log_ids AS i ON i.package_execution_id = s.executionid
2013-03-06 10:30:06.94 spid9s         -- Then delete the actual logs    
2013-03-06 10:30:06.94 spid9s         frame procname=adhoc line=2 stmtstart=28 sqlhandle=0x0100040034b4980cb0306bf70600000000000000000000000000000000000000000000000000000000000000
2013-03-06 10:30:06.94 spid9s     EXEC [dbo].[sp_syscollector_purge_collection_logs]    
2013-03-06 10:30:06.94 spid9s         inputbuf
2013-03-06 10:30:06.94 spid9s                 EXEC [dbo].[sp_syscollector_purge_collection_logs]
2013-03-06 10:30:06.94 spid9s        resource-list
2013-03-06 10:30:06.94 spid9s         pagelock fileid=1 pageid=7719 dbid=4 subresource=FULL objectname=msdb.dbo.sysssislog id=lock295feb280 mode=U associatedObjectId=72057594046906368
2013-03-06 10:30:06.94 spid9s         owner-list
2013-03-06 10:30:06.94 spid9s         owner id=process8fe05d868 mode=U
2013-03-06 10:30:06.94 spid9s         waiter-list
2013-03-06 10:30:06.94 spid9s         waiter id=process8fe013c38 mode=U requestType=wait
2013-03-06 10:30:06.94 spid9s         pagelock fileid=1 pageid=14367 dbid=4 subresource=FULL objectname=msdb.dbo.sysssislog id=lock8fe9e8480 mode=U associatedObjectId=72057594046906368
2013-03-06 10:30:06.94 spid9s         owner-list
2013-03-06 10:30:06.94 spid9s         owner id=process5c7917498 mode=U
2013-03-06 10:30:06.94 spid9s         waiter-list
2013-03-06 10:30:06.94 spid9s         waiter id=process8fe024928 mode=U requestType=wait
2013-03-06 10:30:06.94 spid9s         pagelock fileid=1 pageid=14367 dbid=4 subresource=FULL objectname=msdb.dbo.sysssislog id=lock8fe9e8480 mode=U associatedObjectId=72057594046906368
2013-03-06 10:30:06.94 spid9s         owner-list
2013-03-06 10:30:06.94 spid9s         owner id=process8fe024928 mode=U requestType=wait
2013-03-06 10:30:06.94 spid9s         waiter-list
2013-03-06 10:30:06.94 spid9s         waiter id=process8fe05d868 mode=U requestType=wait
2013-03-06 10:30:06.94 spid9s         pagelock fileid=1 pageid=12407 dbid=4 subresource=FULL objectname=msdb.dbo.sysssislog id=lock2957af500 mode=U associatedObjectId=72057594046906368
2013-03-06 10:30:06.94 spid9s         owner-list
2013-03-06 10:30:06.94 spid9s         owner id=process38adfe188 mode=U
2013-03-06 10:30:06.94 spid9s         waiter-list
2013-03-06 10:30:06.94 spid9s         waiter id=process8fe013868 mode=U requestType=wait
2013-03-06 10:30:06.94 spid9s         pagelock fileid=1 pageid=14191 dbid=4 subresource=FULL objectname=msdb.dbo.sysssislog id=lock295e26080 mode=U associatedObjectId=72057594046906368
2013-03-06 10:30:06.94 spid9s         owner-list
2013-03-06 10:30:06.94 spid9s         owner id=process89a42e188 mode=U
2013-03-06 10:30:06.94 spid9s         waiter-list
2013-03-06 10:30:06.94 spid9s         waiter id=process38adfe188 mode=U requestType=wait
2013-03-06 10:30:06.94 spid9s         pagelock fileid=1 pageid=14223 dbid=4 subresource=FULL objectname=msdb.dbo.sysssislog id=lock5eeb75800 mode=U associatedObjectId=72057594046906368
2013-03-06 10:30:06.94 spid9s         owner-list
2013-03-06 10:30:06.94 spid9s         owner id=process841fde558 mode=U requestType=wait
2013-03-06 10:30:06.94 spid9s         waiter-list
2013-03-06 10:30:06.94 spid9s         waiter id=process89a42e188 mode=U requestType=wait
2013-03-06 10:30:06.94 spid9s         pagelock fileid=1 pageid=11103 dbid=4 subresource=FULL objectname=msdb.dbo.sysssislog id=lock7d245f300 mode=U associatedObjectId=72057594046906368
2013-03-06 10:30:06.94 spid9s         owner-list
2013-03-06 10:30:06.94 spid9s         owner id=process89a42fc38 mode=U requestType=wait
2013-03-06 10:30:06.94 spid9s         waiter-list
2013-03-06 10:30:06.94 spid9s         waiter id=process8fe025c38 mode=U requestType=wait
2013-03-06 10:30:06.94 spid9s         pagelock fileid=1 pageid=11103 dbid=4 subresource=FULL objectname=msdb.dbo.sysssislog id=lock7d245f300 mode=U associatedObjectId=72057594046906368
2013-03-06 10:30:06.94 spid9s         owner-list
2013-03-06 10:30:06.94 spid9s         owner id=process38a652188 mode=U
2013-03-06 10:30:06.94 spid9s         waiter-list
2013-03-06 10:30:06.94 spid9s         waiter id=process89a42fc38 mode=U requestType=wait
2013-03-06 10:30:06.94 spid9s         pagelock fileid=1 pageid=5583 dbid=4 subresource=FULL objectname=msdb.dbo.sysssislog id=lock823d37f80 mode=U associatedObjectId=72057594046906368
2013-03-06 10:30:06.94 spid9s         owner-list
2013-03-06 10:30:06.94 spid9s         owner id=process8fe024928 mode=U
2013-03-06 10:30:06.94 spid9s         waiter-list
2013-03-06 10:30:06.94 spid9s         waiter id=process7b5794928 mode=U requestType=wait
2013-03-06 10:30:06.94 spid9s         pagelock fileid=1 pageid=14295 dbid=4 subresource=FULL objectname=msdb.dbo.sysssislog id=lock295e91000 mode=U associatedObjectId=72057594046906368
2013-03-06 10:30:06.94 spid9s         owner-list
2013-03-06 10:30:06.94 spid9s         owner id=process8fe04d868 mode=U
2013-03-06 10:30:06.94 spid9s         waiter-list
2013-03-06 10:30:06.94 spid9s         waiter id=process38a652188 mode=U requestType=wait
2013-03-06 10:30:06.94 spid9s         pagelock fileid=1 pageid=14223 dbid=4 subresource=FULL objectname=msdb.dbo.sysssislog id=lock5eeb75800 mode=U associatedObjectId=72057594046906368
2013-03-06 10:30:06.94 spid9s         owner-list
2013-03-06 10:30:06.94 spid9s         owner id=process75a717c38 mode=U
2013-03-06 10:30:06.94 spid9s         waiter-list
2013-03-06 10:30:06.94 spid9s         waiter id=process841fde558 mode=U requestType=wait
2013-03-06 10:30:06.94 spid9s         pagelock fileid=1 pageid=17519 dbid=4 subresource=FULL objectname=msdb.dbo.sysssislog id=lock8cc999200 mode=U associatedObjectId=72057594046906368
2013-03-06 10:30:06.94 spid9s         owner-list
2013-03-06 10:30:06.94 spid9s         owner id=process38a652188 mode=U
2013-03-06 10:30:06.94 spid9s         owner id=process38a632188 mode=U
2013-03-06 10:30:06.94 spid9s         waiter-list
2013-03-06 10:30:06.94 spid9s         waiter id=process8fe035868 mode=U requestType=wait
2013-03-06 10:30:06.95 spid9s         pagelock fileid=1 pageid=3319 dbid=4 subresource=FULL objectname=msdb.dbo.sysssislog id=lock295f21400 mode=U associatedObjectId=72057594046906368
2013-03-06 10:30:06.95 spid9s         owner-list
2013-03-06 10:30:06.95 spid9s         owner id=process5c7917c38 mode=U requestType=wait
2013-03-06 10:30:06.95 spid9s         waiter-list
2013-03-06 10:30:06.95 spid9s         waiter id=process38a632188 mode=U requestType=wait
2013-03-06 10:30:06.95 spid9s         pagelock fileid=1 pageid=14343 dbid=4 subresource=FULL objectname=msdb.dbo.sysssislog id=lock1f1794780 mode=U associatedObjectId=72057594046906368
2013-03-06 10:30:06.95 spid9s         owner-list
2013-03-06 10:30:06.95 spid9s         owner id=process75a717c38 mode=U requestType=wait
2013-03-06 10:30:06.95 spid9s         waiter-list
2013-03-06 10:30:06.95 spid9s         waiter id=process8fe04d868 mode=U requestType=wait
2013-03-06 10:30:06.95 spid9s         pagelock fileid=1 pageid=14343 dbid=4 subresource=FULL objectname=msdb.dbo.sysssislog id=lock1f1794780 mode=U associatedObjectId=72057594046906368
2013-03-06 10:30:06.95 spid9s         owner-list
2013-03-06 10:30:06.95 spid9s         owner id=process8fe05d868 mode=U
2013-03-06 10:30:06.95 spid9s         waiter-list
2013-03-06 10:30:06.95 spid9s         waiter id=process75a717c38 mode=U requestType=wait
2013-03-06 10:30:06.95 spid9s         pagelock fileid=1 pageid=5583 dbid=4 subresource=FULL objectname=msdb.dbo.sysssislog id=lock823d37f80 mode=U associatedObjectId=72057594046906368
2013-03-06 10:30:06.95 spid9s         owner-list
2013-03-06 10:30:06.95 spid9s         owner id=process7b5794928 mode=U requestType=wait
2013-03-06 10:30:06.95 spid9s         waiter-list
2013-03-06 10:30:06.95 spid9s         waiter id=process7785190c8 mode=U requestType=wait
2013-03-06 10:30:06.95 spid9s         pagelock fileid=1 pageid=10327 dbid=4 subresource=FULL objectname=msdb.dbo.sysssislog id=lock3a309ac80 mode=U associatedObjectId=72057594046906368
2013-03-06 10:30:06.95 spid9s         owner-list
2013-03-06 10:30:06.95 spid9s         owner id=process8fe05d868 mode=U
2013-03-06 10:30:06.95 spid9s         waiter-list
2013-03-06 10:30:06.95 spid9s         waiter id=process875c5a928 mode=U requestType=wait
2013-03-06 10:30:06.95 spid9s         pagelock fileid=1 pageid=14191 dbid=4 subresource=FULL objectname=msdb.dbo.sysssislog id=lock295e26080 mode=U associatedObjectId=72057594046906368
2013-03-06 10:30:06.95 spid9s         owner-list
2013-03-06 10:30:06.95 spid9s         owner id=process38adfe188 mode=U requestType=wait
2013-03-06 10:30:06.95 spid9s         waiter-list
2013-03-06 10:30:06.95 spid9s         waiter id=process8fe05d0c8 mode=U requestType=wait
2013-03-06 10:30:06.95 spid9s         pagelock fileid=1 pageid=3319 dbid=4 subresource=FULL objectname=msdb.dbo.sysssislog id=lock295f21400 mode=U associatedObjectId=72057594046906368
2013-03-06 10:30:06.95 spid9s         owner-list
2013-03-06 10:30:06.95 spid9s         owner id=process8fe04d868 mode=U
2013-03-06 10:30:06.95 spid9s         waiter-list
2013-03-06 10:30:06.95 spid9s         waiter id=process5c7917c38 mode=U requestType=wait
2013-03-06 10:30:06.95 spid9s         exchangeEvent id=Pipe3db59d480 WaitType=e_waitPipeGetRow nodeId=1
2013-03-06 10:30:06.95 spid9s         owner-list
2013-03-06 10:30:06.95 spid9s         owner id=process75a717c38
2013-03-06 10:30:06.95 spid9s         owner id=process7785190c8
2013-03-06 10:30:06.95 spid9s         owner id=process875c5a928
2013-03-06 10:30:06.95 spid9s         owner id=process89a42fc38
2013-03-06 10:30:06.95 spid9s         owner id=process38adfe188
2013-03-06 10:30:06.95 spid9s         owner id=process8fe025c38
2013-03-06 10:30:06.95 spid9s         owner id=process8fe013c38
2013-03-06 10:30:06.95 spid9s         owner id=process8fe035868
2013-03-06 10:30:06.95 spid9s         owner id=process8fe04d868
2013-03-06 10:30:06.95 spid9s         owner id=process8fe05d0c8
2013-03-06 10:30:06.95 spid9s         owner id=process7b5794928
2013-03-06 10:30:06.95 spid9s         waiter-list
2013-03-06 10:30:06.95 spid9s         waiter id=process5c7917498


And here the relevant jobs :

job_id originating_server_id name enabled description start_step_id category_id owner_sid notify_level_eventlog notify_level_email notify_level_netsend notify_level_page notify_email_operator_id notify_netsend_operator_id notify_page_operator_id delete_level date_created date_modified version_number
2BFD55C5-D4AD-4E95-8C7D-241E749B9668 0 collection_set_3_upload 1 No description available. 1 8 0x01 2 0 0 0 0 0 0 0 2013-01-25 17:22:15.130 2013-02-06 09:01:46.770 7
FBD17474-378D-4317-BF2B-C2E7DCAB9ECD 0 collection_set_2_upload 1 No description available. 1 8 0x01 2 0 0 0 0 0 0 0 2013-01-25 17:22:15.163 2013-02-06 09:01:46.757 7


Posted by Microsoft on 5/29/2013 at 3:28 PM
Could you please send us the deadlock graphs?.

Thanks
Sethu Srinivasan [MSFT]
Posted by Kunk on 3/4/2013 at 1:13 AM
Status is now "won't fix". But no explation! What is wrong with my solution ?

Posted by Kunk on 2/18/2013 at 4:31 AM
No further deadlock. So it seems that only adding the index will reduce the amount aof deadlocks significantly.

Regards
Wolfgang Kunk
Posted by Kunk on 2/13/2013 at 8:17 AM
Up to now there aren't any further deadlocks. Now I switched back to the original procs to see if only the additional index on sysssislog prevents deadlocks.

Regards
Wolfgang Kunk
Posted by Kunk on 2/7/2013 at 12:06 AM
The queryplan shows that the delete-statement executes parallel query threads. But the deadlock occured for spids of jobs for system collection set 2 an 3. And I see clustered index scans on sysssislogs. Therefore I added an additional index to that table :

drop index sysssislog.ssl_idx1
go
create index ssl_idx1 on sysssislog(executionid)
go

Up to now everything is fine. No deadlocks since yesterday.

But I cannot foresee the influence of that index on other ssis-packages writing log entries to sysssislogs. :-(

Regards
Wolfgang Kunk
Posted by Kunk on 2/6/2013 at 4:23 AM
I performed a test with my own implementation of the purge procedure. In fact a took the SQL Server 2012 implementation an added a select for the collection_set_id. So every job only purges rows for its own collection set.
But the result is negative. I still got deadlocks on the delete statement on table sysssislogs. I cannot see why this is still happen. Only thing I can imagine is that the lock ist escalated to a page or table lock. the deadlock graph shows something like "pagelock fileid=1 pageid=39055 ....".

Regards
Wolfgang Kunk
Posted by Microsoft on 2/4/2013 at 5:44 PM
Thank you for reporting this issue - we are investigating and will update you when we have more information.

Thanks,

Alex Grach [MSFT SQL SERVER]
Posted by Kunk on 2/4/2013 at 7:46 AM
I just investigate how the proc sp_syscollector_purge_collection_logs works.
First of all I agree to a statement in feedback 504557, that the execution plan indicates a missing index on [syscollector_execution_log_internal] ([collection_set_id]) INCLUDE ([log_id],[finish_time]). OK but it is indicated for the select to populate the temporary table #purged_log_ids.
As the deadlock detection shows deadlocks on the delete statement on table sysssislog I don't think that a new index on table is relevant.
I think the main problem is that every purge job purges data for all data collection sets. As all jobs are starting the same time the chance of having deadlocks is very high. I found workarounds to schedule the jobs with different starting time. I think it would be better if every purge job only delete the data for its own collection set!

I checked the implementation of the proc in SQL Server 2012. It is slightly different, but still deletes rows for every collection set. I assume that the deadlock will still happen in SQL Server 2012.


Regards
Wolfgang Kunk
Sign in to post a workaround.
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement