Automatic e-mail alerts when SQL database is blocked

1. Build a query for blocked views

Create View [dbo].[Vw_waitingcount] asSELECTS.SESSION_ID, r.blocking_session_id, S.host_name, S.login_name, DatabaseName= db_name(r.database_id), R.command, R.status asSqlexecstatus, Current_execute_sql= substring(T.text, R.statement_start_offset/ 2 + 1, Case  whenStatement_end_offset= - 1  Then Len(T.text)                       ELSE(R.statement_end_offset-Statement_start_offset)/ 2 + 1 END), S.program_name, S.status, S.cpu_time, memory_usage_kb=S.memory_usage* 8, S.reads, S.writes, S.transaction_isolation_level, C.connect_time, C.last_read, C.last_write, C . Net_transport, C.client_net_address, C.client_tcp_port, C.local_tcp_port, R.start_time, R.wait_time , R.wait_type, R.last_wait_type, R.wait_resource, R.open_transaction_count, r.transaction_id fromsys.dm_exec_sessions s Left JOINsys.dm_exec_connections C ons.session_id=c.session_id Left JOINsys.dm_exec_requests R ons.session_id=r.session_id andc.connection_id=r.connection_idOUTERapply Sys.dm_exec_sql_text (r.sql_handle) TWHERE     1 = 1  andS.is_user_process= 1  andCommand is  not NULL

2. Configure SQL to send messages (omit ... ）

3. Build Stored Procedures

 Use [Qhw_shop]GOSETAnsi_nulls onGOSETQuoted_identifier onGOCREATE PROCEDURE [dbo].[Checkblockingandsendmail]@sendtype int =1 asDeclare @ccount intSelect @ccount=COUNT(1) from [Vw_waitingcount] whereblocking_session_id<>0--Print @ccountif(@ccount>0)begin    waitforDelay'00:00:10'--define wait 10 seconds    Select @ccount=COUNT(1) from [Vw_waitingcount] whereblocking_session_id<>0    if(@ccount>0)        begin            waitforDelay'00:00:10'--define wait 10 seconds            Select @ccount=COUNT(1) from [Vw_waitingcount] whereblocking_session_id<>0            if(@ccount>0)            begin                if @sendtype=1                    begin                        --send a message with simple text                        EXECmsdb: Sp_send_dbmail@profile_name = 'sendmailby126',--Profile name                        @recipients = '[email protected]; [Email protected]',--Recipient                        @subject =N'the database is blocked, please log in to the server to view',--message Header                        @body =N'the database is blocked, please log in to the server to view',--Message Content                        @body_format = 'HTML'--Message Format                    End                 if @sendtype=2                    begin                                                --send a message that contains a query                        EXECmsdb: Sp_send_dbmail@profile_name = 'sendmailby126',                        @recipients = '[email protected]; [Email protected]',                        @subject = 'database blocked, results see text',                        --@query = ' SET NOCOUNT on;select getdate () '                        --@query = ' Select GETDATE () as the time of occurrence; select COUNT (1) Total records from [Vw_waitingcount] where Blocking_session_id<>0;sel ECT * from Vw_waitingcount where session_id on (select blocking_session_id from Vw_waitingcount where blocking_session_id& gt;0) and Blocking_session_id=0 '                        @query = 'Select GETDATE () as the time of occurrence; Select Session_id,blocking_session_id,databasename,current_execute_sql,wait_time,last _wait_type from [Vw_waitingcount] where session_id in (select blocking_session_id from [qhw_shop].[ DBO]. [Vw_waitingcount] where blocking_session_id>0) and Blocking_session_id=0'                        --, @body_format = ' HTML '--Message format                    End            End        End End

4. Set up the job, every 1 minutes or 10 minutes or more time for yourself, the job executes the following code

exec @sendtype = 2

5. Mailbox can and binding, a blocking alert, you can receive information, log in to the server, query the source of the blocked SPID, and query the execution of the SQL statement, if not very important, you can kill off.

Automatic e-mail alerts when SQL database is blocked