1.建查询是否有阻塞的视图
create view [dbo].[VW_WaitingCount]
as
SELECT s.session_id, r.blocking_session_id, s.host_name, s.login_name, databasename = db_name(r.database_id), r.command, r.status AS sqlexecstatus,
current_execute_sql = substring(t .text, r.statement_start_offset / 2 + 1, CASE WHEN statement_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
FROM sys.dm_exec_sessions s LEFT JOIN
sys.dm_exec_connections c ON s.session_id = c.session_id LEFT JOIN
sys.dm_exec_requests r ON s.session_id = r.session_id AND c.connection_id = r.connection_id OUTER apply sys.dm_exec_sql_text(r.sql_handle) t
WHERE 1 = 1 AND s.is_user_process = 1 AND command IS NOT NULL
2.配置SQL发送邮件（略去。。。）
3.建存储过程
USE [qhw_shop]
GO SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CheckBlockingAndSendmail]
@sendtype int =1
as
declare @ccount int
select @ccount=COUNT(1) from [VW_WaitingCount] where blocking_session_id<>0
--print @ccount
if(@ccount>0)
begin
waitfor delay '00:00:10'--定义等待10秒
select @ccount=COUNT(1) from [VW_WaitingCount] where blocking_session_id<>0
if(@ccount>0)
begin
waitfor delay '00:00:10'--定义等待10秒
select @ccount=COUNT(1) from [VW_WaitingCount] where blocking_session_id<>0
if(@ccount>0)
begin
if @sendtype=1
begin
--发送简单文本的邮件
EXEC msdb..sp_send_dbmail
@profile_name = 'sendmailby126',--profile名称
@recipients = '[email protected];[email protected]',--收件人
@subject = N'数据库有阻塞，请登录服务器查看',--邮件标题
@body = N'数据库有阻塞，请登录服务器查看',--邮件内容
@body_format = 'HTML'--邮件格式
end
if @sendtype=2
begin --发送包含查询的邮件
EXEC msdb..sp_send_dbmail
@profile_name = 'sendmailby126',
@recipients = '[email protected];[email protected]',
@subject = '数据库有阻塞，结果见正文',
--@query = 'SET NOCOUNT ON;select getdate()'
--@query = 'select getdate() as 发生时间;select count(1) 记录总数 from [VW_WaitingCount] where blocking_session_id<>0;select * from vw_waitingcount where session_id in (select blocking_session_id from vw_waitingcount where blocking_session_id>0) and blocking_session_id=0'
--================查询中一定要加上库名.架构名.对象名！！！！！==================，因为发送邮件时，调用的是MSDB库！！！！！
@query = 'select getdate() as 发生时间;select session_id,blocking_session_id,databasename,current_execute_sql,wait_time,last_wait_type from [qhw_shop].[dbo].[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'--邮件格式
end
end
end
end
--================查询中一定要加上库名.架构名.对象名！！！！！==================,因为发送邮件时，调用的是msdb库！！！！！！
4.建立作业，每隔1分钟或10分钟或多长时间自己设置，作业执行如下代码
exec dbname.dbo.CheckBlockingAndSendmail @sendtype=2
5.邮箱可以和微信绑定，一有阻塞警报，微信即可收到信息，登录服务器，查询出阻塞的源头SPID,并查询出执行的sql语句，如果不是很重要，可以KILL掉。