USE [YourSQLDba] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Maint].[sp_who_blocking]') AND type in (N'P', N'PC')) DROP PROCEDURE [Maint].[sp_who_blocking] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --================================================================================================================== -- ProcedureName : [Maint].[sp_who_blocking] -- Author : Kerry http://www.cnblogs.com/kerrycode/ -- CreateDate : 2014-04-23 -- Description : 監控數據庫阻塞情況，顯示阻塞會話信息或收集阻塞會話信息或發送告警郵件 /****************************************************************************************************************** Parameters ： 參數說明 ******************************************************************************************************************** @OutType : 默認為輸出阻塞會話信息，"Table", "Email"分別表示將阻塞信息寫入表或郵件發送 @EmailSubject : 郵件主題.默認為Sql Blocking Alert，一般指定，例如“ServerName Sql Blocking Alert" @ProfileName : @profile_name 默認值為YourSQLDba_EmailProfile @RecipientsLst : 收件人列表 ******************************************************************************************************************** Modified Date Modified User Version Modified Reason ******************************************************************************************************************** 2014-04-23 Kerry V01.00.00 新建存儲過程[Maint].[sp_who_blocking] *******************************************************************************************************************/ --================================================================================================================== CREATE PROCEDURE [Maint].[sp_who_blocking] ( @OutType VARCHAR(8) ='Default' , @EmailSubject VARCHAR(120)='Sql Blocking Alert' , @ProfileName sysname='YourSQLDba_EmailProfile' , @RecipientsLst VARCHAR(MAX) = NULL ) AS BEGIN SET NOCOUNT ON; DECLARE @HtmlContent NVARCHAR(MAX) ; IF @OutType NOT IN ('Default', 'Table','Email') BEGIN PRINT 'The parameter @OutType is not correct,please check it'; return; END IF @OutType ='Default' BEGIN SELECT db.name AS DatabaseName ,wt.blocking_session_id AS BlockingSessesionId ,sp.program_name AS ProgramName ,COALESCE(sp.LOGINAME, sp.nt_username) AS UserName ,ec1.client_net_address AS ClientIpAddress ,wt.wait_type AS WaitType ,ec1.connect_time AS BlockingStartTime ,wt.WAIT_DURATION_MS/1000 AS WaitDuration ,ec1.session_id AS BlockedSessionId ,h1.TEXT AS BlockedSQLText ,h2.TEXT AS BlockingSQLText FROM sys.dm_tran_locks AS tl INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id LEFT OUTER JOIN master.dbo.sysprocesses sp ON SP.spid = wt.blocking_session_id CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1 CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2; END ELSE IF @OutType='Table' BEGIN INSERT INTO [Maint].[BlockingSQLHistory] SELECT GETDATE() AS RecordTime ,db.name AS DatabaseName ,wt.blocking_session_id AS BlockingSessesionId ,sp.program_name AS ProgramName ,COALESCE(sp.LOGINAME, sp.nt_username) AS UserName ,ec1.client_net_address AS ClientIpAddress ,wt.wait_type AS WaitType ,ec1.connect_time AS BlockingStartTime ,wt.WAIT_DURATION_MS/1000 AS WaitDuration ,ec1.session_id AS BlockedSessionId ,h1.TEXT AS BlockedSQLText ,h2.TEXT AS BlockingSQLText FROM sys.dm_tran_locks AS tl INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id LEFT OUTER JOIN master.dbo.sysprocesses sp ON SP.spid = wt.blocking_session_id CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1 CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2; END ELSE IF @OutType='Email' BEGIN SET @HtmlContent = N'<head>' + N'<style type="text/css">h2, body {font-family: Arial, verdana;} table{font-size:11px; border-collapse:collapse;} td{background-color:#F1F1F1; border:1px solid black; padding:3px;} th{background-color:#99CCFF;}</style>' + N'<table border="1">' + N'<tr> <th>DatabaseName</th> <th>BlockingSessesionId</th> <th>ProgramName</th> <th>UserName</th> <th>ClientIpAddress</th> <th>WaitType</th> <th>BlockingStartTime</th> <th>WaitDuration</th> <th>BlockedSessionId</th> <th>BlockedSQLText</th> <th>BlockingSQLText</th> </tr>' + CAST ( (SELECT db.name AS TD, '' ,wt.blocking_session_id AS TD, '' ,sp.program_name AS TD, '' ,COALESCE(sp.LOGINAME, sp.nt_username) AS TD, '' ,ec1.client_net_address AS TD, '' ,wt.wait_type AS TD, '' ,ec1.connect_time AS TD, '' ,wt.WAIT_DURATION_MS/1000 AS TD, '' ,ec1.session_id AS TD, '' ,h1.TEXT AS TD, '' ,h2.TEXT AS TD, '' FROM sys.dm_tran_locks AS tl INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id LEFT OUTER JOIN master.dbo.sysprocesses sp ON SP.spid = wt.blocking_session_id CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1 CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2 FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' IF @HtmlContent IS NOT NULL BEGIN EXEC msdb.dbo.sp_send_dbmail @profile_name = @ProfileName , @recipients = @RecipientsLst , @subject = @EmailSubject , @body = @HtmlContent , @body_format = 'HTML' ; END END END GO |