December 22, 2014 at 3:25 am
i have pasted the deadlock details from Error log, i am not able to find out how/why system object is participating in deadlock as victime/creator.
2014-12-22 00:37:57.750 spid49s deadlock-list
2014-12-22 00:37:57.750 spid49s deadlock victim=process5e13783868
2014-12-22 00:37:57.750 spid49s process-list
2014-12-22 00:37:57.750 spid49s process id=process5e13783868 taskpriority=5 logused=10000 waitresource=DATABASE: 59 waittime=2545 schedulerid=19 kpid=5572 status=suspended spid=52 sbid=0 ecid=0 priority=-5 trancount=2 lastbatchstarted=2014-12-22T00:37:48.767 lastbatchcompleted=2014-1
2014-12-22 00:37:57.750 spid49s executionStack
2014-12-22 00:37:57.750 spid49s frame procname=adhoc line=89 stmtstart=4274 stmtend=4812 sqlhandle=0x020000007c952231f22e8c5ef540d316f6e9b401b077984e0000000000000000000000000000000000000000
2014-12-22 00:37:57.750 spid49s insert into #dbList (dbid)
2014-12-22 00:37:57.750 spid49s selectdatabase_id
2014-12-22 00:37:57.750 spid49s fromsys.databases with (readpast)
2014-12-22 00:37:57.750 spid49s whereuser_access <> 1-- Not SINGLE USER
2014-12-22 00:37:57.750 spid49s andstate = 0-- ONLINE
2014-12-22 00:37:57.750 spid49s and is_auto_close_on = 0-- Not auto close
2014-12-22 00:37:57.750 spid49s andhas_dbaccess(name) <> 0;-- Have Access.
2014-12-22 00:37:57.750 spid49s inputbuf
2014-12-22 00:37:57.750 spid49s /*
2014-12-22 00:37:57.750 spid49s -- SoSSE 20.0.1.333
2014-12-22 00:37:57.750 spid49s -- File - Procedure\sqlserver_spotlight\2005\QS_GeneralDBStats.sql
2014-12-22 00:37:57.750 spid49s -- *
2014-12-22 00:37:57.750 spid49s -- * Copyright 2014 Dell Inc. ALL RIGHTS RESERVED
2014-12-22 00:37:57.750 spid49s -- *
2014-12-22 00:37:57.750 spid49s */
2014-12-22 00:37:57.750 spid49s SET DEADLOCK_PRIORITY -10
2014-12-22 00:37:57.750 spid49s set nocount on
2014-12-22 00:37:57.750 spid49s set lock_timeout 100
2014-12-22 00:37:57.750 spid49s DECLARE
2014-12-22 00:37:57.750 spid49s @ErrorMessage NVARCHAR(4000),
2014-12-22 00:37:57.750 spid49s @ErrorNumber INT,
2014-12-22 00:37:57.750 spid49s @ErrorSeverity INT,
2014-12-22 00:37:57.750 spid49s @ErrorState INT,
2014-12-22 00:37:57.750 spid49s @ErrorLine INT,
2014-12-22 00:37:57.750 spid49s @ErrorProcedure NVARCHAR(200);
2014-12-22 00:37:57.750 spid49s BEGIN TRY
2014-12-22 00:37:57.750 spid49s use tempdb
2014-12-22 00:37:57.750 spid49s declare@DatabaseCntint
2014-12-22 00:37:57.750 spid49s declare@DataFileCntint
2014-12-22 00:37:57.750 spid49s declare@DataFileKBfloat(53)
2014-12-22 00:37:57.750 spid49s declare@DataFilePctUsedfloat(53)
2014-12-22 00:37:57.750 spid49s declare@FilegroupCntint
2014-12-22 00:37:57.750 spid49s declare@LogFileCntint
2014-12-22 00:37:57.750 spid49s declare@LogFileKBfloat(53)
2014-12-22 00:37:57.750 spid49s declare@LogFilePctUsedfloat(53)
2014-12-22 00:37:57.750 spid49s select@DatabaseCnt= 0
2014-12-22 00:37:57.750 spid49s ,@DataFileCnt= 0
2014-12-22 00:37:57.750 spid49s ,@DataFileKB= 0
2014-12-22 00:37:57.750 spid49s ,@DataFilePctUsed= 0
2014-12-22 00:37:57.750 spid49s ,@FilegroupCnt= 0
2014-12-22 00:37:57.750 spid49s ,@LogFileCnt= 0
2014-12-22 00:37:57.750 spid49s ,@LogFileKB= 0
2014-12-22 00:37:57.750 spid49s ,@LogFilePctUsed= 0
2014-12-22 00:37:57.750 spid49s declare @dbcc_logspace table
2014-12-22 00:37:57.750 spid49s (
2014-12-22 00:37:57.750 spid49s [DBName] [sysname] NOT
2014-12-22 00:37:57.750 spid49s process id=process6e61498 taskpriority=0 logused=15300 waitresource=KEY: 1:281474978545664 (b0242a552b12) waittime=4484 ownerId=2420582540 transactionname=dbdestroy lasttranstarted=2014-12-22T00:37:49.957 XDES=0x2722a34d28 lockMode=X schedulerid=29 kpid
2014-12-22 00:37:57.750 spid49s executionStack
2014-12-22 00:37:57.750 spid49s frame procname=adhoc line=1 stmtstart=64 stmtend=104 sqlhandle=0x02000000e50c9f0fc2ef1ed0e82271f253adf2fe7413e6c90000000000000000000000000000000000000000
2014-12-22 00:37:57.750 spid49s DROP DATABASE ESS_SS
2014-12-22 00:37:57.750 spid49s frame procname=COMP_REPORT.dbo.ap_rpt_CreateSnapShot line=114 stmtstart=8866 stmtend=8916 sqlhandle=0x03001e0030acaf5932b0090000a4000001000000000000000000000000000000000000000000000000000000
2014-12-22 00:37:57.750 spid49s exec (@SQLCmd)
2014-12-22 00:37:57.750 spid49s frame procname=adhoc line=1 sqlhandle=0x01000200ea5b801e70d3d0694b00000000000000000000000000000000000000000000000000000000000000
2014-12-22 00:37:57.750 spid49s exec COMP_REPORT.dbo.ap_rpt_CreateSnapShot @active_snapshot_count= 6
2014-12-22 00:37:57.750 spid49s inputbuf
2014-12-22 00:37:57.750 spid49s exec COMP_REPORT.dbo.ap_rpt_CreateSnapShot @active_snapshot_count= 6
2014-12-22 00:37:57.750 spid49s resource-list
2014-12-22 00:37:57.750 spid49s databaselock subresource=FULL dbid=59 dbname=unknown lockPartition=0 id=lock4659d79a80 mode=X
2014-12-22 00:37:57.750 spid49s owner-list
2014-12-22 00:37:57.750 spid49s owner id=process6e61498 mode=X
2014-12-22 00:37:57.750 spid49s waiter-list
2014-12-22 00:37:57.750 spid49s waiter id=process5e13783868 mode=S requestType=wait
2014-12-22 00:37:57.750 spid49s keylock hobtid=281474978545664 dbid=1 objectname=master.sys.sysdbreg indexname=clst id=lock3ac0f57400 mode=U associatedObjectId=281474978545664
2014-12-22 00:37:57.750 spid49s owner-list
2014-12-22 00:37:57.750 spid49s owner id=process5e13783868 mode=S
2014-12-22 00:37:57.750 spid49s waiter-list
2014-12-22 00:37:57.750 spid49s waiter id=process6e61498 mode=X requestType=convert
2014-12-22 00:42:07.540 spid183 Starting up database 'COMP_TRACK_SS42'.
2014-12-22 00:42:07.550 spid183 Recovery of database 'COMP_TRACK_SS42' (48) is 0% complete (approximately 1200 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2014-12-22 00:42:07.570 spid183 Recovery of database 'COMP_TRACK_SS42' (48) is 0% complete (approximately 1200 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2014-12-22 00:42:07.570 spid183 Recovery of database 'COMP_TRACK_SS42' (48) is 0% complete (approximately 1200 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2014-12-22 00:42:09.090 spid183 343 transactions rolled forward in database 'COMP_TRACK_SS42' (48:0). This is an informational message only. No user action is required.
2014-12-22 00:42:09.120 spid183 0 transactions rolled back in database 'COMP_TRACK_SS42' (48:0). This is an informational message only. No user action is required.
2014-12-22 00:42:09.120 spid183 Recovery is writing a checkpoint in database 'COMP_TRACK_SS42' (48). This is an informational message only. No user action is required.
2014-12-22 00:42:12.930 spid183 Recovery completed for database COMP_TRACK_SS42 (database ID 48) in 5 second(s) (analysis 25 ms, redo 1521 ms, undo 14 ms.) This is an informational message only. No user action is required.
2014-12-22 00:42:22.580 spid183 Starting up database 'ESS_SS42'.
2014-12-22 00:42:22.590 spid183 Recovery of database 'ESS_SS42' (59) is 0% complete (approximately 918 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2014-12-22 00:42:22.610 spid183 Recovery of database 'ESS_SS42' (59) is 0% complete (approximately 918 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2014-12-22 00:42:23.420 spid183 635 transactions rolled forward in database 'ESS_SS42' (59:0). This is an informational message only. No user action is required.
2014-12-22 00:42:23.450 spid183 0 transactions rolled back in database 'ESS_SS42' (59:0). This is an informational message only. No user action is required.
2014-12-22 00:42:23.450 spid183 Recovery is writing a checkpoint in database 'ESS_SS42' (59). This is an informational message only. No user action is required.
2014-12-22 00:42:27.280 spid183 Recovery completed for database ESS_SS42 (database ID 59) in 4 second(s) (analysis 31 ms, redo 806 ms, undo 24 ms.) This is an informational message only. No user action is required.
2014-12-22 00:42:36.930 spid183 Starting up database 'ESS_EMARKET_SS42'.
2014-12-22 00:42:36.930 spid183 Recovery of database 'ESS_EMARKET_SS42' (60) is 0% complete (approximately 691 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2014-12-22 00:42:36.950 spid183 Recovery of database 'ESS_EMARKET_SS42' (60) is 0% complete (approximately 691 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2014-12-22 00:42:37.570 spid183 553 transactions rolled forward in database 'ESS_EMARKET_SS42' (60:0). This is an informational message only. No user action is required.
2014-12-22 00:42:37.590 spid183 0 transactions rolled back in database 'ESS_EMARKET_SS42' (60:0). This is an informational message only. No user action is required.
2014-12-22 00:42:37.590 spid183 Recovery is writing a checkpoint in database 'ESS_EMARKET_SS42' (60). This is an informational message only. No user action is required.
2014-12-22 00:42:41.440 spid183 Recovery completed for database ESS_EMARKET_SS42 (database ID 60) in 4 second(s) (analysis 22 ms, redo 621 ms, undo 12 ms.) This is an informational message only. No user action is required.
2014-12-22 00:42:55.740 spid36s deadlock-list
2014-12-22 00:42:55.740 spid36s deadlock victim=process5e13783868
2014-12-22 00:42:55.740 spid36s process-list
2014-12-22 00:42:55.740 spid36s process id=process5e13783868 taskpriority=5 logused=10000 waitresource=DATABASE: 51 waittime=539 schedulerid=19 kpid=5572 status=suspended spid=52 sbid=0 ecid=0 priority=-5 trancount=2 lastbatchstarted=2014-12-22T00:42:48.963 lastbatchcompleted=2014-12
2014-12-22 00:42:55.740 spid36s executionStack
2014-12-22 00:42:55.740 spid36s frame procname=adhoc line=89 stmtstart=4274 stmtend=4812 sqlhandle=0x020000007c952231f22e8c5ef540d316f6e9b401b077984e0000000000000000000000000000000000000000
2014-12-22 00:42:55.740 spid36s insert into #dbList (dbid)
2014-12-22 00:42:55.740 spid36s selectdatabase_id
2014-12-22 00:42:55.740 spid36s fromsys.databases with (readpast)
2014-12-22 00:42:55.740 spid36s whereuser_access <> 1-- Not SINGLE USER
2014-12-22 00:42:55.740 spid36s andstate = 0-- ONLINE
2014-12-22 00:42:55.740 spid36s and is_auto_close_on = 0-- Not auto close
2014-12-22 00:42:55.740 spid36s andhas_dbaccess(name) <> 0;-- Have Access.
2014-12-22 00:42:55.740 spid36s inputbuf
2014-12-22 00:42:55.740 spid36s /*
2014-12-22 00:42:55.740 spid36s -- SoSSE 20.0.1.333
2014-12-22 00:42:55.740 spid36s -- File - Procedure\sqlserver_spotlight\2005\QS_GeneralDBStats.sql
2014-12-22 00:42:55.740 spid36s -- *
2014-12-22 00:42:55.740 spid36s -- * Copyright 2014 Dell Inc. ALL RIGHTS RESERVED
2014-12-22 00:42:55.740 spid36s -- *
2014-12-22 00:42:55.740 spid36s */
2014-12-22 00:42:55.740 spid36s SET DEADLOCK_PRIORITY -10
2014-12-22 00:42:55.740 spid36s set nocount on
2014-12-22 00:42:55.740 spid36s set lock_timeout 100
2014-12-22 00:42:55.740 spid36s DECLARE
2014-12-22 00:42:55.740 spid36s @ErrorMessage NVARCHAR(4000),
2014-12-22 00:42:55.740 spid36s @ErrorNumber INT,
2014-12-22 00:42:55.740 spid36s @ErrorSeverity INT,
2014-12-22 00:42:55.740 spid36s @ErrorState INT,
2014-12-22 00:42:55.740 spid36s @ErrorLine INT,
2014-12-22 00:42:55.740 spid36s @ErrorProcedure NVARCHAR(200);
2014-12-22 00:42:55.740 spid36s BEGIN TRY
2014-12-22 00:42:55.740 spid36s use tempdb
2014-12-22 00:42:55.740 spid36s declare@DatabaseCntint
2014-12-22 00:42:55.740 spid36s declare@DataFileCntint
2014-12-22 00:42:55.740 spid36s declare@DataFileKBfloat(53)
2014-12-22 00:42:55.740 spid36s declare@DataFilePctUsedfloat(53)
2014-12-22 00:42:55.740 spid36s declare@FilegroupCntint
2014-12-22 00:42:55.740 spid36s declare@LogFileCntint
2014-12-22 00:42:55.740 spid36s declare@LogFileKBfloat(53)
2014-12-22 00:42:55.740 spid36s declare@LogFilePctUsedfloat(53)
2014-12-22 00:42:55.740 spid36s select@DatabaseCnt= 0
2014-12-22 00:42:55.740 spid36s ,@DataFileCnt= 0
2014-12-22 00:42:55.740 spid36s ,@DataFileKB= 0
2014-12-22 00:42:55.740 spid36s ,@DataFilePctUsed= 0
2014-12-22 00:42:55.740 spid36s ,@FilegroupCnt= 0
2014-12-22 00:42:55.740 spid36s ,@LogFileCnt= 0
2014-12-22 00:42:55.740 spid36s ,@LogFileKB= 0
2014-12-22 00:42:55.740 spid36s ,@LogFilePctUsed= 0
2014-12-22 00:42:55.740 spid36s declare @dbcc_logspace table
2014-12-22 00:42:55.740 spid36s (
2014-12-22 00:42:55.740 spid36s [DBName] [sysname] NOT
2014-12-22 00:42:55.740 spid36s process id=processf60ece188 taskpriority=0 logused=16420 waitresource=KEY: 1:281474978545664 (010718597b81) waittime=483 ownerId=2421599285 transactionname=dbdestroy lasttranstarted=2014-12-22T00:42:51.933 XDES=0x58906123a8 lockMode=X schedulerid=5 kpid
2014-12-22 00:42:55.740 spid36s executionStack
2014-12-22 00:42:55.740 spid36s frame procname=adhoc line=1 stmtstart=82 stmtend=140 sqlhandle=0x02000000cce3b917f76b6d12c39468688c58d71e2e14517d0000000000000000000000000000000000000000
2014-12-22 00:42:55.740 spid36s DROP DATABASE ESS_EMARKET_SS1
2014-12-22 00:42:55.740 spid36s frame procname=COMP_REPORT.dbo.ap_rpt_CreateSnapShot line=114 stmtstart=8866 stmtend=8916 sqlhandle=0x03001e0030acaf5932b0090000a4000001000000000000000000000000000000000000000000000000000000
2014-12-22 00:42:55.740 spid36s exec (@SQLCmd)
2014-12-22 00:42:55.740 spid36s frame procname=adhoc line=1 sqlhandle=0x01000200ea5b801e90ffd2c50600000000000000000000000000000000000000000000000000000000000000
2014-12-22 00:42:55.740 spid36s exec COMP_REPORT.dbo.ap_rpt_CreateSnapShot @active_snapshot_count= 6
2014-12-22 00:42:55.740 spid36s inputbuf
2014-12-22 00:42:55.740 spid36s exec COMP_REPORT.dbo.ap_rpt_CreateSnapShot @active_snapshot_count= 6
2014-12-22 00:42:55.740 spid36s resource-list
2014-12-22 00:42:55.740 spid36s databaselock subresource=FULL dbid=51 dbname=unknown lockPartition=0 id=lock506026d580 mode=X
2014-12-22 00:42:55.740 spid36s owner-list
2014-12-22 00:42:55.740 spid36s owner id=processf60ece188 mode=X
2014-12-22 00:42:55.740 spid36s waiter-list
2014-12-22 00:42:55.740 spid36s waiter id=process5e13783868 mode=S requestType=wait
2014-12-22 00:42:55.740 spid36s keylock hobtid=281474978545664 dbid=1 objectname=master.sys.sysdbreg indexname=clst id=lock13b9289080 mode=U associatedObjectId=281474978545664
2014-12-22 00:42:55.740 spid36s owner-list
2014-12-22 00:42:55.740 spid36s owner id=process5e13783868 mode=S
2014-12-22 00:42:55.740 spid36s waiter-list
2014-12-22 00:42:55.740 spid36s waiter id=processf60ece188 mode=X requestType=convert
2014-12-22 00:47:07.210 spid97 Starting up database 'COMP_TRACK_SS47'.
2014-12-22 00:47:07.210 spid97 Recovery of database 'COMP_TRACK_SS47' (49) is 0% complete (approximately 1200 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2014-12-22 00:47:07.240 spid97 Recovery of database 'COMP_TRACK_SS47' (49) is 0% complete (approximately 1200 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2014-12-22 00:47:07.240 spid97 Recovery of database 'COMP_TRACK_SS47' (49) is 0% complete (approximately 1200 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2014-12-22 00:47:08.710 spid97 348 transactions rolled forward in database 'COMP_TRACK_SS47' (49:0). This is an informational message only. No user action is required.
2014-12-22 00:47:08.740 spid97 0 transactions rolled back in database 'COMP_TRACK_SS47' (49:0). This is an informational message only. No user action is required.
2014-12-22 00:47:08.740 spid97 Recovery is writing a checkpoint in database 'COMP_TRACK_SS47' (49). This is an informational message only. No user action is required.
2014-12-22 00:47:12.550 spid97 Recovery completed for database COMP_TRACK_SS47 (database ID 49) in 5 second(s) (analysis 25 ms, redo 1467 ms, undo 17 ms.) This is an informational message only. No user action is required.
2014-12-22 00:47:22.150 spid97 Starting up database 'ESS_SS47'.
2014-12-22 00:47:22.150 spid97 Recovery of database 'ESS_SS47' (50) is 0% complete (approximately 948 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2014-12-22 00:47:22.180 spid97 Recovery of database 'ESS_SS47' (50) is 0% complete (approximately 948 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2014-12-22 00:47:23.020 spid97 703 transactions rolled forward in database 'ESS_SS47' (50:0). This is an informational message only. No user action is required.
2014-12-22 00:47:23.050 spid97 0 transactions rolled back in database 'ESS_SS47' (50:0). This is an informational message only. No user action is required.
2014-12-22 00:47:23.050 spid97 Recovery is writing a checkpoint in database 'ESS_SS47' (50). This is an informational message only. No user action is required.
2014-12-22 00:47:27.230 spid97 Recovery completed for database ESS_SS47 (database ID 50) in 5 second(s) (analysis 30 ms, redo 833 ms, undo 18 ms.) This is an informational message only. No user action is required.
2014-12-22 00:47:36.810 spid97 Starting up database 'ESS_EMARKET_SS47'.
2014-12-22 00:47:36.810 spid97 Recovery of database 'ESS_EMARKET_SS47' (51) is 0% complete (approximately 699 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2014-12-22 00:47:36.830 spid97 Recovery of database 'ESS_EMARKET_SS47' (51) is 0% complete (approximately 699 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2014-12-22 00:47:37.490 spid97 570 transactions rolled forward in database 'ESS_EMARKET_SS47' (51:0). This is an informational message only. No user action is required.
2014-12-22 00:47:37.500 spid97 0 transactions rolled back in database 'ESS_EMARKET_SS47' (51:0). This is an informational message only. No user action is required.
2014-12-22 00:47:37.500 spid97 Recovery is writing a checkpoint in database 'ESS_EMARKET_SS47' (51). This is an informational message only. No user action is required.
2014-12-22 00:47:41.320 spid97 Recovery completed for database ESS_EMARKET_SS47 (database ID 51) in 4 second(s) (analysis 21 ms, redo 658 ms, undo 7 ms.) This is an informational message only. No user action is required.
2014-12-22 00:47:53.710 spid120s deadlock-list
2014-12-22 00:47:53.710 spid120s deadlock victim=process5449868
process-list
process id=process5449868 taskpriority=5 logused=10000 waitresource=DATABASE: 53 waittime=2010 schedulerid=17 kpid=6340 status=suspended spid=52 sbid=0 ecid=0 priority=-5 trancount=2 lastbatchstarted=2014-12-22T00:47:48.373 lastbatchcompleted=2014-12-2
executionStack
frame procname=adhoc line=89 stmtstart=4274 stmtend=4812 sqlhandle=0x020000007c952231f22e8c5ef540d316f6e9b401b077984e0000000000000000000000000000000000000000
2014-12-22 00:47:53.710 spid120s insert into #dbList (dbid)
2014-12-22 00:47:53.710 spid120s selectdatabase_id
2014-12-22 00:47:53.710 spid120s fromsys.databases with (readpast)
2014-12-22 00:47:53.710 spid120s whereuser_access <> 1-- Not SINGLE USER
2014-12-22 00:47:53.710 spid120s andstate = 0-- ONLINE
2014-12-22 00:47:53.710 spid120s and is_auto_close_on = 0-- Not auto close
2014-12-22 00:47:53.710 spid120s andhas_dbaccess(name) <> 0;-- Have Access.
inputbuf
2014-12-22 00:47:53.710 spid120s /*
2014-12-22 00:47:53.710 spid120s -- SoSSE 20.0.1.333
2014-12-22 00:47:53.710 spid120s -- File - Procedure\sqlserver_spotlight\2005\QS_GeneralDBStats.sql
2014-12-22 00:47:53.710 spid120s -- *
2014-12-22 00:47:53.710 spid120s -- * Copyright 2014 Dell Inc. ALL RIGHTS RESERVED
2014-12-22 00:47:53.710 spid120s -- *
2014-12-22 00:47:53.710 spid120s */
2014-12-22 00:47:53.710 spid120s SET DEADLOCK_PRIORITY -10
2014-12-22 00:47:53.710 spid120s set nocount on
2014-12-22 00:47:53.710 spid120s set lock_timeout 100
2014-12-22 00:47:53.710 spid120s DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200);
2014-12-22 00:47:53.710 spid120s BEGIN TRY
use tempdb
2014-12-22 00:47:53.710 spid120s declare@DatabaseCntint
2014-12-22 00:47:53.710 spid120s declare@DataFileCntint
2014-12-22 00:47:53.710 spid120s declare@DataFileKBfloat(53)
2014-12-22 00:47:53.710 spid120s declare@DataFilePctUsedfloat(53)
2014-12-22 00:47:53.710 spid120s declare@FilegroupCntint
2014-12-22 00:47:53.710 spid120s declare@LogFileCntint
2014-12-22 00:47:53.710 spid120s declare@LogFileKBfloat(53)
2014-12-22 00:47:53.710 spid120s declare@LogFilePctUsedfloat(53)
2014-12-22 00:47:53.710 spid120s select@DatabaseCnt= 0
2014-12-22 00:47:53.710 spid120s ,@DataFileCnt= 0
2014-12-22 00:47:53.710 spid120s ,@DataFileKB= 0
2014-12-22 00:47:53.710 spid120s ,@DataFilePctUsed= 0
2014-12-22 00:47:53.710 spid120s ,@FilegroupCnt= 0
2014-12-22 00:47:53.710 spid120s ,@LogFileCnt= 0
2014-12-22 00:47:53.710 spid120s ,@LogFileKB= 0
2014-12-22 00:47:53.710 spid120s ,@LogFilePctUsed= 0
2014-12-22 00:47:53.710 spid120s declare @dbcc_logspace table
2014-12-22 00:47:53.710 spid120s (
2014-12-22 00:47:53.710 spid120s [DBName] [sysname] NOT
process id=process3285fb6188 taskpriority=0 logused=15408 waitresource=KEY: 1:281474978545664 (c06e4447f2f8) waittime=1954 ownerId=2422498562 transactionname=dbdestroy lasttranstarted=2014-12-22T00:47:48.420 XDES=0x1ef90fb6a8 lockMode=X schedulerid=16 k
executionStack
frame procname=adhoc line=1 stmtstart=66 stmtend=108 sqlhandle=0x0200000008eac41e9ba900f1edf72d411c7ac4ecc42cedec0000000000000000000000000000000000000000
2014-12-22 00:47:53.710 spid120s DROP DATABASE ESS_SS1
frame procname=COMP_REPORT.dbo.ap_rpt_CreateSnapShot line=114 stmtstart=8866 stmtend=8916 sqlhandle=0x03001e0030acaf5932b0090000a4000001000000000000000000000000000000000000000000000000000000
2014-12-22 00:47:53.710 spid120s exec (@SQLCmd)
frame procname=adhoc line=1 sqlhandle=0x01000200ea5b801ef0988bc15200000000000000000000000000000000000000000000000000000000000000
2014-12-22 00:47:53.710 spid120s exec COMP_REPORT.dbo.ap_rpt_CreateSnapShot @active_snapshot_count= 6
inputbuf
2014-12-22 00:47:53.710 spid120s exec COMP_REPORT.dbo.ap_rpt_CreateSnapShot @active_snapshot_count= 6
resource-list
databaselock subresource=FULL dbid=53 dbname=unknown lockPartition=0 id=lock462eacf100 mode=X
owner-list
owner id=process3285fb6188 mode=X
waiter-list
waiter id=process5449868 mode=S requestType=wait
keylock hobtid=281474978545664 dbid=1 objectname=master.sys.sysdbreg indexname=clst id=lock10a2c088300 mode=U associatedObjectId=281474978545664
owner-list
owner id=process5449868 mode=S
waiter-list
waiter id=process3285fb6188 mode=X requestType=convert
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 22, 2014 at 8:46 am
I can't take sufficient time to wade through all of that output, but it seems that you are hitting system tables with activity (read and possibly write with a create database in there?). System tables are subject to deadlocks just like any other tables.
It seems that your sproc may be a start up one? If so, I would first recommend that you put in a WAITFOR DELAY 'some number of seconds or minutes' to allow all normal SQL Server startup activities to complete to see if that keeps the deadlocks from happening.
If that doesn't work, then you are down to the normal ways of addressing deadlocks. Proper indexing is a frequent magic bullet for deadlocks. You may not be able to do this though, depending on the exact offending object.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 22, 2014 at 9:27 am
If you shut down Spotlight, does the deadlock go away? If so, time to call customer support.
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 26, 2015 at 2:42 pm
Find the file Quest \sqlserver_spotlight\2005\QS_GeneralDBStats.sql and change (readpast) with(nolock)
March 26, 2015 at 9:43 pm
As Gail mentioned better call customer care.. System Tables part of deadlock is not a good sign i feel.
March 27, 2015 at 1:48 am
You as a tribe right there is a graph slash Louis Marcus the living God by my way anyway I still have to eat my last meal of the day which were consistent vet so is Greek yogurt with a slow release and a routine the flavor is I'll cookies n cream delicious prime Perfect Garcinia Cambogia[/url] interesting this integral role in this up to somebody that's what we want to-do. I think you come back and you know deep down to bed about bodybuilding give you some ideas from precisions a lot of people did not get my last video I Ashley when I said that you know Phil Heath indeed technically he's going to win other mister Libya’s is not right up picking it up or bass .
For more information please contact us at --> > >> > > > http://www.optimalstackfacts.org/perfect-garcinia-cambogia/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply