Version 5.0.8.2656 Deadlock

crimdoncrimdon Posts: 53 Bronze 2
Started getting this from the Redgate database:

deadlock-list
deadlock victim=process32236b0c8
process-list
process id=process32236b0c8 taskpriority=0 logused=220 waitresource=KEY: 61:72057594096910336 (08226f03141a) waittime=7395 ownerId=57084541 transactionname=user_transaction lasttranstarted=2016-02-24T10:22:36.527 XDES=0x374afb6a8 lockMode=RangeS-U schedulerid=4 kpid=9400 status=suspended spid=151 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2016-02-24T10:22:36.527 lastbatchcompleted=2016-02-24T10:22:36.527 lastattention=1900-01-01T00:00:00.527 clientapp=SQL Monitor - Repository hostname=xxx hostpid=4664 loginname=xxx isolationlevel=read committed (2) xactid=57084541 currentdb=61 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=adhoc line=1 sqlhandle=0x02000000b54bc92c1cbffa916d3131c082a19adffe00f8f50000000000000000000000000000000000000000
insert bulk [data].[Cluster_SqlServer_TopQueries_QueryWaitStats_Sightings] ([Id] BigInt, [SightingDate] BigInt) with (CHECK_CONSTRAINTS)
inputbuf
insert bulk [data].[Cluster_SqlServer_TopQueries_QueryWaitStats_Sightings] ([Id] BigInt, [SightingDate] BigInt) with (CHECK_CONSTRAINTS)
process id=process2948cd498 taskpriority=0 logused=192600 waitresource=KEY: 61:72057594096910336 (913c11fc1262) waittime=3947 ownerId=57084307 transactionname=user_transaction lasttranstarted=2016-02-24T10:22:35.887 XDES=0x293c2cbc0 lockMode=X schedulerid=3 kpid=1200 status=suspended spid=57 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2016-02-24T10:22:35.887 lastbatchcompleted=2016-02-24T10:22:35.887 lastattention=2016-02-24T09:49:10.967 clientapp=SQL Monitor - Repository hostname=xxx hostpid=4664 loginname=xxx isolationlevel=snapshot (5) xactid=57084307 currentdb=61 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=RedGateMonitor.data.Cluster_SqlServer_TopQueries_QueryWaitStats_Sightings_DeleteChunk line=18 stmtstart=1080 stmtend=2214 sqlhandle=0x03003d00cf4ee43875a10c01a6a4000001000000000000000000000000000000000000000000000000000000
DELETE [data].[Cluster_SqlServer_TopQueries_QueryWaitStats_Sightings]
FROM @Chunk c
INNER JOIN [data].[Cluster_SqlServer_TopQueries_QueryWaitStats_Sightings] d WITH (ROWLOCK) ON c.[SightingDate] = d.[SightingDate] AND c.[Id] = d.[Id]
-- FORCE ORDER: The order above is carefully chosen, and this prevents pathological orders.
-- LOOP JOIN: Profiling shows that this tends to improve performance, as long as the number of rows to be deleted is small
-- compared to the number of rows in the table, by encouraging index seeks.
OPTION (FORCE ORDER, LOOP JOIN, MAXDOP 1)
inputbuf
Proc [Database Id = 61 Object Id = 954486479]
resource-list
keylock hobtid=72057594096910336 dbid=61 objectname=RedGateMonitor.data.Cluster_SqlServer_TopQueries_QueryWaitStats_Sightings indexname=Cluster_SqlServer_TopQueries_QueryWaitStats_Sightings_Id_SightingDate id=lock37f07ec80 mode=X associatedObjectId=72057594096910336
owner-list
owner id=process2948cd498 mode=X
waiter-list
waiter id=process32236b0c8 mode=RangeS-U requestType=wait
keylock hobtid=72057594096910336 dbid=61 objectname=RedGateMonitor.data.Cluster_SqlServer_TopQueries_QueryWaitStats_Sightings indexname=Cluster_SqlServer_TopQueries_QueryWaitStats_Sightings_Id_SightingDate id=lock33503c600 mode=RangeS-U associatedObjectId=72057594096910336
owner-list
owner id=process32236b0c8 mode=RangeS-U
waiter-list
waiter id=process2948cd498 mode=X requestType=wait

Comments

  • Hi crimdon,

    This just looks like SQL Monitor deadlocking with itself on collecting / purging data from one of the Top Queries tables - SQL Monitor is able to handle situations like this and it is a known occurrence with internal reference SRP-9636. There should be steps to prevent this from happening in work scheduled for version 5.1.

    Are you getting a large number of these deadlocks or just random occurrences?

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • crimdoncrimdon Posts: 53 Bronze 2
    They don't happen very often and non since the latest patch I applied yesterday
  • Hi Crimdon,

    If they are sporadic then it's nothing to worry about. We'll see what 5.1 brings, hopefully it will be prevented from occurring at all!

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • traiantraian Posts: 7 Bronze 1
    Hello,
    I have the same issue, only for me the deadlocks are more frequent and the purge process was left behind leaving me with a fast growing database. Is there a cleanup script that I can use on the DB while I have the Base Monitor and Web Server turned off. I have the the MSSQL RedGate Monitor 5.0.8, backend runs on a SQL Ent 2012 SP3. Thanks!
  • Alex BAlex B Posts: 757 Diamond 1
    Hi Traian,

    If you could send in an email to [email protected] referencing this forum post we can get a script to force the purge sent to you.

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
Sign In or Register to comment.