/****** Object: StoredProcedure [dbo].[usp_EmailDeadlockInfo] Script Date: 10/26/2010 16:22:59 ******/
SET ANSI_NULLS ON
GO
USE Control
GO
SET QUOTED_IDENTIFIER OFF
GO
drop PROCEDURE [dbo].[usp_EmailDeadlockInfo]
go
CREATE PROCEDURE [dbo].[usp_EmailDeadlockInfo]
@MinutesGoBack SMALLINT = 61,
@recipients VARCHAR(500) = 'diablosupport.facl.fl@corelogic.com'
AS
SET NOCOUNT ON
IF OBJECT_ID('tempdb.dbo.#ErrorLog') IS NOT NULL DROP TABLE #ErrorLog
CREATE TABLE #ErrorLog
(
ID INT IDENTITY,
LogDate DATETIME NOT NULL,
ProcessInfo VARCHAR(75),
LogInfo VARCHAR(MAX)
)
IF @MinutesGoBack <= 0
BEGIN
SET @MinutesGoBack = 32000
END
INSERT #ErrorLog(LogDate, ProcessInfo, LogInfo)
EXEC xp_readerrorlog
SET QUOTED_IDENTIFIER OFF
DECLARE @LogInfo VARCHAR(MAX) = '', @Query VARCHAR(MAX) = '', @ROWCOUNT INT, @body NVARCHAR(MAX)
;WITH DeadLockList AS
(SELECT
K.LogDate, K.processInfo, K.ID
,(SELECT MIN(E.ID) - 1 AS OuterBound
FROM #ErrorLog E
WHERE E.ID >= K.ID
AND (E.processinfo <> K.processinfo OR E.Logdate > DATEADD(SECOND, 1, K.Logdate))) AS OuterBound
FROM
(SELECT LogDate, processinfo, ID
FROM #ErrorLog
WHERE LogInfo = 'deadlock-list'
AND LogDate > DATEADD(MINUTE, -1 * @MinutesGoBack, GETDATE())
) K)
SELECT
@Query = @Query + "SET @LogInfo = @LogInfo + '*** DEADLOCK REPORT " + ProcessInfo + " " + CONVERT(VARCHAR(30), LogDate, 109) + " ***
'
SELECT @LogInfo = @LogInfo + LogInfo + '
'
FROM #ErrorLog
WHERE ID BETWEEN " + LTRIM(STR(ISNULL(ID, 0))) + " AND " + LTRIM(STR(ISNULL(OuterBound, 999999999))) + "
SET @LogInfo = @LogInfo + '
'
"
FROM DeadLockList
SET @ROWCOUNT = @@ROWCOUNT
IF @ROWCOUNT > 0
BEGIN
DECLARE @NQuery NVARCHAR(MAX) = @Query
DECLARE @subject NVARCHAR(500) = 'DEADLOCK REPORT, ' + LTRIM(STR(@ROWCOUNT)) + ' DEADLOCK(S) OCCURED WITHIN ' + LTRIM(STR(@MinutesGoBack)) + ' MINUTES'
EXEC SP_EXECUTESQL @NQuery, N'@LogInfo VARCHAR(MAX) OUTPUT', @LogInfo OUTPUT
SET @body = @LogInfo
--PRINT @LogInfo
EXEC msdb.dbo.sp_send_dbmail
--@recipients='diablosupport.facl.fl@corelogic.com',
@recipients = @recipients,
@subject = @subject,
@body = @body
END
GO
use msdb
go
/****** Object: Job [DBA Email Dead Locks] Script Date: 10/26/2010 16:22:18 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 10/26/2010 16:22:18 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBA Email Dead Locks',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'FIRSTAMDATA\FACL-SA-SQL05SRVC', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [usp_EmailDeadlockInfo] Script Date: 10/26/2010 16:22:18 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'usp_EmailDeadlockInfo',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC Control.dbo.usp_EmailDeadlockInfo @MinutesGoBack = 61
',
@database_name=N'Control',
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'EXEC usp_EmailDeadlockInfo',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=1,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20090811,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'0c1a9931-69ee-4108-8f64-e39299179cae'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO