Sql-Server
DBA 阻止查詢電子郵件警報
當查詢阻塞發生在幾分鐘內時，有沒有人有一個很好的 Sql Alert 來發送電子郵件通知？我知道如何編寫自己的程式碼，但似乎是網際網路上一個很好的程式碼庫。如果可能，也請在沒有事件通知的情況下給出答案。
我喜歡下面這個： 發生阻塞時的電子郵件通知
我避免使用這些，因為它們使用我聽說已棄用的 sys.processes： 自動檢測阻塞
這與第一個類似，但沒有那麼多細節： 查找長時間執行的查詢
在實施之前嘗試找到好的解決方案或共識。隨意發送解決方案或在下面編輯一個。
謝謝，
注意：下面的一個似乎有一個不必要的臨時表步驟，可能可以消除，只需一封 sql 電子郵件。
*/ SET NOCOUNT ON -- Checked for currently running queries by putting data in temp table SELECT s.session_id ,r.STATUS ,r.blocking_session_id ,r.wait_type ,wait_resource ,r.wait_time / (1000.0) 'WaitSec' ,r.cpu_time ,r.logical_reads ,r.reads ,r.writes ,r.total_elapsed_time / (1000.0) 'ElapsSec' ,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, ( ( CASE r.statement_end_offset WHEN - 1 THEN Datalength(st.TEXT) ELSE r.statement_end_offset END - r.statement_start_offset ) / 2 ) + 1) AS statement_text ,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text ,r.command ,s.login_name ,s.host_name ,s.program_name ,s.host_process_id ,s.last_request_end_time ,s.login_time ,r.open_transaction_count INTO #temp_requests FROM sys.dm_exec_sessions AS s INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st WHERE r.session_id != @@SPID ORDER BY r.cpu_time DESC ,r.STATUS ,r.blocking_session_id ,s.session_id IF ( SELECT count(*) FROM #temp_requests WHERE blocking_session_id > 50 ) <> 0 BEGIN -- blocking found, sent email. DECLARE @tableHTML NVARCHAR(MAX); SET @tableHTML = N'<H1>Blocking Report</H1>' + N'<table border="1">' + N'<tr>' + N'<th>session_id</th>' + N'<th>Status</th>' + N'<th>blocking_session_id</th><th>wait_type</th><th>wait_resource</th>' + N'<th>WaitSec</th>' + N'<th>cpu_time</th>' + N'<th>logical_reads</th>' + N'<th>reads</th>' + N'<th>writes</th>' + N'<th>ElapsSec</th>' + N'<th>statement_text</th>' + N'<th>command_text</th>' + N'<th>command</th>' + N'<th>login_name</th>' + N'<th>host_name</th>' + N'<th>program_name</th>' + N'<th>host_process_id</th>' + N'<th>last_request_end_time</th>' + N'<th>login_time</th>' + N'<th>open_transaction_count</th>' + '</tr>' + CAST(( SELECT td = s.session_id ,'' ,td = r.STATUS ,'' ,td = r.blocking_session_id ,'' ,td = r.wait_type ,'' ,td = wait_resource ,'' ,td = r.wait_time / (1000.0) ,'' ,td = r.cpu_time ,'' ,td = r.logical_reads ,'' ,td = r.reads ,'' ,td = r.writes ,'' ,td = r.total_elapsed_time / (1000.0) ,'' ,td = Substring(st.TEXT, (r.statement_start_offset / 2) + 1, ( ( CASE r.statement_end_offset WHEN - 1 THEN Datalength(st.TEXT) ELSE r.statement_end_offset END - r.statement_start_offset ) / 2 ) + 1) ,'' ,td = Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), '') ,'' ,td = r.command ,'' ,td = s.login_name ,'' ,td = s.host_name ,'' ,td = s.program_name ,'' ,td = s.host_process_id ,'' ,td = s.last_request_end_time ,'' ,td = s.login_time ,'' ,td = r.open_transaction_count FROM sys.dm_exec_sessions AS s INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st WHERE r.session_id != @@SPID AND blocking_session_id > 0 ORDER BY r.cpu_time DESC ,r.STATUS ,r.blocking_session_id ,s.session_id FOR XML PATH('tr') ,TYPE ) AS NVARCHAR(MAX)) + N'</table>'; EXEC msdb.dbo.sp_send_dbmail @body = @tableHTML ,@body_format = 'HTML' ,@profile_name = N'Database Mail' ,@recipients = N'blakhani@outlook.com' ,@Subject = N'Blocking Detected' END DROP TABLE #temp_requests
我編寫了使用事件通知的阻塞檢測器。你可以在這裡找到
以下是它的工作原理：
This script will create - a blocking detection alert - table to hold blocking information - modify the 'blocked process threshold' sp_configure option to 5 mins (300 sec) - create a sql agent job that will fire in resonse to the alert to capture blocking info完整腳本：
/************************************************************************************** Author: KIN SHAH Date : 03/02/2011 Adapt the script as per your env --> places to change ---- CHANGE HERE !! This script will create - a blocking detection alert - table to hold blocking information - modify the 'blocked process threshold' sp_configure option to 5 mins (300 sec) - create a sql agent job that will fire in resonse to the alert to capture blocking info Disclaimer The views expressed on my posts on this site are mine alone and do not reflect the views of my company. All posts of mine are provided "AS IS" with no warranties, and confers no rights. The following disclaimer applies to all code, scripts and demos available on my posts: This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. I grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to use my name, logo, or trademarks to market Your software product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend me from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code. ************************************************************************************/ USE [dbaalert] ---- CHANGE HERE !! GO IF OBJECT_ID('dbo.Scores', 'U') IS NOT NULL drop table [dbo].[BlockingInfo]; -- *************change the blocking threshold to 5mins (300sec) ********** ---- EXEC sp_configure 'blocked process threshold', 300 ---- CHANGE HERE !! go reconfigure with override go --------- **** create table to hold blocking data ********* ---------- /****** Object: Table [dbo].[BlockingInfo] Script Date: 02/17/2011 15:41:35 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[BlockingInfo]( [RecordId] [int] IDENTITY(1,1) NOT NULL, [AlertTime] [datetime] NOT NULL, [BlockingDetails] [xml] NULL, [Notified] [int] NOT NULL, PRIMARY KEY CLUSTERED ( [RecordId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[BlockingInfo] ADD CONSTRAINT [DF_blocking_flag] DEFAULT ((0)) FOR [Notified] GO SET QUOTED_IDENTIFIER OFF; GO ---------------------------- disable old and create new job--------------------------------------------- USE [msdb] GO --- disable old job on the server IF EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = N'DBA Group - Monitoring - Blocking Detector') EXEC msdb.dbo.sp_update_job @job_name=N'DBA Group - Monitoring - Blocking Detector', @enabled = 0 GO USE [msdb] GO /****** Object: Job [DBA Group - Monitoring - Blocked Process Detector] Script Date: 03/02/2011 11:47:32 ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 03/02/2011 11:47:33 ******/ 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'DBA Group - Monitoring - Blocked Process Detector', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'Authors: Kin Shah', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'sa', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [Insert Blocking info] Script Date: 03/02/2011 11:47:34 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert Blocking 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 dbo.BlockingInfo ( AlertTime, BlockingDetails ) VALUES ( GETDATE(), ''$(ESCAPE_NONE(WMI(TextData)))'' )', @database_name=N'dbaalert', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [Send Email] Script Date: 03/02/2011 11:47:35 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Send Email', @step_id=2, @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' SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO if exists (select 1 from dbo.BlockingInfo where Notified = 0 ) begin DECLARE @AlertTime datetime DECLARE @BlockingDetails xml DECLARE @RecordID int -- Block Events table. if object_id (''tempdb..#BlockEvents'') > 0 drop table #BlockEvents create table #BlockEvents ( AlertTime datetime, BlockingDetails xml, RecordID int ) ; if object_id (''tempdb..#dba_job_name'') > 0 drop table #dba_job_name create table #dba_job_name ( id int identity (1,1), job_sid varchar(256) NULL, job_name varchar(256) NULL ) -- Block Info table. if object_id (''tempdb..#BlockInfo'') > 0 drop table #BlockInfo create table #BlockInfo ( RecordID int, BlockedDBName sysname null, BlockedHostName sysname null, BlockingDBName sysname null, BlockingHostName sysname null, BlockedWaitResource varchar (256) NULL, WaitTime_sec int null, BlockedTransactionName varchar(256) NULL, BlockedSPID int NULL, BlockedProgram varchar(256) NULL, BlockedProgram_sid varchar(256) NULL, BlockedProgram_jn varchar(256) NULL, BlockingSPID int NULL, BlockingProgram varchar(256) NULL, BlockingProgram_sid varchar(256) NULL, BlockingProgram_jn varchar(256) NULL ) ; -- Get all blocking events within selected period. INSERT INTO #BlockEvents ( AlertTime, BlockingDetails, RecordID ) SELECT AlertTime, BlockingDetails, RecordID FROM dbo.BlockingInfo where Notified = 0; WHILE EXISTS (SELECT RecordID FROM #BlockEvents) BEGIN SELECT TOP 1 @AlertTime = AlertTime, @BlockingDetails = BlockingDetails, @RecordID = RecordID FROM #BlockEvents ORDER BY RecordID DESC -- Insert into temporary table for processing. INSERT INTO #BlockInfo SELECT @RecordID,db_name(a.BlockedCurrentDb),a.BlockedHostName,db_name(a.BlockingCurrentDb),a.BlockingHostName, a.BlockedWaitResource ,a.BlockedWaitTime/(1000) as WaitTime_sec,a.BlockedTransactionName,a.BlockedSPID,a.BlockedProgram,null,null ,a.BlockingSPID,a.BlockingProgram,null,null FROM (SELECT ref.value(''./blocked-process[1]/process[1]/@waitresource'', ''varchar(512)'') AS BlockedWaitResource, ref.value(''./blocked-process[1]/process[1]/@waittime'', ''int'') AS BlockedWaitTime, ref.value(''./blocked-process[1]/process[1]/@transactionname'', ''sysname'') AS BlockedTransactionName, ref.value(''./blocked-process[1]/process[1]/@spid'', ''int'') AS BlockedSPID, ref.value(''./blocked-process[1]/process[1]/@clientapp'', ''varchar(256)'') AS BlockedProgram, ref.value(''./blocked-process[1]/process[1]/@hostname'', ''varchar(256)'') AS BlockedHostName, ref.value(''./blocked-process[1]/process[1]/@loginname'', ''varchar(256)'') AS BlockedLoginName, ref.value(''./blocked-process[1]/process[1]/@currentdb'', ''varchar(256)'') AS BlockedCurrentDb, ref.value(''./blocking-process[1]/process[1]/@waitresource'', ''varchar(512)'') AS BlockingWaitResource, ref.value(''./blocking-process[1]/process[1]/@waittime'', ''int'') AS BlockingWaitTime, ref.value(''./blocking-process[1]/process[1]/@transactionname'', ''sysname'') AS BlockingTransactionName, ref.value(''./blocking-process[1]/process[1]/@spid'', ''int'') AS BlockingSPID, ref.value(''./blocking-process[1]/process[1]/@clientapp'', ''varchar(256)'') AS BlockingProgram, ref.value(''./blocking-process[1]/process[1]/@hostname'', ''varchar(256)'') AS BlockingHostName, ref.value(''./blocking-process[1]/process[1]/@loginname'', ''varchar(256)'') AS BlockingLoginName, ref.value(''./blocking-process[1]/process[1]/@currentdb'', ''varchar(256)'') AS BlockingCurrentDb FROM @BlockingDetails.nodes(''//blocked-process-report'') AS node(ref)) AS a DELETE FROM #BlockEvents WHERE RecordID = @RecordID END insert into #dba_job_name (job_sid) select distinct left(right(([BlockedProgram]),44),34) from #BlockInfo where lower(BlockedProgram) like ''sqlagent%'' union select distinct left(right(([BlockingProgram]),44),34) from #BlockInfo where lower(BlockingProgram) like ''sqlagent%'' -- update job_name on #dba_job_name -- Due to uniqueidentifier problem, we need to run under @sqltext declare @cur int,@tol int select @cur=1,@tol =0 select @tol= max(id) from #dba_job_name declare @job_id varchar(max),@sqltext varchar(max) while (@cur<=@tol) begin select @job_id = job_sid from #dba_job_name where id = @cur select @sqltext=''update #dba_job_name set job_name = (select [name] from msdb..sysjobs with (nolock) where job_id = ''+@job_id+'') where id =''+convert(varchar(max),@cur) exec (@sqltext); select @cur=@cur+1 end -- strip out job_id update #BlockInfo set BlockedProgram_sid = case when (lower(BlockedProgram) like ''sqlagent%'') then left(right(([BlockedProgram]),44),34) end, BlockingProgram_sid = case when (lower(BlockingProgram) like ''sqlagent%'') then left(right(([BlockingProgram]),44),34) end -- update job_name for BlockedProgram update b set b.BlockedProgram_jn = j.job_name from #BlockInfo b join #dba_job_name j on b.BlockedProgram_sid = j.job_sid -- update job_name for BlockingProgram update b set b.BlockingProgram_jn = j.job_name from #BlockInfo b join #dba_job_name j on b.BlockingProgram_sid = j.job_sid declare @tableHTML nvarchar(max) set @tableHTML =N''<H3><FONT SIZE="3" FACE="Tahoma">Blocking Has occured.. Please Investigate on ''+ @@servername +''</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">BlockedDBName</th>'' + N''<th align="center">BlockedHostName</th>'' + N''<th align="center">BlockingDBName</th>'' + N''<th align="center">BlockingHostName</th>'' + N''<th align="center">BlockedWaitResource</th>'' + N''<th align="center">WaitTime_sec</th>'' + N''<th align="center">BlockedTransactionName</th>'' + N''<th align="center">BlockedSPID</th>'' + N''<th align="center">BlockedProgram</th>'' + N''<th align="center">BlockingSPID</th>'' + N''<th align="center">BlockingProgram</th>'' + N''</tr>'' + ISNULL(CAST ( ( select td = '''', td = ISNULL(RecordId,''''),'''', td = ISNULL(BlockedDBName,''**No data available**''),'''', td = ISNULL(BlockedHostName,''**No data available**''),'''', td = ISNULL(BlockingDBName,''**No data available**''),'''', td = ISNULL(BlockingHostName,''**No data available**''),'''', td = ISNULL(BlockedWaitResource,''**No data available**''),'''', td = ISNULL(WaitTime_sec,''''),'''', td = ISNULL(BlockedTransactionName,''**No data available**''),'''', td = ISNULL(BlockedSPID,''''),'''', td = ISNULL(COALESCE(BlockedProgram_jn,BlockedProgram),''**No data available**''),'''', td = ISNULL(BlockingSPID,''''),'''', td = ISNULL(COALESCE(BlockingProgram_jn,BlockingProgram),''**No data available**''),'''' from #BlockInfo where BlockingDBName is not null FOR XML PATH(''tr''), TYPE ) AS NVARCHAR(MAX) ),'''') + N''</FONT>'' + N''</table>'' ; ------------ send email declare @subject1 varchar(50) set @subject1 = ''Blocked Process Report for ''+@@servername EXEC msdb.dbo.sp_send_dbmail @profile_name = ''You db mail profile'', ---- CHANGE HERE !! @recipients=''yourcompanyDBATEAM@company.com'', ---- CHANGE HERE !! @subject = @subject1, @body = @tableHTML, @body_format = ''HTML'' ; end go -- drop all temp tables if object_id (''tempdb..#BlockEvents'') > 0 drop table #BlockEvents if object_id (''tempdb..#dba_job_name'') > 0 drop table #dba_job_name if object_id (''tempdb..#BlockInfo'') > 0 drop table #BlockInfo -- update the BlockingInfo table so that when the job runs it wont send out alert update dbo.BlockingInfo set Notified = 1 where Notified = 0 ', @database_name=N'dbaalert', ---- CHANGE HERE !! @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [Trim Records Older than 15 days] Script Date: 03/02/2011 11:47:35 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Trim Records Older than 15 days', @step_id=3, @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'use dbaalert ---- CHANGE HERE !! go declare @starttime datetime -- trim records older than 15 days select @starttime = CONVERT(varchar,GETDATE()-15,112) --select @starttime delete from BlockingInfo where convert(varchar, AlertTime, 112) <= @starttime and Notified =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 ------------ create alert (this is dynamic for default and named instances) USE [msdb] GO -- create an WMI alert to respond to blocking IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to Blocking') EXEC msdb.dbo.sp_delete_alert @name=N'Respond to Blocking' 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 Blocking', @wmi_namespace=@server_namespace, @wmi_query=N'SELECT * FROM BLOCKED_PROCESS_REPORT', @job_name=N'DBA Group - Monitoring - Blocked Process Detector' ---- CHANGE HERE job name!! GO使用 DMV 的另一種選擇是（根據您的需要自定義）：
- beta_lockinfo - 來自 Erland Sommarskog
- view_Running_Blocked_processes.sql - 來自 MS Tiger 團隊