Diese Prozedur, z.B. von einem von einem SQL Agent Job aufgerufen, überprüft ob blockierende Abfragen vorliegen,
und meldet diese per E-Mail. Hierzu muss dann natürlich Database-Mail dann auch aktiv sein.
Der Job der diese Prozedur aufruft sollte alle 2 Minuten ausgeführt werden.
Eine Blockierung kann kann maximal 4 Minuten unerkannt bleiben.
Hier der notwendige Code:
USE master
GO
IF EXISTS ( SELECT
*
FROM
dbo.sysobjects
WHERE
OBJECTPROPERTY(id, N'IsUserTable') = 1 AND
id = OBJECT_ID(N'dbo.BlockedProcess') )
DROP TABLE dbo.BlockedProcess
GO
-- Create the log table
CREATE TABLE dbo.BlockedProcess
(
BlockedProcessNo_PK INT IDENTITY(1, 1)
NOT NULL
, spid SMALLINT NULL
, blocked SMALLINT NULL
, open_tran SMALLINT NULL
, login_time DATETIME NULL
, last_batch DATETIME NULL
, loginname VARCHAR(20) NULL
, hostname VARCHAR(70) NULL
, secs MONEY NULL
, db VARCHAR(20)
, input_buffer VARCHAR(1000)
, CreateDate DATETIME NOT NULL )
ON [PRIMARY]
GO
ALTER TABLE dbo.BlockedProcess
WITH NOCHECK
ADD
CONSTRAINT BlockedProcess_CreateDate_DF DEFAULT ( GETDATE() ) FOR CreateDate
, CONSTRAINT BlockedProcess_PK PRIMARY KEY CLUSTERED ( BlockedProcessNo_PK )
ON [PRIMARY]
GO
IF EXISTS ( SELECT
*
FROM
sysobjects
WHERE
sysstat & 0xf = 4 AND
id = OBJECT_ID('dbo.sp_CheckForBlock') )
DROP PROCEDURE dbo.sp_CheckForBlock
GO
---- Parameter
---- @dbs2Check = Die zu prüfende Datenbank
---- @LockTimeInSec = Blockirung xx Sekunden
---- @AutoKill 1|0 = Blockierenden Prozess automatisch töten
---- Die Proc muss von einem Job alle 2 Minuten aufgerufen werden, so das ein Blockmaximal 4 Minuten besteht
CREATE PROCEDURE sp_CheckForBlock
(
@dbs2Check VARCHAR(30)
, @LockTimeInSec INT = 120
, @AutoKill BIT = 0 )
AS
DECLARE @spid SMALLINT
DECLARE @hostname VARCHAR(70)
DECLARE @ExecStr VARCHAR(8000)
DECLARE @rc INT
DECLARE @msg VARCHAR(8000)
DECLARE @InputBuffer VARCHAR(1000)
SET NOCOUNT ON
IF EXISTS ( SELECT
blocked
FROM
master.dbo.sysprocesses
WHERE
dbid = DB_ID(@dbs2Check) AND
blocked 0 )
BEGIN -- Hurra, wir haben einen Block...
-- get the blocking spid
SELECT
@spid = spid
, @hostname = hostname
FROM
master.dbo.sysprocesses
WHERE
blocked = 0 AND
( CONVERT(MONEY, GETDATE() - last_batch) * 86400.0 ) = @LockTimeInSec AND
spid IN ( SELECT DISTINCT
blocked
FROM
master.dbo.sysprocesses
WHERE
dbid = DB_ID(@dbs2Check) AND
blocked 0 )
IF @spid IS NOT NULL
BEGIN
DECLARE @ProcInfo TABLE
(
EventType VARCHAR(30)
, PARAMETERS INT
, EventInfo VARCHAR(255) )
---INPUTBUFFER Zeigt die letzte Anweisung an, die von einem Client an eine Instanz von MicrosoftSQL Server gesendet wurde
SET @ExecStr = 'DBCC INPUTBUFFER(' +
CONVERT(VARCHAR, @spid) +
') WITH NO_INFOMSGS'
INSERT INTO
@ProcInfo
EXEC (
@ExecStr
)
SELECT
@InputBuffer = ISNULL('Type: ' + EventType +
', ', '') +
ISNULL('Param: ' +
CONVERT(VARCHAR, PARAMETERS) + ', ',
'') + ISNULL('Buffer: ' + EventInfo,
'')
FROM
@ProcInfo
DROP TABLE #ProcInfo
-- Alle betroffenen Prozesse, die vom Lock betroffen sind listen
INSERT INTO
BlockedProcess
(
spid
, blocked
, open_tran
, login_time
, last_batch
, loginname
, hostname
, secs
, db
, input_buffer )
SELECT
spid
, blocked
, open_tran
, login_time
, last_batch
, CONVERT(VARCHAR(20), loginame)
, CONVERT(VARCHAR(70), hostname)
, CONVERT(MONEY, GETDATE() -
last_batch) * 86400.0 --(4 Minuten)
, CONVERT(VARCHAR(20), DB_NAME(dbid))
, CASE WHEN spid = @spid
THEN @InputBuffer
ELSE NULL
END
FROM
master.dbo.sysprocesses
WHERE
(
dbid = DB_ID(@dbs2Check) AND
blocked 0
--AND program_name IN('')
) OR
spid = @spid
-- Send Mail
SET @msg = ''
SELECT
@msg = @msg + 'SPID: ' +
CONVERT(VARCHAR, spid) + CHAR(13) +
CHAR(10) + 'Blocked: ' +
CONVERT(VARCHAR, blocked) + CHAR(13) +
CHAR(10) + 'Open Trans: ' +
CONVERT(VARCHAR, open_tran) + CHAR(13) +
CHAR(10) + 'Login: ' +
CONVERT(VARCHAR, login_time, 121) +
CHAR(13) + CHAR(10) + 'Last Batch: ' +
CONVERT(VARCHAR, last_batch, 121) +
CHAR(13) + CHAR(10) + 'User: ' +
CONVERT(VARCHAR, loginame) + CHAR(13) +
CHAR(10) + 'Machine: ' +
CONVERT(VARCHAR, hostname) + CHAR(13) +
CHAR(10) + 'Seconds: ' +
CONVERT(VARCHAR, CONVERT(MONEY, GETDATE() -
last_batch) * 86400.0) + CHAR(13) +
CHAR(10) +
CASE WHEN spid = @spid
THEN ISNULL('Blocker CMD: ' +
@InputBuffer, '')
ELSE ''
END + CHAR(13) + CHAR(10) + CHAR(13) +
CHAR(10)
FROM
master.dbo.sysprocesses
WHERE
(
dbid = DB_ID(@dbs2Check) AND
blocked 0
) OR
spid = @spid
DECLARE @subject VARCHAR(255)= 'Blocked process! ' +
@@SERVERNAME + 'Database ' + @dbs2Check
--- globales Profil verwenden (muss am Server eingerichtet sein)
--- falls nein, dann bei @profile_name entsprechend angeben
EXEC msdb.dbo.sp_send_dbmail
---@profile_name = '',
@recipients = 'youremail.com',
@body = @msg, @subject = @subject ;
-- kill process
IF @AutoKill != 0
BEGIN
SET @ExecStr = 'KILL ' +
CONVERT(VARCHAR, @spid)
EXEC (
@ExecStr
)
END
END -- @spid IS NOT NULL
END -- Is Block
SET NOCOUNT OFF
GO