Generate Deadlock Summary Information

  • Carolyn Richardson

    SSCrazy Eights

    Points: 8194

    Comments posted to this topic are about the item Generate Deadlock Summary Information

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • julian.fletcher

    SSCrazy

    Points: 2605

    Are there a couple of missing N in front of 'objectlock lockPartition=0' etc?

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Good to see an article about deadlocks.

  • TheSQLGuru

    SSC Guru

    Points: 134017

    Your description of TF 1204 is not correct. Please revise the article. Also, I am not sure why you would want two trace flags on that both record deadlock information.

    I would also make a note of the fact that scanning a very large set of log files, doing inserts and then deduping those records could cause a substantial overhead on the server each time the job was run.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Carolyn Richardson

    SSCrazy Eights

    Points: 8194

    We are fortunate enough to have a well over-specked server in a non 24/7 operation so I don't have to worry about running jobs like this out of hours but it would be a different matter in other types of environments.

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • jimmystrain

    SSC Enthusiast

    Points: 144

    Missing END in script.

    --remove any duplicates created

    WITH CTE

    AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY [LogDate],

    [ProcessInfo], [Text] ORDER BY [LogDate] ) AS RowNumber ,

    [LogDate] ,

    [ProcessInfo] ,

    [Text]

    FROM [DBA].[dbo].[DeadockObject]

    )

    DELETE FROM CTE

    WHERE RowNumber > 1;

    END

    GO

  • Carolyn Richardson

    SSCrazy Eights

    Points: 8194

    ?There is no BEGIN so therefor no END needed or am I missing something?

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • jimmystrain

    SSC Enthusiast

    Points: 144

    Sure there is:

    CREATE PROCEDURE [dbo].[usp_RefreshDeadockObject]

    AS

    BEGIN

    TRUNCATE TABLE [dbo].[DeadockObject];

    INSERT INTO [dbo].[DeadockObject]

    ( [LogDate] ,

    [ProcessInfo] ,

    [Text]

    )

    EXEC master.dbo.xp_readerrorlog 0, 1, 'objectlock lockPartition=0',

    NULL, NULL, NULL, N'desc';

    INSERT INTO [dbo].[DeadockObject]

    ( [LogDate] ,

    [ProcessInfo] ,

    [Text]

    )

    EXEC master.dbo.xp_readerrorlog 1, 1, 'objectlock lockPartition=0',

    NULL, NULL, NULL, N'desc';

    INSERT INTO [dbo].[DeadockObject]

    ( [LogDate] ,

    [ProcessInfo] ,

    [Text]

    )

    EXEC master.dbo.xp_readerrorlog 2, 1, 'objectlock lockPartition=0',

    NULL, NULL, NULL, N'desc';

    INSERT INTO [dbo].[DeadockObject]

    ( [LogDate] ,

    [ProcessInfo] ,

    [Text]

    )

    EXEC master.dbo.xp_readerrorlog 3, 1, 'objectlock lockPartition=0',

    NULL, NULL, NULL, N'desc';

    INSERT INTO [dbo].[DeadockObject]

    ( [LogDate] ,

    [ProcessInfo] ,

    [Text]

    )

    EXEC master.dbo.xp_readerrorlog 4, 1, 'objectlock lockPartition=0',

    NULL, NULL, NULL, N'desc';

    INSERT INTO [dbo].[DeadockObject]

    ( [LogDate] ,

    [ProcessInfo] ,

    [Text]

    )

    EXEC master.dbo.xp_readerrorlog 5, 1, 'objectlock lockPartition=0',

    NULL, NULL, NULL, N'desc';

    --remove any duplicates created

    WITH CTE

    AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY [LogDate],

    [ProcessInfo], [Text] ORDER BY [LogDate] ) AS RowNumber ,

    [LogDate] ,

    [ProcessInfo] ,

    [Text]

    FROM [DBA].[dbo].[DeadockObject]

    )

    DELETE FROM CTE

    WHERE RowNumber > 1;

    GO

  • Carolyn Richardson

    SSCrazy Eights

    Points: 8194

    OK Thanks will sort

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • TheSQLGuru

    SSC Guru

    Points: 134017

    Carolyn Richardson (12/13/2016)


    We are fortunate enough to have a well over-specked server in a non 24/7 operation so I don't have to worry about running jobs like this out of hours but it would be a different matter in other types of environments.

    Very fortunate for you! However, please keep in mind that you wrote an article for a million+ readers that have an extraordinary permutation of systems and needs. 🙂

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • mickegohle

    SSC Enthusiast

    Points: 174

    Hiall,

    Very nice with a Deadlock script. However, when I execute the scheduled job I didn't get any results even though I have a deadlock in the errorlog. I etried to execute the procedured via a query-window with the command: 'exec usp_RefreshDeadlockVictim'. I got the following message:

    (0 row(s) affected)

    (0 row(s) affected)

    (0 row(s) affected)

    (0 row(s) affected)

    (0 row(s) affected)

    (0 row(s) affected)

    (0 row(s) affected)

    Msg 0, Level 11, State 0, Line 1

    A severe error occurred on the current command. The results, if any, should be discarded.

    Anyone has any idea ?

  • mickegohle

    SSC Enthusiast

    Points: 174

    mickegohle (12/14/2016)


    Hiall,

    Very nice with a Deadlock script. However, when I execute the scheduled job I didn't get any results even though I have a deadlock in the errorlog. I etried to execute the procedured via a query-window with the command: 'exec usp_RefreshDeadlockVictim'. I got the following message:

    (0 row(s) affected)

    (0 row(s) affected)

    (0 row(s) affected)

    (0 row(s) affected)

    (0 row(s) affected)

    (0 row(s) affected)

    (0 row(s) affected)

    Msg 0, Level 11, State 0, Line 1

    A severe error occurred on the current command. The results, if any, should be discarded.

    Anyone has any idea ?

    Ok, That was cause i didn't commit my transaction that was not deadlocked but rolled it back. But when committing i got the following (now some rows inserted but in the end it gets rolled back due to error message in the end):

    (0 row(s) affected)

    (1 row(s) affected)

    (0 row(s) affected)

    (1 row(s) affected)

    (0 row(s) affected)

    (1 row(s) affected)

    (0 row(s) affected)

    (1 row(s) affected)

    (0 row(s) affected)

    (1 row(s) affected)

    (0 row(s) affected)

    (1 row(s) affected)

    Msg 0, Level 11, State 0, Line 0

    A severe error occurred on the current command. The results, if any, should be discarded.

  • Carolyn Richardson

    SSCrazy Eights

    Points: 8194

    Do you get anything returned when you just query the log ie:-

    EXEC master.dbo.xp_readerrorlog 0, 1, 'deadlock victim=process', NULL, NULL, NULL, N'desc'

    obviously I'm not sure which log it would be in but probably the current log if you've only just added the trace flags.

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • mickegohle

    SSC Enthusiast

    Points: 174

    Carolyn Richardson (12/14/2016)


    Do you get anything returned when you just query the log ie:-

    EXEC master.dbo.xp_readerrorlog 0, 1, 'deadlock victim=process', NULL, NULL, NULL, N'desc'

    obviously I'm not sure which log it would be in but probably the current log if you've only just added the trace flags.

    I look in the current log and i have this row: "2016-12-14 09:27:26.10 spid32s deadlock victim=process4ecec4108"

    I then execute: "EXEC master.dbo.xp_readerrorlog 0, 1, 'deadlock victim=process', NULL, NULL, NULL, N'desc' "

    I get the following: "Error executing extended stored procedure: Invalid Parameter Type"

  • Carolyn Richardson

    SSCrazy Eights

    Points: 8194

    I've tried updating the procedure to read

    CREATE PROCEDURE [dbo].[usp_RefreshDeadockVictim]

    AS

    TRUNCATE TABLE [dbo].[DeadlockVictim]

    INSERT INTO [dbo].[DeadlockVictim]

    ([LogDate]

    ,[ProcessInfo]

    ,[Text])

    EXEC master.dbo.xp_readerrorlog 0, 1, N'deadlock victim=process', NULL, NULL, NULL, N'desc'

    INSERT INTO [dbo].[DeadlockVictim]

    ([LogDate]

    ,[ProcessInfo]

    ,[Text])

    EXEC master.dbo.xp_readerrorlog 1, 1, N'deadlock victim=process', NULL, NULL, NULL, N'desc'

    INSERT INTO [dbo].[DeadlockVictim]

    ([LogDate]

    ,[ProcessInfo]

    ,[Text])

    EXEC master.dbo.xp_readerrorlog 2, 1, N'deadlock victim=process', NULL, NULL, NULL, N'desc'

    INSERT INTO [dbo].[DeadlockVictim]

    ([LogDate]

    ,[ProcessInfo]

    ,[Text])

    EXEC master.dbo.xp_readerrorlog 3, 1, N'deadlock victim=process', NULL, NULL, NULL, N'desc'

    INSERT INTO [dbo].[DeadlockVictim]

    ([LogDate]

    ,[ProcessInfo]

    ,[Text])

    EXEC master.dbo.xp_readerrorlog 4, 1, N'deadlock victim=process', NULL, NULL, NULL, N'desc'

    INSERT INTO [dbo].[DeadlockVictim]

    ([LogDate]

    ,[ProcessInfo]

    ,[Text])

    EXEC master.dbo.xp_readerrorlog 5, 1, N'deadlock victim=process', NULL, NULL, NULL, N'desc'

    Both work in my environment you'd also have to change the Deadlock victim table fields to nvarchar as well not sure whether this will fix it.

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply