Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL Scripts & Samples

Posted June 25, 2011

Dead Lock Graph

By DatabaseJournal.com Staff

>>Script Language and Platform: SQL Server
This script will create the base table, stored procedures, set up alert, Job for the dead lock. The output will send both xml and xdl file. XDL file is the deadloack graph that can be opened in SSMS.

Author: Sathiyamoorthy Kunchithapatham

	--Enable the traceflag and server configuration  dbcc traceon(1222,-1)  go  sp_configure xp_cmdshell,1 

 RECONFIGURE with override
 go
 -- Create the Base table in dba database.
 USE [dba]
GO

/****** Object:  Table [dbo].[DeadlockEvents]    Script Date: 06/20/2012 12:19:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeadlockEvents]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[DeadlockEvents](  [Id] [int] IDENTITY(1,1) NOT NULL,  [AlertTime] [datetime2](7) NOT NULL,  [DeadlockGraph] [xml] NULL
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_AlertTime]') AND type = 'D') BEGIN ALTER TABLE [dbo].[DeadlockEvents] ADD  CONSTRAINT [DF_AlertTime]  DEFAULT (sysdatetime()) FOR [AlertTime] END

GO
--Create a user defined procedure in master db USE [master] GO

/****** Object:  StoredProcedure [dbo].[usp_getdeadlockinfo]    Script Date: 06/20/2012 12:26:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_getdeadlockinfo]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE procedure [dbo].[usp_getdeadlockinfo] as begin set nocount on
SELECT TOP 1 [DeadlockGraph].query(''/TextData/deadlock-list'') FROM [DBA].[dbo].[DeadlockEvents] ORDER BY Id DESC   FOR XML RAW, TYPE, ELEMENTS XSINIL 
end' 
END
GO
--Set up the WMI alert for deadlock
USE [msdb]
GO

/****** Object:  Alert [Respond to DEADLOCK_GRAPH]    Script Date: 06/20/2012 12:20:58 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to DEADLOCK_GRAPH') EXEC msdb.dbo.sp_add_alert @name=N'Respond to DEADLOCK_GRAPH',
  @message_id=0,
  @severity=0,
  @enabled=1,
  @delay_between_responses=120,
  @include_event_description_in=1,
  @category_name=N'[Uncategorized]',
  @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
  @wmi_query=N'SELECT * FROM DEADLOCK_GRAPH',
  @job_id=N'5ae39cb7-5e46-495c-aea2-7ab2355f77de'
GO
--Set up a job to respond for the alert
--Inside the job replace the db profile, email recipients

USE [msdb]
GO

/****** Object:  Job [Capture Deadlock Graph]    Script Date: 06/20/2012 12:22:55 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 06/20/2012 12:22:55 ******/
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)
select @jobId = job_id from msdb.dbo.sysjobs where (name = N'Capture Deadlock Graph') if (@jobId is NULL) BEGIN EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Capture Deadlock Graph',
  @enabled=1,
  @notify_level_eventlog=2,
  @notify_level_email=0,
  @notify_level_netsend=0,
  @notify_level_page=0,
  @delete_level=0,
  @description=N'Job for responding to DEADLOCK_GRAPH events',
  @category_name=N'[Uncategorized (Local)]',
  @owner_login_name=N'sa', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
/****** Object:  Step [Insert graph into DeadlockEvents]    Script Date: 06/20/2012 12:22:55 ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 1) EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert graph into DeadlockEvents',
  @step_id=1,
  @cmdexec_success_code=0,
  @on_success_action=3,
  @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'INSERT INTO dba..DeadlockEvents(DeadlockGraph)
                VALUES (N''$(ESCAPE_SQUOTE(WMI(TextData)))'')',
  @database_name=N'DBA',
  @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [create xdl file]    Script Date: 06/20/2012 12:22:55 ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 2) EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'create xdl file',
  @step_id=2,
  @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'DECLARE @FileName varchar(50),
        @bcpCommand varchar(2000)

SET @FileName = ''D:\dba\temp\xmlfile.xdl''
--SET @FileName = ''D:\dba\temp\deadlock ''+rtrim(CONVERT(char(25),GETDATE()))+''.xdl''

SET @bcpCommand = ''bcp "SELECT TOP 1 [DeadlockGraph].query(''''/TextData/deadlock-list'''') FROM [DBA].[dbo].[DeadlockEvents] ORDER BY Id DESC" queryout "''
SET @bcpCommand = @bcpCommand + @FileName + ''" -T -c -q''

EXEC master..xp_cmdshell @bcpCommand

EXEC msdb.dbo.sp_send_dbmail
    --@profile_name = ''profile@xyc.com'',
    @recipients = ''dba@xyc.com;xyz@xyc.com'',
    @query = ''exec master..usp_getdeadlockinfo'',
    @subject = ''Please find the attached Dead Lock Reports'',
    --@body_format = ''HTML'',
    @attach_query_result_as_file = 1, 
    @query_result_header = 0,
    @query_attachment_filename = ''DeadLock.xml'',
    @file_attachments  = ''D:\dba\temp\xmlfile.xdl'',
    @query_no_truncate = 1,
    @query_result_width = 32767,
    @exclude_query_output = 1',
  @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


Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, or fitness for a particular purpose... Disclaimer Continued

Back to Database Journal Home



SQL Scripts & Samples Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL Scripts & Samples Forum
Topic By Replies Updated
sql help!! add 1 August 22nd, 11:58 AM
How To Combine These 2 Queries into 1 Query ? tarek_land 1 June 7th, 08:37 AM
solving query svibuk 1 February 3rd, 06:08 AM
converting from a character string to uniqueidentifier saturnius 4 January 4th, 05:56 AM