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 |
Partager