Lock/Block Monitoring with Alerting

Over the past few days, I have been working on creating a mechanism in CMS whereby it will alert us when a lock has been blocking another process for a given period.

The scenario that we are trying to alert on is when a SPID is a lead blocker on other Child SPIDs which would lead to a connection from one user timing out in the DB Application.

After a lot of testing, I have put the code to do this into DB1 as a Scheduled Job which will run every 3 minutes.

Breakdown of what this code does:

  • Finds Parent Locks
  • Queries to find out whether Parent lock is the blocker of another SPID – not itself (Sometimes it is ok to have a lock/block as SPIDs can sometime spawn processes to do part of the work)
    • If no locks found then Deletes everything from the Blocking_Log table
  • If blocks found which have been held for more than 5 minutes then send email
    • If Lock details already exist in Blocking_Log table AND timestamp of last alert is less than 1 hour ago then do not send email
    • If it is a new block then add to Blocking_Log
  • Send Email with details of locking, SQL Buffer of the Spids involved, and Pages/objects that these SPIDs are locking/blocking.

Usage

1. Create the logging table in the DB_Monitoring Database [Blocking_Log_Table.txt]
2. Create the Agent Job in MSDB [Lock_Job.txt]
3. Amend Step 2 of the Lock_Monitoring Job to contain the correct DBMail Profile for the particular server 4. Create the Directory E:\Backup\Lock_Monitoring

Blocking_Log_Table.txt

/****** Object:  Table [dbo].[Blocking_Log]    Script Date: 10/05/2009 17:28:37 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Blocking_Log](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [SPID] [int] NOT NULL,
      [Host_Name] [nvarchar](50) NOT NULL,
      [Program_Name] [nvarchar](75) NOT NULL,
      [Timestamp] [datetime] NOT NULL
) ON [PRIMARY]

Lock_Job.txt

USE [msdb]
GO

/****** Object:  Job [Lock Monitoring]    Script Date: 10/06/2009 13:47:54 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 10/06/2009 13:47:54 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Lock Monitoring', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Monitor locks]    Script Date: 10/06/2009 13:47:54 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Monitor locks', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'SET NOCOUNT ON 
DECLARE @Handle varbinary(64);
DECLARE @SPID INT;
DECLARE @Subj varchar(75);
DECLARE @waittime INT;
declare @SpidStatus varchar(100),@BlockerSpid int, @BlockedBy int,@DBname sysname,@CTEXT VARCHAR(8000)
SET @waittime = 300000

DECLARE [Blocked_Spids] CURSOR FOR 

select case when blocked != 0 then ''Not Lead Blocked''
when blocked = 0 then ''Lead Blocker''
Else ''Unknown''
End [Block],
spid,blocked, db_name(dbid) dbname from master..sysprocesses
where (blocked = 0 and spid in (select blocked from master..sysprocesses)) or blocked != 0 and waittime > @waittime  order by blocked asc, waittime desc
FOR READ ONLY

OPEN [Blocked_Spids]
WHILE 1 = 1
BEGIN
FETCH NEXT FROM [Blocked_Spids] INTO @SpidStatus, @BlockerSpid,@BlockedBy,@DBname
IF @@fetch_status <> 0 BREAK

SELECT RTRIM(LTRIM(STR(@BlockerSpid))) + '' ''+ @SpidStatus +'' In Database ''+@DBname + '' Is running following T-SQL and is Blocked by ''+ ltrim(STR(@BlockedBy))

SELECT ''SPID '' + convert(varchar(4),@BlockerSpid) + '' Logged in on '' + convert(varchar(10),Login_time,103) + '' @ '' + convert(varchar(8),Login_time,108) + '' from '' + [host_name] + '' using '' + [program_name] from sys.dm_exec_sessions
where session_id = @blockerspid

SELECT @Handle = sql_handle 
FROM master..sysprocesses 
WHERE spid = @BlockerSpid 

SELECT @CTEXT=text FROM ::fn_get_sql(@Handle);

IF @CTEXT IS NULL OR LEN(LTRIM(RTRIM(@CTEXT)))=0
BEGIN
SELECT ''DBCC INPUTBUFFER (''+LTRIM(STR(@BlockerSpid)) +'')''
DBCC INPUTBUFFER(@BlockerSpid)
END
ELSE 
SELECT @CTEXT
SELECT ''LOCK STATUS ON SPID ''+ STR(@BlockerSpid) 
EXECUTE sp_lock @BlockerSpid
END

deallocate [Blocked_Spids]

DECLARE @nolocks INT;

select @nolocks = count(*) 
from master..sysprocesses
where (blocked = 0 and spid in (select blocked from master..sysprocesses))

if @nolocks = 0
BEGIN
delete from DB_Monitoring..Blocking_log
END

', 
        @database_name=N'master', 
        @output_file_name=N'E:\backup\Lock_Monitoring\Locks_Trace.log', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Send Mail]    Script Date: 10/06/2009 13:47:54 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Send Mail', 
        @step_id=2, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'DECLARE @subj varchar (100)
DECLARE @leadspid INT;
DECLARE @blockcount INT;
DECLARE @AlreadyAlerted INT;
DECLARE @waittime INT;
DECLARE @dbname varchar (25);
DECLARE @program varchar (75);
DECLARE @host varchar (25);

SET @waittime = 300000

select @blockcount = count(*)  
from master..sysprocesses
where blocked != 0 and waittime > @waittime 

select @leadspid = spid,
@dbname = db_name(dbid) 
from master..sysprocesses
where (blocked = 0 and spid in (select blocked from master..sysprocesses))

select @host = host_name, @Program = Program_Name from sys.dm_exec_sessions
where session_id = @leadspid

select @AlreadyAlerted = count(*) from DB_Monitoring..Blocking_Log
where SPID = @leadspid and Host_name = @host and Program_Name = @Program
and Timestamp > dateadd(hour, -1, GETDATE())

if @AlreadyAlerted = 0 and @blockcount > 0 
BEGIN
Insert into DB_Monitoring..Blocking_log (SPID,Host_Name,Program_Name,Timestamp) values (@leadspid, @host, @program, getdate())



select @subj =  ''Blocking Alert: '' + @@servername + '' on Database '' + @dbname + '' (Lead Blocker SPID '' + convert(varchar(4), @leadspid) + '')''


EXEC msdb.dbo.sp_send_dbmail 
    @profile_name = ''Mail_Profile'',
    @recipients = ''abc@company.co.uk'',
    @file_attachments = ''E:\Backup\Lock_Monitoring\locks_trace.log'',
    @subject = @subj,
    @body = ''See attached file for Lock Information'',
    @body_format = ''TEXT''

END', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'continiuos', 
        @enabled=1, 
        @freq_type=4, 
        @freq_interval=1, 
        @freq_subday_type=4, 
        @freq_subday_interval=3, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=0, 
        @active_start_date=20090930, 
        @active_end_date=99991231, 
        @active_start_time=0, 
        @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO


Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: