Following script creates stored procedure which can be called via sql job to run every 6 hours to get detals of dedalock on server through e-mail .
USE [master]
GO
/****** Object: StoredProcedure [dbo].[listDeadLocks] Script Date: 04/23/2015 02:41:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[listDeadLocks]
AS
BEGIN
DECLARE @RawLogs TABLE (id INT IDENTITY (1, 1), logdate DATETIME, processinfo NVARCHAR(50), logtext NVARCHAR(MAX))
DECLARE @tab_Deadlocklist TABLE (deadlockNo INT , processinfo NVARCHAR(20), logtext VARCHAR(MAX))
--CREATE INDEX IDX_TEMP ON @tab (logtext);
DECLARE @DEADLOCKQUERIES TABLE (logtext NVARCHAR(MAX))
DECLARE @SNAME AS NVARCHAR(64), @ERRORBODY VARCHAR(MAX), @BODY VARCHAR(MAX) ,@ERRORSUBJECT NVARCHAR(256) ,@TO AS NVARCHAR(100),@ERRORBODY_shell VARCHAR(MAX)
DECLARE @found INT , @insert_flag INT , @v_deadlock_ex_count INT, @DNO INT, @rawlogs_cnt INT
DECLARE @c1_logdate datetime, @c1_processinfo NVARCHAR(50), @c1_logtext varchar(max)
DECLARE @v_currentProcessId NVARCHAR(256),@v_processed NVARCHAR(256),@Db_name NVARCHAR(100)
DECLARE @timeInHours INT, @default_threshold_value INT
SET @found = 0
SET @timeInHours = 16 --- this takes last 6 hours deadlocks from ErrorLog
SET @DNO = 1
SET @insert_flag=0
SET @v_currentProcessId=''
SET @rawlogs_cnt = 0
SET @default_threshold_value=1
SELECT GETDATE() AS CurrentStartTime
INSERT INTO @RawLogs
EXEC sp_readerrorlog 1
SET @rawlogs_cnt = @@ROWCOUNT
SELECT GETDATE() AS CurrentTimeAfterRawlogs1
INSERT INTO @RawLogs
EXEC sp_readerrorlog
SET @rawlogs_cnt = @rawlogs_cnt + @@ROWCOUNT
SELECT GETDATE() AS CurrentTimeAfterRawlogs2
SELECT @rawlogs_cnt
IF object_id('tempdb..#Database_Exceptions') is not null
BEGIN
DROP TABLE #Database_Exceptions
END
CREATE TABLE #Database_Exceptions
(
DBname VARCHAR(50),
deadlock_count INT
);
--trying append date to the error file
--DECLARE @vstrDate AS CHAR(10)
--select @vstrDate = CONVERT(char(10), GETDATE(), 121)
--select @vstrDate
--- DeadLock threshold counts are defined in the following file.
BEGIN TRY
BULK
INSERT #Database_Exceptions
FROM '\\sharedpath\Admin\Scripts\SQL\Deadlock_DB_Execption_Count.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
ERRORFILE = '\\sharedpath\Admin\Scripts\SQL\Deadlock_DB_Execption_Count_Error.txt'
)
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
RETURN -1
END CATCH
select * from #Database_Exceptions
SELECT GETDATE() AS CurrentTimeAfterFileRead
DECLARE c1 CURSOR FOR
SELECT logdate,processinfo,logtext FROM @RawLogs
WHERE
logdate >= dateadd(hh, -1*@timeInHours, getdate())
and processinfo not in ('Backup','Logon')
ORDER BY processinfo,id asc;
OPEN c1
FETCH NEXT FROM c1 INTO @c1_logdate, @c1_processinfo, @c1_logtext
WHILE @@FETCH_STATUS = 0
BEGIN
IF @c1_logtext ='deadlock-list' OR @insert_flag=1
BEGIN
IF @v_currentProcessId=@c1_processinfo OR @v_currentProcessId=''
BEGIN
SET @v_currentProcessId=@c1_processinfo
INSERT INTO @tab_DeadLockList VALUES (@DNO, @v_currentProcessId, @c1_logtext)
SET @insert_flag=1
END
ELSE
BEGIN
SET @insert_flag=0
--SELECT @v_currentProcessId
SET @v_currentProcessId=''
SET @DNO = @DNO + 1
END
END
FETCH NEXT FROM c1 INTO @c1_logdate, @c1_processinfo, @c1_logtext
END
CLOSE c1
DEALLOCATE c1
SELECT GETDATE() AS TimeStampAfterC1
-- total deadlocks
--select @DNO-1
DECLARE @c2_deadId INT
DECLARE @c2_deadlocktext varchar(max)
DECLARE @c2_currentDeadId INT
DECLARE @c2_concattext VARCHAR(MAX)
set @c2_concattext= ''
DECLARE @v_cnt int
set @v_cnt=0
set @c2_currentDeadId=0
DECLARE c2 CURSOR FOR
SELECT deadlockNo ,logtext
from @tab_Deadlocklist
where --logtext like '%frame procname=marqueestats.dbo.Dispatcher_UpdateServer li%'
logtext not like '%deadlock-list%'
and logtext not like '%deadlock victim%'
and logtext not like '%process-list%'
and logtext not like '%executionStack%'
and logtext not like '%process id%'
and logtext not like '%inputbuf%'
and logtext not like '%owner id%'
and logtext not like '%waiter-list%'
and logtext not like '%waiter id%'
and logtext not like '%resource-list%'
and logtext not like '%owner-list%'
and logtext not like '%pagelock fileid%'
and logtext not like '%exchangeEvent%'
and logtext not like '%==%'
-- added on 17/09/12
and logtext not like '--%'
and logtext not like 'keylock %'
ORDER BY deadlockNo ASC
OPEN c2
FETCH NEXT FROM c2 INTO @c2_deadId, @c2_deadlocktext
WHILE @@FETCH_STATUS = 0
BEGIN
IF @c2_currentDeadId=0 or @c2_currentDeadId=@c2_deadId
BEGIN
SET @c2_currentDeadId=@c2_deadId
IF @c2_deadlocktext like '%frame procname%'
BEGIN
SET @c2_deadlocktext=LTRIM(REPLACE(@c2_deadlocktext,'frame procname=',CHAR(13)+'Procedure: '))
SET @c2_deadlocktext=SUBSTRING(@c2_deadlocktext,1,CHARINDEX('line=',@c2_deadlocktext)-1) +'. '+ CHAR(13)+ 'Query: '
END
SET @c2_concattext = @c2_concattext + @c2_deadlocktext
END
ELSE
BEGIN
--- insert the contactenated queries into a temp table
INSERT INTO @DEADLOCKQUERIES SELECT @c2_concattext
SET @c2_concattext=''
SET @c2_currentDeadId=@c2_deadId
IF @c2_deadlocktext like '%frame procname%'
BEGIN
SET @c2_deadlocktext=LTRIM(REPLACE(@c2_deadlocktext,'frame procname=',CHAR(13)+'Procedure: '))
SET @c2_deadlocktext=SUBSTRING(@c2_deadlocktext,1,CHARINDEX('line=',@c2_deadlocktext)-1) +'. '+ CHAR(13)+ 'Query: '
--SET @db_name=SUBSTRING(@c2_deadlocktext,1,CHARINDEX('.',@c2_deadlocktext,1)-1)
--SELECT @Db_name
END
SET @c2_concattext = @c2_concattext + @c2_deadlocktext
END
FETCH NEXT FROM c2
INTO @c2_deadId, @c2_deadlocktext
END
INSERT INTO @DEADLOCKQUERIES SELECT @c2_concattext
CLOSE c2
DEALLOCATE c2
SELECT GETDATE() AS TimeStampAfterC2
--SELECT TOP 5 COUNT(*),substring(logtext,1,500) FROM @DEADLOCKQUERIES
--GROUP BY substring(logtext,1,500)
----HAVING COUNT(*)>1
SET @SNAME=(SELECT @@SERVERNAME)
SET @TO='e-mail@domain.com;e-mail-group@domain.com;'
--SELECT TOP 5 COUNT(*),substring(logtext,1,500) FROM @DEADLOCKQUERIES
--WHERE lower(logtext) not like '%procedure%adhoc%'
--GROUP BY substring(logtext,1,500)
--HAVING COUNT(*)>1
DECLARE C3 CURSOR FOR
SELECT TOP 5 COUNT(*),substring(logtext,1,500) FROM @DEADLOCKQUERIES
--WHERE lower(logtext) not like '%procedure%adhoc%'
GROUP BY substring(logtext,1,500)
HAVING COUNT(*)>=@default_threshold_value --- min dead lock count changed to 50
OPEN c3
FETCH NEXT FROM c3 INTO @v_cnt, @BODY
WHILE @@FETCH_STATUS = 0
BEGIN
SET @found =1
SET @ERRORBODY = 'Following deadlock occured '+ cast(@v_cnt as nvarchar(10)) + ' time(s). ' + CHAR(13) +@BODY
--SET @ERRORBODY_shell= 'Following deadlock occured '+ cast(@v_cnt as nvarchar(10)) + ' time(s).`r`n ' +@BODY
SET @Db_name=SUBSTRING(@BODY,12,CHARINDEX('.',@BODY,1)-12)
SET @ERRORSUBJECT = @SNAME + ': DeadLocks Found in ' + UPPER(LTRIM(@Db_name)) + ' database.'
IF UPPER(LTRIM(RTRIM(@Db_name))) not like '%ADHOC%' AND upper(@Db_name) not like '%MSDB%'
BEGIN
--SELECT * FROM #Database_Exceptions
--WHERE UPPER(DBname)= UPPER(@Db_name)
--SELECT TOP 1 deadlock_count FROM #Database_Exceptions ---- to remove any duplicate entried in the file.
--WHERE UPPER(DBname)= UPPER(LTRIM(RTRIM(@Db_name)))
set @v_deadlock_ex_count = (SELECT TOP 1 deadlock_count FROM #Database_Exceptions
WHERE UPPER(DBname)= UPPER(LTRIM(RTRIM(@Db_name))) )
set @v_deadlock_ex_count = ISNULL(@v_deadlock_ex_count,@default_threshold_value)
SELECT UPPER(LTRIM(RTRIM(@Db_name))),@v_cnt, @v_deadlock_ex_count
IF @v_cnt > @v_deadlock_ex_count
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Standard Profile',
@recipients = @TO,
@body = @ERRORBODY,
@subject = @ERRORSUBJECT ;
END
END
FETCH NEXT FROM C3
INTO @v_cnt, @BODY
END
CLOSE c3
DEALLOCATE c3
SELECT GETDATE() AS TimeStampAfterC3
END
GO
on shared path deadlock_db_exception_count , edit the text file and type
database_name,<deadlock count>
EX: db1,3 -- 3 deadlcoks on db1 database
example e-mail :
USE [master]
GO
/****** Object: StoredProcedure [dbo].[listDeadLocks] Script Date: 04/23/2015 02:41:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[listDeadLocks]
AS
BEGIN
DECLARE @RawLogs TABLE (id INT IDENTITY (1, 1), logdate DATETIME, processinfo NVARCHAR(50), logtext NVARCHAR(MAX))
DECLARE @tab_Deadlocklist TABLE (deadlockNo INT , processinfo NVARCHAR(20), logtext VARCHAR(MAX))
--CREATE INDEX IDX_TEMP ON @tab (logtext);
DECLARE @DEADLOCKQUERIES TABLE (logtext NVARCHAR(MAX))
DECLARE @SNAME AS NVARCHAR(64), @ERRORBODY VARCHAR(MAX), @BODY VARCHAR(MAX) ,@ERRORSUBJECT NVARCHAR(256) ,@TO AS NVARCHAR(100),@ERRORBODY_shell VARCHAR(MAX)
DECLARE @found INT , @insert_flag INT , @v_deadlock_ex_count INT, @DNO INT, @rawlogs_cnt INT
DECLARE @c1_logdate datetime, @c1_processinfo NVARCHAR(50), @c1_logtext varchar(max)
DECLARE @v_currentProcessId NVARCHAR(256),@v_processed NVARCHAR(256),@Db_name NVARCHAR(100)
DECLARE @timeInHours INT, @default_threshold_value INT
SET @found = 0
SET @timeInHours = 16 --- this takes last 6 hours deadlocks from ErrorLog
SET @DNO = 1
SET @insert_flag=0
SET @v_currentProcessId=''
SET @rawlogs_cnt = 0
SET @default_threshold_value=1
SELECT GETDATE() AS CurrentStartTime
INSERT INTO @RawLogs
EXEC sp_readerrorlog 1
SET @rawlogs_cnt = @@ROWCOUNT
SELECT GETDATE() AS CurrentTimeAfterRawlogs1
INSERT INTO @RawLogs
EXEC sp_readerrorlog
SET @rawlogs_cnt = @rawlogs_cnt + @@ROWCOUNT
SELECT GETDATE() AS CurrentTimeAfterRawlogs2
SELECT @rawlogs_cnt
IF object_id('tempdb..#Database_Exceptions') is not null
BEGIN
DROP TABLE #Database_Exceptions
END
CREATE TABLE #Database_Exceptions
(
DBname VARCHAR(50),
deadlock_count INT
);
--trying append date to the error file
--DECLARE @vstrDate AS CHAR(10)
--select @vstrDate = CONVERT(char(10), GETDATE(), 121)
--select @vstrDate
--- DeadLock threshold counts are defined in the following file.
BEGIN TRY
BULK
INSERT #Database_Exceptions
FROM '\\sharedpath\Admin\Scripts\SQL\Deadlock_DB_Execption_Count.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
ERRORFILE = '\\sharedpath\Admin\Scripts\SQL\Deadlock_DB_Execption_Count_Error.txt'
)
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
RETURN -1
END CATCH
select * from #Database_Exceptions
SELECT GETDATE() AS CurrentTimeAfterFileRead
DECLARE c1 CURSOR FOR
SELECT logdate,processinfo,logtext FROM @RawLogs
WHERE
logdate >= dateadd(hh, -1*@timeInHours, getdate())
and processinfo not in ('Backup','Logon')
ORDER BY processinfo,id asc;
OPEN c1
FETCH NEXT FROM c1 INTO @c1_logdate, @c1_processinfo, @c1_logtext
WHILE @@FETCH_STATUS = 0
BEGIN
IF @c1_logtext ='deadlock-list' OR @insert_flag=1
BEGIN
IF @v_currentProcessId=@c1_processinfo OR @v_currentProcessId=''
BEGIN
SET @v_currentProcessId=@c1_processinfo
INSERT INTO @tab_DeadLockList VALUES (@DNO, @v_currentProcessId, @c1_logtext)
SET @insert_flag=1
END
ELSE
BEGIN
SET @insert_flag=0
--SELECT @v_currentProcessId
SET @v_currentProcessId=''
SET @DNO = @DNO + 1
END
END
FETCH NEXT FROM c1 INTO @c1_logdate, @c1_processinfo, @c1_logtext
END
CLOSE c1
DEALLOCATE c1
SELECT GETDATE() AS TimeStampAfterC1
-- total deadlocks
--select @DNO-1
DECLARE @c2_deadId INT
DECLARE @c2_deadlocktext varchar(max)
DECLARE @c2_currentDeadId INT
DECLARE @c2_concattext VARCHAR(MAX)
set @c2_concattext= ''
DECLARE @v_cnt int
set @v_cnt=0
set @c2_currentDeadId=0
DECLARE c2 CURSOR FOR
SELECT deadlockNo ,logtext
from @tab_Deadlocklist
where --logtext like '%frame procname=marqueestats.dbo.Dispatcher_UpdateServer li%'
logtext not like '%deadlock-list%'
and logtext not like '%deadlock victim%'
and logtext not like '%process-list%'
and logtext not like '%executionStack%'
and logtext not like '%process id%'
and logtext not like '%inputbuf%'
and logtext not like '%owner id%'
and logtext not like '%waiter-list%'
and logtext not like '%waiter id%'
and logtext not like '%resource-list%'
and logtext not like '%owner-list%'
and logtext not like '%pagelock fileid%'
and logtext not like '%exchangeEvent%'
and logtext not like '%==%'
-- added on 17/09/12
and logtext not like '--%'
and logtext not like 'keylock %'
ORDER BY deadlockNo ASC
OPEN c2
FETCH NEXT FROM c2 INTO @c2_deadId, @c2_deadlocktext
WHILE @@FETCH_STATUS = 0
BEGIN
IF @c2_currentDeadId=0 or @c2_currentDeadId=@c2_deadId
BEGIN
SET @c2_currentDeadId=@c2_deadId
IF @c2_deadlocktext like '%frame procname%'
BEGIN
SET @c2_deadlocktext=LTRIM(REPLACE(@c2_deadlocktext,'frame procname=',CHAR(13)+'Procedure: '))
SET @c2_deadlocktext=SUBSTRING(@c2_deadlocktext,1,CHARINDEX('line=',@c2_deadlocktext)-1) +'. '+ CHAR(13)+ 'Query: '
END
SET @c2_concattext = @c2_concattext + @c2_deadlocktext
END
ELSE
BEGIN
--- insert the contactenated queries into a temp table
INSERT INTO @DEADLOCKQUERIES SELECT @c2_concattext
SET @c2_concattext=''
SET @c2_currentDeadId=@c2_deadId
IF @c2_deadlocktext like '%frame procname%'
BEGIN
SET @c2_deadlocktext=LTRIM(REPLACE(@c2_deadlocktext,'frame procname=',CHAR(13)+'Procedure: '))
SET @c2_deadlocktext=SUBSTRING(@c2_deadlocktext,1,CHARINDEX('line=',@c2_deadlocktext)-1) +'. '+ CHAR(13)+ 'Query: '
--SET @db_name=SUBSTRING(@c2_deadlocktext,1,CHARINDEX('.',@c2_deadlocktext,1)-1)
--SELECT @Db_name
END
SET @c2_concattext = @c2_concattext + @c2_deadlocktext
END
FETCH NEXT FROM c2
INTO @c2_deadId, @c2_deadlocktext
END
INSERT INTO @DEADLOCKQUERIES SELECT @c2_concattext
CLOSE c2
DEALLOCATE c2
SELECT GETDATE() AS TimeStampAfterC2
--SELECT TOP 5 COUNT(*),substring(logtext,1,500) FROM @DEADLOCKQUERIES
--GROUP BY substring(logtext,1,500)
----HAVING COUNT(*)>1
SET @SNAME=(SELECT @@SERVERNAME)
SET @TO='e-mail@domain.com;e-mail-group@domain.com;'
--SELECT TOP 5 COUNT(*),substring(logtext,1,500) FROM @DEADLOCKQUERIES
--WHERE lower(logtext) not like '%procedure%adhoc%'
--GROUP BY substring(logtext,1,500)
--HAVING COUNT(*)>1
DECLARE C3 CURSOR FOR
SELECT TOP 5 COUNT(*),substring(logtext,1,500) FROM @DEADLOCKQUERIES
--WHERE lower(logtext) not like '%procedure%adhoc%'
GROUP BY substring(logtext,1,500)
HAVING COUNT(*)>=@default_threshold_value --- min dead lock count changed to 50
OPEN c3
FETCH NEXT FROM c3 INTO @v_cnt, @BODY
WHILE @@FETCH_STATUS = 0
BEGIN
SET @found =1
SET @ERRORBODY = 'Following deadlock occured '+ cast(@v_cnt as nvarchar(10)) + ' time(s). ' + CHAR(13) +@BODY
--SET @ERRORBODY_shell= 'Following deadlock occured '+ cast(@v_cnt as nvarchar(10)) + ' time(s).`r`n ' +@BODY
SET @Db_name=SUBSTRING(@BODY,12,CHARINDEX('.',@BODY,1)-12)
SET @ERRORSUBJECT = @SNAME + ': DeadLocks Found in ' + UPPER(LTRIM(@Db_name)) + ' database.'
IF UPPER(LTRIM(RTRIM(@Db_name))) not like '%ADHOC%' AND upper(@Db_name) not like '%MSDB%'
BEGIN
--SELECT * FROM #Database_Exceptions
--WHERE UPPER(DBname)= UPPER(@Db_name)
--SELECT TOP 1 deadlock_count FROM #Database_Exceptions ---- to remove any duplicate entried in the file.
--WHERE UPPER(DBname)= UPPER(LTRIM(RTRIM(@Db_name)))
set @v_deadlock_ex_count = (SELECT TOP 1 deadlock_count FROM #Database_Exceptions
WHERE UPPER(DBname)= UPPER(LTRIM(RTRIM(@Db_name))) )
set @v_deadlock_ex_count = ISNULL(@v_deadlock_ex_count,@default_threshold_value)
SELECT UPPER(LTRIM(RTRIM(@Db_name))),@v_cnt, @v_deadlock_ex_count
IF @v_cnt > @v_deadlock_ex_count
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Standard Profile',
@recipients = @TO,
@body = @ERRORBODY,
@subject = @ERRORSUBJECT ;
END
END
FETCH NEXT FROM C3
INTO @v_cnt, @BODY
END
CLOSE c3
DEALLOCATE c3
SELECT GETDATE() AS TimeStampAfterC3
END
GO
on shared path deadlock_db_exception_count , edit the text file and type
database_name,<deadlock count>
EX: db1,3 -- 3 deadlcoks on db1 database
example e-mail :
No comments:
Post a Comment