Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

SQL Servers has Traces and XEvents. These are used to capture and analyze what is going on with our SQL Server Instances. Events are stored in the stack for later analysis. For example, If I decide to monitor any dead lock in the database, I just query the trace file to see the history of deadlock for a period of time. Here is my question:

While events occur, in our example deadlock event, is there a way to get an email notification using msdb.dbo.xp_send_dbmail?

share|improve this question
add comment

2 Answers

Yes you can do that using Event Notification for deadlocks / blocking / create database / drop database, and many more events as outlined here.

Below is the script that will help you with Deadlock detection and email in real time :

It will create an alert as well as a TSQL Job to fire with all the details emailed to DBA team. look for change Here to replace required stuff.

use dba_db ---- change HERE use find and replace as per your database name !!
go
/********************************************************************
Job Name    :   Monitoring - Deadlock Detector  
Created by  :   Kin for dba.stackexchange.com
Version     :   V1.0
Funtionality:   1. Real time Deadlock detection using WMI
                2. Send email out when deadlock occurs along with 
                   Deadlock Graph attached.
                3. Logs all the deadlocks in a central Table
                   dba_db.dbo.Deadlock
                4. Does not require trace flags 1204 and 1222 enabled
*********************************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Deadlock]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
Print ' *** Table Deadlock Table Already Exists... moving Further *** '
ELSE
create table Deadlock
(
RecordId int identity (1,1) primary key not null,
AlertTime datetime not null,
DeadlockGraph xml,
Notified int not null constraint [DF_deadlock_flag] default (0)
)
go
create index deadlock_idx on Deadlock  (AlertTime) with fillfactor = 100 
go
USE [msdb]
GO
-- No need to enable deadlock trace flags
--dbcc traceon (1204,1222,-1)
--go
-- enable replace runtime tokens for sql agent to respond to WMI alets
USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens=1
GO
-- create the 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'Monitoring - Deadlock Detector', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'Job Name :   Monitoring - Deadlock Detector  
Created by  :   Kin for dba.stackexchange.com
Version     :   V1.0
Funtionality:   1. Real time Deadlock detection using WMI
                2. Send email out when deadlock occurs along with 
                   Deadlock Graph attached.
                3. Logs all the deadlocks in a central Table
                   dba_db.dbo.Deadlock
                4. Does not require trace flags 1204 and 1222 enabled', 
        @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'Insert Deadlock info', 
        @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 Deadlock (

AlertTime,

DeadlockGraph

)

VALUES (

GETDATE(),

''$(ESCAPE_NONE(WMI(TextData)))''

)', 
        @database_name=N'dba_db', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Send Email with Deadlock Graph]    Script Date: 10/01/2010 12:01:45 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Send Email with Deadlock Graph', 
        @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'if exists (select 1 from dba_db.dbo.Deadlock where notified = 0 )
begin
declare @tableHTML nvarchar(max)
set @tableHTML =N''<H3><FONT SIZE="3" FACE="Tahoma">Deadlock Has occured on ''+@@servername+'' .. Please Investigate Immediately </FONT></H3>''
set @tableHTML = @tableHTML+ N''<table border="1">'' +
           N''<FONT SIZE="2" FACE="Calibri">'' +            
            N''<tr><th align="center">RecordId</th>'' +
            N''<th align="center">AlertTime</th>'' +
            N''<th align="center">DeadlockGraph</th>'' +

            N''</tr>'' +
           ISNULL(CAST ( ( 
                            select  td = '''',
                                    td = RecordId,'''',
                                    td = AlertTime,'''',
                                    td = DeadlockGraph,''''

                     from dba_db.dbo.Deadlock where notified = 0

    FOR XML PATH(''tr''), TYPE 

            ) AS NVARCHAR(MAX) ),'''') +
            N''</FONT>'' +
            N''</table>'' ;
-- bcp out as .xdl file. This is the deadlock graph that will be emailed. Note that it will be overwritten everytime !!
exec master..xp_cmdshell ''BCP.exe "SELECT  [Deadlockgraph].query(''''/TextData/deadlock-list'''') FROM dba_db.dbo.Deadlock where Notified = 0" queryout "d:\logs\deadlock.xdl" -c -q -T -Slocalhost''; ---- change localhost ..with the servername\instance or servername !!
-- send email out with the graph attached 
declare @subject1 varchar(50)
set @subject1 = ''Deadlock Has Occured on ''+@@servername
EXEC msdb.dbo.sp_send_dbmail 
            @profile_name = ''DBMAIL PROFILE'',             ---- change HERE db mail profile !!
            @recipients=''DBAcompanyGroup@companyName.com'', ---- change HERE  email group!!
            @subject = @subject1,
            @body = @tableHTML,
            @body_format = ''HTML'', 
            @file_attachments = ''D:\logs\Deadlock.xdl''; ---- change HERE  deadlock graph location!!
end
go
-- update the Deadlock table so that when the job runs it wont send out previous alert
update dba_db.dbo.Deadlock
set Notified = 1 where notified = 0
--SELECT * FROM dba_db.dbo.Deadlock', 
        @database_name=N'dba_db', 
        @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:

-- create an WMI alert to respond to deadlocks
IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to Deadlock')
EXEC msdb.dbo.sp_delete_alert @name=N'Respond to Deadlock'
GO
DECLARE @server_namespace varchar(255)
IF ISNULL(CHARINDEX('\', @@SERVERNAME), 0) > 0
SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + SUBSTRING(@@SERVERNAME, ISNULL(CHARINDEX('\', @@SERVERNAME), 0) + 1, LEN(@@SERVERNAME) - ISNULL(CHARINDEX('/', @@SERVERNAME), 0))
ELSE
SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER'
EXEC msdb.dbo.sp_add_alert @name=N'Respond to Deadlock',
@wmi_namespace=@server_namespace,
@wmi_query=N'SELECT * FROM DEADLOCK_GRAPH',
@job_name='Monitoring - Deadlock Detector' ;
GO
share|improve this answer
add comment

Yes, you can monitor for this event. There are a number of ways to do it and a number of third party apps that will be able to do this but you can handle all of it inside SQL Server.

You will want to create a stored procedure that reads the error log and captures the events you are interested in, and use that sp to send them out using dbmail. You will then need to create a SQL Agent job that calls this stored procedure at whatever interval you determine.

There are a number of good examples of how to do this, both create the stored procedure and the SQL Agent job so I won't go into the coding. Here is a good starter piece from the folks over at MSSQLTIPS Error log monitoring

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.