by
0
6
3,445
0
Top 1% !
Popular
Famous
Tagged
Specified
OpenSource
Popularity: 3569th place
Architecturex86_64
DBMSMS SQL Server
Languagesql
LicenseMIT_X11
OSWindows

Detect and received deadlock info on SQL Server

The first requirement is to turn on the appropriate Trace Flags: DBCC TRACEON (3605,1204,1222,-1)
The second requirement is to ensure you have DB Mail setup and working (optional).

Many thanks to Geoff Albin: http://www.sqlservercentral.com/articles/deadlock/71374/
Copy Embed Code
<iframe id="embedFrame" style="width:600px; height:300px;"
src="https://www.snip2code.com/Embed/17591/Detect-and-received-deadlock-info-on-SQL?startLine=0"></iframe>
Click on the embed code to copy it into your clipboard Width Height
Leave empty to retrieve all the content Start End
--== FIRST STEP ==-- --== We will create a temporary table to hold the error log detail. ==-- --== Before we create the temporary table, we make sure it does not already exist. ==-- IF OBJECT_ID('tempdb.dbo.ErrorLog') IS Not Null BEGIN DROP TABLE tempdb.dbo.ErrorLog END --== We have checked for the existence of the temporary table and dropped it if it was there. ==-- --== Now, we can create the table called tempdb.dbo.ErrorLog ==-- CREATE TABLE tempdb.dbo.ErrorLog (Id int IDENTITY (1, 1) NOT NULL, logdate DATETIME, procInfo VARCHAR(10), ERRORLOG VARCHAR(MAX)) --== We create a 3 column table to hold the contents of the SQL Server Error log. ==-- --== Then we insert the actual data from the Error log into our newly created table. ==-- INSERT INTO tempdb.dbo.ErrorLog EXEC master.dbo.sp_readerrorlog --== With our table created and populated, we can now use the info inside of it. ==-- BEGIN --== Set a variable to get our instance name. ==-- --== We do this so the email we receive makes more sense. ==-- declare @servername nvarchar(150) set @servername = @@servername --== We set another variable to create a subject line for the email. ==-- declare @mysubject nvarchar(200) set @mysubject = 'Deadlock event notification on server '+@servername+'.' --== Now we will prepare and send the email. Change the email address to suite your environment. ==-- EXEC msdb.dbo.sp_send_dbmail @recipients='DBA_Super_Hero@email.com', @subject = @mysubject, @body = 'Deadlock has occurred. View attachment to see the deadlock info', @query = 'select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''%Deadlock encountered%'' order by Id DESC)', @query_result_width = 600, @attach_query_result_as_file = 1 END --== Clean up our process by dropping our temporary table. ==-- DROP TABLE tempdb.dbo.ErrorLog --== SECOND STEP: ==-- --== SQL Server Agent Job ==-- USE [msdb] GO BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 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'Deadlock Job', @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'sa', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Deadlock has occurred.', @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'--== This is for SQL 2005 and higher. ==-- --== We will create a temporary table to hold the error log detail. ==-- --== Before we create the temporary table, we make sure it does not already exist. ==-- IF OBJECT_ID(''tempdb.dbo.ErrorLog'') IS Not Null BEGIN DROP TABLE tempdb.dbo.ErrorLog END --== We have checked for the existence of the temporary table and dropped it if it was there. ==-- --== Now, we can create the table called tempdb.dbo.ErrorLog ==-- CREATE TABLE tempdb.dbo.ErrorLog (Id int IDENTITY (1, 1) NOT NULL, logdate DATETIME, procInfo VARCHAR(10), ERRORLOG VARCHAR(MAX)) --== We create a 3 column table to hold the contents of the SQL Server Error log. ==-- --== Then we insert the actual data from the Error log into our newly created table. ==-- INSERT INTO tempdb.dbo.ErrorLog EXEC master.dbo.sp_readerrorlog --== With our table created and populated, we can now use the info inside of it. ==-- BEGIN --== Set a variable to get our instance name. ==-- --== We do this so the email we receive makes more sense. ==-- declare @servername nvarchar(150) set @servername = @@servername --== We set another variable to create a subject line for the email. ==-- declare @mysubject nvarchar(200) set @mysubject = ''Deadlock event notification on server ''+@servername+''.'' --== Now we will prepare and send the email. Change the email address to suite your environment. ==-- EXEC msdb.dbo.sp_send_dbmail @recipients=''DBA_Super_Hero@email.com'', @subject = @mysubject, @body = ''Deadlock has occurred. View attachment to see the deadlock info'', @query = ''select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''''%Deadlock encountered%'''' order by Id DESC)'', @query_result_width = 600, @attach_query_result_as_file = 1 END --== Clean up our process by dropping our temporary table. ==-- DROP TABLE tempdb.dbo.ErrorLog ', @database_name=N'master', @flags=0 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_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
If you want to be updated about similar snippets, Sign in and follow our Channels

blog comments powered by Disqus