· 8 years ago · Jun 21, 2018, 09:54 AM
1*/
2 SET NOCOUNT ON
3
4-- Checked for currently running queries by putting data in temp table
5SELECT s.session_id
6 ,r.STATUS
7 ,r.blocking_session_id
8 ,r.wait_type
9 ,wait_resource
10 ,r.wait_time / (1000.0) 'WaitSec'
11 ,r.cpu_time
12 ,r.logical_reads
13 ,r.reads
14 ,r.writes
15 ,r.total_elapsed_time / (1000.0) 'ElapsSec'
16 ,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
17 (
18 CASE r.statement_end_offset
19 WHEN - 1
20 THEN Datalength(st.TEXT)
21 ELSE r.statement_end_offset
22 END - r.statement_start_offset
23 ) / 2
24 ) + 1) AS statement_text
25 ,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
26 ,r.command
27 ,s.login_name
28 ,s.host_name
29 ,s.program_name
30 ,s.host_process_id
31 ,s.last_request_end_time
32 ,s.login_time
33 ,r.open_transaction_count
34INTO #temp_requests
35FROM sys.dm_exec_sessions AS s
36INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
37CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
38WHERE r.session_id != @@SPID
39ORDER BY r.cpu_time DESC
40 ,r.STATUS
41 ,r.blocking_session_id
42 ,s.session_id
43
44IF (
45 SELECT count(*)
46 FROM #temp_requests
47 WHERE blocking_session_id > 50
48 ) <> 0
49BEGIN
50 -- blocking found, sent email.
51 DECLARE @tableHTML NVARCHAR(MAX);
52
53 SET @tableHTML = N'<H1>Blocking Report</H1>' + N'<table border="1">' + N'<tr>' + N'<th>session_id</th>' + N'<th>Status</th>' +
54 N'<th>blocking_session_id</th><th>wait_type</th><th>wait_resource</th>' +
55 N'<th>WaitSec</th>' + N'<th>cpu_time</th>' +
56 N'<th>logical_reads</th>' + N'<th>reads</th>' +
57 N'<th>writes</th>' + N'<th>ElapsSec</th>' + N'<th>statement_text</th>' + N'<th>command_text</th>' +
58 N'<th>command</th>' + N'<th>login_name</th>' + N'<th>host_name</th>' + N'<th>program_name</th>' +
59 N'<th>host_process_id</th>' + N'<th>last_request_end_time</th>' + N'<th>login_time</th>' +
60 N'<th>open_transaction_count</th>' + '</tr>' + CAST((
61 SELECT td = s.session_id
62 ,''
63 ,td = r.STATUS
64 ,''
65 ,td = r.blocking_session_id
66 ,''
67 ,td = r.wait_type
68 ,''
69 ,td = wait_resource
70 ,''
71 ,td = r.wait_time / (1000.0)
72 ,''
73 ,td = r.cpu_time
74 ,''
75 ,td = r.logical_reads
76 ,''
77 ,td = r.reads
78 ,''
79 ,td = r.writes
80 ,''
81 ,td = r.total_elapsed_time / (1000.0)
82 ,''
83 ,td = Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
84 (
85 CASE r.statement_end_offset
86 WHEN - 1
87 THEN Datalength(st.TEXT)
88 ELSE r.statement_end_offset
89 END - r.statement_start_offset
90 ) / 2
91 ) + 1)
92 ,''
93 ,td = Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) +
94 N'.' + Quotename(Object_name(st.objectid, st.dbid)), '')
95 ,''
96 ,td = r.command
97 ,''
98 ,td = s.login_name
99 ,''
100 ,td = s.host_name
101 ,''
102 ,td = s.program_name
103 ,''
104 ,td = s.host_process_id
105 ,''
106 ,td = s.last_request_end_time
107 ,''
108 ,td = s.login_time
109 ,''
110 ,td = r.open_transaction_count
111 FROM sys.dm_exec_sessions AS s
112 INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
113 CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
114 WHERE r.session_id != @@SPID
115 AND blocking_session_id > 0
116 ORDER BY r.cpu_time DESC
117 ,r.STATUS
118 ,r.blocking_session_id
119 ,s.session_id
120 FOR XML PATH('tr')
121 ,TYPE
122 ) AS NVARCHAR(MAX)) + N'</table>';
123
124 EXEC msdb.dbo.sp_send_dbmail @body = @tableHTML
125 ,@body_format = 'HTML'
126 ,@profile_name = N'Database Mail'
127 ,@recipients = N'blakhani@outlook.com'
128 ,@Subject = N'Blocking Detected'
129END
130
131DROP TABLE #temp_requests
132
133This script will create
134 - a blocking detection alert
135 - table to hold blocking information
136 - modify the 'blocked process threshold' sp_configure option to 5 mins (300 sec)
137 - create a sql agent job that will fire in resonse to the alert to capture blocking info
138
139/**************************************************************************************
140Author: KIN SHAH
141Date : 03/02/2011
142
143Adapt the script as per your env --> places to change ---- CHANGE HERE !!
144
145This script will create
146 - a blocking detection alert
147 - table to hold blocking information
148 - modify the 'blocked process threshold' sp_configure option to 5 mins (300 sec)
149 - create a sql agent job that will fire in resonse to the alert to capture blocking info
150
151Disclaimer
152The 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.
153
154The following disclaimer applies to all code, scripts and demos available on my posts:
155
156This 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.
157
158I 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:
159
160(i) to use my name, logo, or trademarks to market Your software product in which the Sample Code is embedded;
161(ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and
162(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.
163
164************************************************************************************/
165USE [dbaalert] ---- CHANGE HERE !!
166GO
167
168IF OBJECT_ID('dbo.Scores', 'U') IS NOT NULL
169 drop table [dbo].[BlockingInfo];
170-- *************change the blocking threshold to 5mins (300sec) ********** ----
171EXEC sp_configure 'blocked process threshold', 300 ---- CHANGE HERE !!
172go
173reconfigure with override
174go
175--------- **** create table to hold blocking data ********* ----------
176
177/****** Object: Table [dbo].[BlockingInfo] Script Date: 02/17/2011 15:41:35 ******/
178SET ANSI_NULLS ON
179GO
180
181SET QUOTED_IDENTIFIER ON
182GO
183
184CREATE TABLE [dbo].[BlockingInfo](
185 [RecordId] [int] IDENTITY(1,1) NOT NULL,
186 [AlertTime] [datetime] NOT NULL,
187 [BlockingDetails] [xml] NULL,
188 [Notified] [int] NOT NULL,
189PRIMARY KEY CLUSTERED
190(
191 [RecordId] ASC
192)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
193) ON [PRIMARY]
194
195GO
196ALTER TABLE [dbo].[BlockingInfo] ADD CONSTRAINT [DF_blocking_flag] DEFAULT ((0)) FOR [Notified]
197GO
198SET QUOTED_IDENTIFIER OFF;
199GO
200---------------------------- disable old and create new job---------------------------------------------
201USE [msdb]
202GO
203
204--- disable old job on the server
205IF EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = N'DBA Group - Monitoring - Blocking Detector')
206EXEC msdb.dbo.sp_update_job @job_name=N'DBA Group - Monitoring - Blocking Detector', @enabled = 0
207GO
208
209USE [msdb]
210GO
211
212/****** Object: Job [DBA Group - Monitoring - Blocked Process Detector] Script Date: 03/02/2011 11:47:32 ******/
213BEGIN TRANSACTION
214DECLARE @ReturnCode INT
215SELECT @ReturnCode = 0
216/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 03/02/2011 11:47:33 ******/
217IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
218BEGIN
219EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
220IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
221
222END
223
224DECLARE @jobId BINARY(16)
225EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBA Group - Monitoring - Blocked Process Detector',
226 @enabled=1,
227 @notify_level_eventlog=0,
228 @notify_level_email=0,
229 @notify_level_netsend=0,
230 @notify_level_page=0,
231 @delete_level=0,
232 @description=N'Authors: Kin Shah',
233 @category_name=N'[Uncategorized (Local)]',
234 @owner_login_name=N'sa', @job_id = @jobId OUTPUT
235IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
236/****** Object: Step [Insert Blocking info] Script Date: 03/02/2011 11:47:34 ******/
237EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert Blocking info',
238 @step_id=1,
239 @cmdexec_success_code=0,
240 @on_success_action=3,
241 @on_success_step_id=0,
242 @on_fail_action=2,
243 @on_fail_step_id=0,
244 @retry_attempts=0,
245 @retry_interval=0,
246 @os_run_priority=0, @subsystem=N'TSQL',
247 @command=N'INSERT INTO dbo.BlockingInfo (
248
249AlertTime,
250
251BlockingDetails
252
253)
254
255VALUES (
256
257GETDATE(),
258
259''$(ESCAPE_NONE(WMI(TextData)))''
260
261)',
262 @database_name=N'dbaalert',
263 @flags=0
264IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
265/****** Object: Step [Send Email] Script Date: 03/02/2011 11:47:35 ******/
266EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Send Email',
267 @step_id=2,
268 @cmdexec_success_code=0,
269 @on_success_action=3,
270 @on_success_step_id=0,
271 @on_fail_action=2,
272 @on_fail_step_id=0,
273 @retry_attempts=0,
274 @retry_interval=0,
275 @os_run_priority=0, @subsystem=N'TSQL',
276 @command=N'
277SET ANSI_NULLS ON
278GO
279
280SET QUOTED_IDENTIFIER ON
281GO
282if exists (select 1 from dbo.BlockingInfo where Notified = 0 )
283begin
284
285DECLARE @AlertTime datetime
286DECLARE @BlockingDetails xml
287DECLARE @RecordID int
288-- Block Events table.
289if object_id (''tempdb..#BlockEvents'') > 0 drop table #BlockEvents
290create table #BlockEvents (
291AlertTime datetime,
292BlockingDetails xml,
293RecordID int
294) ;
295if object_id (''tempdb..#dba_job_name'') > 0
296drop table #dba_job_name
297create table #dba_job_name
298(
299 id int identity (1,1),
300 job_sid varchar(256) NULL,
301 job_name varchar(256) NULL
302)
303-- Block Info table.
304if object_id (''tempdb..#BlockInfo'') > 0
305drop table #BlockInfo
306create table #BlockInfo (
307RecordID int,
308BlockedDBName sysname null,
309BlockedHostName sysname null,
310BlockingDBName sysname null,
311BlockingHostName sysname null,
312BlockedWaitResource varchar (256) NULL,
313WaitTime_sec int null,
314BlockedTransactionName varchar(256) NULL,
315BlockedSPID int NULL,
316BlockedProgram varchar(256) NULL,
317BlockedProgram_sid varchar(256) NULL,
318BlockedProgram_jn varchar(256) NULL,
319BlockingSPID int NULL,
320BlockingProgram varchar(256) NULL,
321BlockingProgram_sid varchar(256) NULL,
322BlockingProgram_jn varchar(256) NULL
323
324) ;
325
326-- Get all blocking events within selected period.
327INSERT INTO #BlockEvents (
328AlertTime,
329BlockingDetails,
330RecordID
331)
332
333SELECT
334AlertTime,
335BlockingDetails,
336RecordID
337FROM dbo.BlockingInfo where Notified = 0;
338
339WHILE EXISTS (SELECT RecordID FROM #BlockEvents)
340
341BEGIN
342 SELECT TOP 1 @AlertTime = AlertTime,
343 @BlockingDetails = BlockingDetails,
344 @RecordID = RecordID
345 FROM #BlockEvents
346 ORDER BY RecordID DESC
347
348 -- Insert into temporary table for processing.
349
350 INSERT INTO #BlockInfo
351 SELECT @RecordID,db_name(a.BlockedCurrentDb),a.BlockedHostName,db_name(a.BlockingCurrentDb),a.BlockingHostName, a.BlockedWaitResource
352 ,a.BlockedWaitTime/(1000) as WaitTime_sec,a.BlockedTransactionName,a.BlockedSPID,a.BlockedProgram,null,null
353 ,a.BlockingSPID,a.BlockingProgram,null,null
354 FROM
355 (SELECT
356 ref.value(''./blocked-process[1]/process[1]/@waitresource'', ''varchar(512)'') AS BlockedWaitResource,
357 ref.value(''./blocked-process[1]/process[1]/@waittime'', ''int'') AS BlockedWaitTime,
358 ref.value(''./blocked-process[1]/process[1]/@transactionname'', ''sysname'') AS BlockedTransactionName,
359 ref.value(''./blocked-process[1]/process[1]/@spid'', ''int'') AS BlockedSPID,
360 ref.value(''./blocked-process[1]/process[1]/@clientapp'', ''varchar(256)'') AS BlockedProgram,
361 ref.value(''./blocked-process[1]/process[1]/@hostname'', ''varchar(256)'') AS BlockedHostName,
362 ref.value(''./blocked-process[1]/process[1]/@loginname'', ''varchar(256)'') AS BlockedLoginName,
363 ref.value(''./blocked-process[1]/process[1]/@currentdb'', ''varchar(256)'') AS BlockedCurrentDb,
364
365 ref.value(''./blocking-process[1]/process[1]/@waitresource'', ''varchar(512)'') AS BlockingWaitResource,
366 ref.value(''./blocking-process[1]/process[1]/@waittime'', ''int'') AS BlockingWaitTime,
367 ref.value(''./blocking-process[1]/process[1]/@transactionname'', ''sysname'') AS BlockingTransactionName,
368 ref.value(''./blocking-process[1]/process[1]/@spid'', ''int'') AS BlockingSPID,
369 ref.value(''./blocking-process[1]/process[1]/@clientapp'', ''varchar(256)'') AS BlockingProgram,
370 ref.value(''./blocking-process[1]/process[1]/@hostname'', ''varchar(256)'') AS BlockingHostName,
371 ref.value(''./blocking-process[1]/process[1]/@loginname'', ''varchar(256)'') AS BlockingLoginName,
372 ref.value(''./blocking-process[1]/process[1]/@currentdb'', ''varchar(256)'') AS BlockingCurrentDb
373 FROM @BlockingDetails.nodes(''//blocked-process-report'')
374 AS node(ref)) AS a
375
376 DELETE FROM #BlockEvents
377 WHERE RecordID = @RecordID
378
379END
380
381insert into #dba_job_name (job_sid)
382select distinct left(right(([BlockedProgram]),44),34) from #BlockInfo where lower(BlockedProgram) like ''sqlagent%''
383union
384select distinct left(right(([BlockingProgram]),44),34) from #BlockInfo where lower(BlockingProgram) like ''sqlagent%''
385
386-- update job_name on #dba_job_name
387-- Due to uniqueidentifier problem, we need to run under @sqltext
388declare @cur int,@tol int
389select @cur=1,@tol =0
390select @tol= max(id) from #dba_job_name
391declare @job_id varchar(max),@sqltext varchar(max)
392
393while (@cur<=@tol)
394begin
395 select @job_id = job_sid from #dba_job_name where id = @cur
396 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)
397 exec (@sqltext);
398 select @cur=@cur+1
399end
400
401-- strip out job_id
402update #BlockInfo
403set BlockedProgram_sid = case when (lower(BlockedProgram) like ''sqlagent%'') then left(right(([BlockedProgram]),44),34) end,
404 BlockingProgram_sid = case when (lower(BlockingProgram) like ''sqlagent%'') then left(right(([BlockingProgram]),44),34) end
405
406-- update job_name for BlockedProgram
407update b
408set b.BlockedProgram_jn = j.job_name
409from #BlockInfo b join #dba_job_name j
410on b.BlockedProgram_sid = j.job_sid
411
412-- update job_name for BlockingProgram
413update b
414set b.BlockingProgram_jn = j.job_name
415from #BlockInfo b join #dba_job_name j
416on b.BlockingProgram_sid = j.job_sid
417
418declare @tableHTML nvarchar(max)
419set @tableHTML =N''<H3><FONT SIZE="3" FACE="Tahoma">Blocking Has occured.. Please Investigate on ''+ @@servername +''</FONT></H3>''
420set @tableHTML = @tableHTML + N''<table border="1">'' +
421 N''<FONT SIZE="2" FACE="Calibri">'' +
422 N''<tr><th align="center">RecordId</th>'' +
423 N''<th align="center">BlockedDBName</th>'' +
424 N''<th align="center">BlockedHostName</th>'' +
425 N''<th align="center">BlockingDBName</th>'' +
426 N''<th align="center">BlockingHostName</th>'' +
427 N''<th align="center">BlockedWaitResource</th>'' +
428 N''<th align="center">WaitTime_sec</th>'' +
429 N''<th align="center">BlockedTransactionName</th>'' +
430 N''<th align="center">BlockedSPID</th>'' +
431 N''<th align="center">BlockedProgram</th>'' +
432 N''<th align="center">BlockingSPID</th>'' +
433 N''<th align="center">BlockingProgram</th>'' +
434 N''</tr>'' +
435 ISNULL(CAST ( (
436 select td = '''',
437 td = ISNULL(RecordId,''''),'''',
438 td = ISNULL(BlockedDBName,''**No data available**''),'''',
439 td = ISNULL(BlockedHostName,''**No data available**''),'''',
440 td = ISNULL(BlockingDBName,''**No data available**''),'''',
441 td = ISNULL(BlockingHostName,''**No data available**''),'''',
442 td = ISNULL(BlockedWaitResource,''**No data available**''),'''',
443 td = ISNULL(WaitTime_sec,''''),'''',
444 td = ISNULL(BlockedTransactionName,''**No data available**''),'''',
445 td = ISNULL(BlockedSPID,''''),'''',
446 td = ISNULL(COALESCE(BlockedProgram_jn,BlockedProgram),''**No data available**''),'''',
447 td = ISNULL(BlockingSPID,''''),'''',
448 td = ISNULL(COALESCE(BlockingProgram_jn,BlockingProgram),''**No data available**''),''''
449 from #BlockInfo where BlockingDBName is not null
450
451 FOR XML PATH(''tr''), TYPE
452
453 ) AS NVARCHAR(MAX) ),'''') +
454 N''</FONT>'' +
455 N''</table>'' ;
456 ------------ send email
457declare @subject1 varchar(50)
458set @subject1 = ''Blocked Process Report for ''+@@servername
459EXEC msdb.dbo.sp_send_dbmail
460 @profile_name = ''You db mail profile'', ---- CHANGE HERE !!
461 @recipients=''yourcompanyDBATEAM@company.com'', ---- CHANGE HERE !!
462 @subject = @subject1,
463 @body = @tableHTML,
464 @body_format = ''HTML'' ;
465end
466go
467
468-- drop all temp tables
469if object_id (''tempdb..#BlockEvents'') > 0 drop table #BlockEvents
470if object_id (''tempdb..#dba_job_name'') > 0 drop table #dba_job_name
471if object_id (''tempdb..#BlockInfo'') > 0 drop table #BlockInfo
472
473-- update the BlockingInfo table so that when the job runs it wont send out alert
474update dbo.BlockingInfo
475set Notified = 1 where Notified = 0
476
477',
478 @database_name=N'dbaalert', ---- CHANGE HERE !!
479 @flags=0
480IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
481/****** Object: Step [Trim Records Older than 15 days] Script Date: 03/02/2011 11:47:35 ******/
482EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Trim Records Older than 15 days',
483 @step_id=3,
484 @cmdexec_success_code=0,
485 @on_success_action=1,
486 @on_success_step_id=0,
487 @on_fail_action=2,
488 @on_fail_step_id=0,
489 @retry_attempts=0,
490 @retry_interval=0,
491 @os_run_priority=0, @subsystem=N'TSQL',
492 @command=N'use dbaalert ---- CHANGE HERE !!
493go
494declare @starttime datetime
495-- trim records older than 15 days
496select @starttime = CONVERT(varchar,GETDATE()-15,112)
497--select @starttime
498delete from BlockingInfo where convert(varchar, AlertTime, 112) <= @starttime and Notified =1',
499 @database_name=N'master',
500 @flags=0
501IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
502EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
503IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
504EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
505IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
506COMMIT TRANSACTION
507GOTO EndSave
508QuitWithRollback:
509 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
510EndSave:
511GO
512
513------------ create alert (this is dynamic for default and named instances)
514USE [msdb]
515GO
516-- create an WMI alert to respond to blocking
517IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to Blocking')
518EXEC msdb.dbo.sp_delete_alert @name=N'Respond to Blocking'
519GO
520DECLARE @server_namespace varchar(255)
521IF ISNULL(CHARINDEX('', @@SERVERNAME), 0) > 0
522SET @server_namespace = N'\.rootMicrosoftSqlServerServerEvents' + SUBSTRING(@@SERVERNAME, ISNULL(CHARINDEX('', @@SERVERNAME), 0) + 1, LEN(@@SERVERNAME) - ISNULL(CHARINDEX('/', @@SERVERNAME), 0))
523ELSE
524SET @server_namespace = N'\.rootMicrosoftSqlServerServerEventsMSSQLSERVER'
525EXEC msdb.dbo.sp_add_alert @name=N'Respond to Blocking',
526@wmi_namespace=@server_namespace,
527@wmi_query=N'SELECT * FROM BLOCKED_PROCESS_REPORT',
528@job_name=N'DBA Group - Monitoring - Blocked Process Detector' ---- CHANGE HERE job name!!
529GO