December 12, 2016 at 10:46 pm
Comments posted to this topic are about the item Generate Deadlock Summary Information
December 13, 2016 at 1:59 am
Are there a couple of missing N in front of 'objectlock lockPartition=0' etc?
December 13, 2016 at 6:38 am
Good to see an article about deadlocks.
December 13, 2016 at 8:01 am
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
December 13, 2016 at 8:25 am
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.
December 13, 2016 at 8:44 am
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
December 13, 2016 at 9:02 am
December 13, 2016 at 9:06 am
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
December 13, 2016 at 9:11 am
December 13, 2016 at 10:04 am
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
December 14, 2016 at 4:55 am
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 ?
December 14, 2016 at 5:20 am
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.
December 14, 2016 at 6:01 am
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.
December 14, 2016 at 6:10 am
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"
December 14, 2016 at 6:22 am
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.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply