1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
|
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_CheckBlockingProcesses] Script Date: 11/04/2008 11:05:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_CheckBlockingProcesses] AS
DECLARE @BlockedProcesses int
SELECT @BlockedProcesses = count(*)
FROM master..sysprocesses with(nolock)
WHERE (blocked!=0)
AND (waittime > 30000)
IF ( @BlockedProcesses>0 ) BEGIN
declare @CrLf char(2)
set @CrLf=char(13)+char(13)
declare @MailRecipients varchar(250)
set @MailRecipients = ''
declare @Server varchar(250)
IF PATINDEX('\', @@SERVERNAME)>0 BEGIN
set @Server = Left(@@SERVERNAME, PATINDEX('\', @@SERVERNAME))
END ELSE BEGIN
set @Server = @@SERVERNAME
END
declare @MailSubject varchar(250)
set @MailSubject = 'Blocking Processes Report on ' + @@SERVERNAME
declare @MailAttach varchar(250)
set @MailAttach = '"\\' + @Server + '\BlockingProcessesReports\'
+ @Server + '.'
+ convert(varchar, getdate(), 102) + '.'
+ right('00'+ltrim(str(datepart(hh,getdate()))),2) + '.'
+ right('00'+ltrim(str(datepart(mi,getdate()))),2) + '.'
+ right('00'+ltrim(str(datepart(ss,getdate()))),2)
+ '.txt"'
DECLARE @TextValue TABLE (
LineNumber int Identity,
TextLine varchar(1024)
)
INSERT INTO @TextValue (TextLine)
SELECT distinct
'User ' + rtrim(p.loginame)
+ ' on computer ' + rtrim(p.hostname)
+ ' running process ' + convert(varchar, p.spid)
+ ' is blocked by user ' + rtrim(p2.loginame)
+ ' on computer ' + rtrim(p2.hostname)
+ ' running process ' + convert(varchar, p.blocked)
+ ' since ' + convert(varchar,p.waittime/1000) + ' seconds.' + @CrLf
+ ' ==> Blocked resource are : Program = [' + rtrim(p.program_name) + '],'
+ ' Database = [' + rtrim(db.name) + '],'
+ ' Command = [' + rtrim(p.cmd) + '].'
FROM master..sysprocesses p WITH(NOLOCK)
INNER JOIN master..sysdatabases db WITH(NOLOCK) ON p.dbid = db.dbid
INNER JOIN master..sysprocesses p2 WITH(NOLOCK) ON p2.spid = p.blocked
WHERE (p.blocked!=0)
AND (p.waittime > 30000)
INSERT INTO @TextValue (TextLine)
select distinct 'First blocking process : ' + convert(varchar,p.spid) + @CrLf
from master..sysprocesses p with(nolock)
where p.blocked = 0 and p.spid in (select blocked from master..sysprocesses with(nolock) where spid != 0)
DECLARE Results CURSOR FOR
SELECT TextLine FROM @TextValue ORDER BY LineNumber
DECLARE @TextRow varchar(1024)
declare @MailBody varchar(2500)
set @MailBody = '=== Blocking report ( >30s )===' + @CrLf + 'Please consult details at ' + @MailAttach + @CrLf
OPEN Results
FETCH Results INTO @TextRow
WHILE @@FETCH_STATUS=0 BEGIN
set @MailBody = @MailBody + @CrLf + @TextRow
FETCH Results INTO @TextRow
END
CLOSE Results
DEALLOCATE Results
set @MailBody = @MailBody + @CrLf + '=== End of report ==='
declare @ShellCommand varchar(1024)
set @ShellCommand = 'isql -E -S ' + @@SERVERNAME + ' -w 3000 -o ' + @MailAttach + ' -Q "exec sp_blocker_pss80"'
-- Trigger Event to warn via pager
exec master..sp_user_counter1 @blockedprocesses
exec master..xp_cmdshell @ShellCommand
exec msdb.dbo.sp_send_dbmail @profile_name = ''
, @recipients=@MailRecipients
, @Body=@MailBody
, @subject=@MailSubject
END ELSE BEGIN
exec master..sp_user_counter1 0
END |
