83512 Wasserburg,Herrengasse 5 0152 53 55 26 40 Janke@innsql.de Mo-Fr. 09:00-16:00

Deadlocks ermitteln


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:

Show/Hidden sql code

View source
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

(0 Votes)

Uwe Janke

Contact Info

Addresse:
Herrengasse 5, 83512 Wasserburg am Inn

Ruf:
0152-53 55 26 40

Email:
Janke@innsql.de

Wir nutzen Cookies auf unserer Website. Einige von ihnen sind essenziell für den Betrieb der Seite, während andere uns helfen, diese Website und die Nutzererfahrung zu verbessern (Tracking Cookies). Sie können selbst entscheiden, ob Sie die Cookies zulassen möchten. Bitte beachten Sie, dass bei einer Ablehnung womöglich nicht mehr alle Funktionalitäten der Seite zur Verfügung stehen.