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