Detect locking/blocking in SQL and send email alerts

Published on
6,057 Points
2 Endorsements
Last Modified:
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts including details about the processes involved.

Without monitoring/alerting on certain events you can't be sure what happened in your SQL Server until next time when you go in and eventually find the locks/blocks in your system via SQL's own Performance Dashboard Reports or sp_lock.

The following article will show you how to install and configure a SQL job that will send you email alerts including details about the processes involved in locking/blocking.

The code attached below will create one monitoring working table in master database where locking/blocking details are stored, a stored procedure that does the check and a SQL job (installed disabled) that will execute the stored procedure and send email alerts IF there are certain number of SPID's locking/blocking each other. 

You will need to have Email enabled and configured on your SQL server, and one Operator created. You will need your actual email profile to replace "SQL Mail Profile" and real operator name to replace "SQL_DBAs" strings in  attached code and also a domain\username SQL sysadmin account to replace "Domain\UserName" in the attached code.  A  code sample to create locking/blocking scenario for testing purposes is also enclosed as a comment at the top of the code along with many more in the body. The code as is will check and send an alert every two minutes while locking/blocking occurs and in order to modify its frequency you can adjust the SQL job schedule but you will have to change the "waitfor delay '00:02:00';" inside the stored procedure - just not too often so you don't get flooded with emails.

Please set it up first in a test environment, and note that you could use various methods  (Powershell for instance)  to deploy it to multiple servers assuming the "prerequisites" are met - domain\username SQL sysadmin account, SQL Mail configured with server specific email profile and operator.


to test...

use msdb

select * into msdb.dbo.dba_jobs_status from msdb.dbo.sysjobhistory;

--run this in one SPID
begin tran
select * from msdb.dbo.dba_jobs_status
delete from msdb.dbo.dba_jobs_status

--then this in another SPID
truncate table msdb.dbo.dba_jobs_status

-- cleanup - drop test table created above
drop table msdb.dbo.dba_jobs_status;

USE [master]

/****** Object:  Table [dbo].[dba_blockinfo]    Script Date: 03/04/2015 10:20:55 ******/



CREATE TABLE [dbo].[dba_blockinfo](
	[lock_type] [nvarchar](120) NULL,
	[database_id] [nvarchar](150) NULL,
	[blk_object] [bigint] NULL,
	[lock_req] [nvarchar](120) NULL,
	[wait_sid] [bigint] NULL,
	[wait_time] [sysname] NOT NULL,
	[wait_type] [nvarchar](60) NULL,
	[wait_batch] [varchar](max) NULL,
	[wait_stmt] [varchar](max) NULL,
	[block_stmt] [varchar](max) NULL,
	[blocker_sid] [bigint] NULL


USE [master]
/****** Object:  StoredProcedure [dbo].[dba_AlertBlocksWarning_csv]    Script Date: 03/10/2015 13:05:53 ******/

-- =============================================
-- Author:		lcohan
-- Create date: 2011-12-16
-- Description:	Used to alert if severe Blocking/Locking occured.
-- Used By:     SQL Server scheduled job
-- Parameters:  
--				@profile_name_in = 'SQL Mail Profile', 
--				@recipients_in = 'recipient_mail@mail.com', 
--				@servername_in = 'SQL Server Cluster NNN'
-- Outputs:     email alert sent to the DBA as ATTACHMENT rather than HTML body
-- Execute:     exec [dbo].[dba_AlertBlocksWarning_csv]  @profile_name_in = 'SQL Mail Profile', @recipients_in = 'lcohan@mail.com', @servername_in = 'MySuper Cluster'; 
-- =============================================
CREATE PROCEDURE [dbo].[dba_AlertBlocksWarning_csv] 
	@profile_name_in nvarchar(100),
	@recipients_in nvarchar(500),
	@servername_in nvarchar(50) 


--send detailed blocking info if exsists
truncate table master..dba_blockinfo;

insert into master..dba_blockinfo
select t1.resource_type							as lock_type
	,db_name(resource_database_id)				as database_id
	,t1.resource_associated_entity_id			as blk_object
	,t1.request_mode							as lock_req	 -- lock requested
	,t1.request_session_id						as wait_sid  -- spid of waiter
	,t2.wait_duration_ms						as wait_time
	,t2.wait_type								as wait_type		
	,(select text from sys.dm_exec_requests	r  --- get sql for waiter
		cross apply sys.dm_exec_sql_text(r.sql_handle) 
		where r.session_id = t1.request_session_id) as wait_batch
	,(select substring(qt.text,r.statement_start_offset/2, 
			(case when r.statement_end_offset = -1 
			then len(convert(nvarchar(max), qt.text)) * 2 
			else r.statement_end_offset end - r.statement_start_offset)/2) 
		from sys.dm_exec_requests r
		cross apply sys.dm_exec_sql_text(r.sql_handle) qt
		where r.session_id = t1.request_session_id) as wait_stmt    --- this is the statement executing right now
	,(select text from sys.sysprocesses p		--- get sql for blocker
		cross apply sys.dm_exec_sql_text(p.sql_handle) 
		where p.spid = t2.blocking_session_id) as block_stmt
	,t2.blocking_session_id as blocker_sid -- spid of blocker
	sys.dm_tran_locks t1, 
	sys.dm_os_waiting_tasks t2
	t1.lock_owner_address = t2.resource_address

DECLARE @title nvarchar(500)
SET @title = 'Blocking occured on '+@@SERVERNAME+'. There are: '+cast((select count(*) from master..dba_blockinfo) as sysname) + ' blocked processes at: '+cast(getdate() as sysname)

declare @sql_query nvarchar(1000);
--set @sql_query = N'set nocount on; SELECT * FROM master.dbo.dba_blockinfo WHERE datalength(database_id) > 0;'
set @sql_query = N'set nocount on; 
SELECT  cast(left(ltrim(database_id),50) as nvarchar) as database_id, 
blocker_sid as blocker_SPID,
wait_sid as blocked_SPID,
cast(left(ltrim(lock_type),50) as nvarchar) as lock_type,
cast(left(ltrim(lock_req),50) as nvarchar) as lock_req,
cast(left(ltrim(wait_time),50) as nvarchar) as wait_time,
cast(left(ltrim(wait_type),50) as nvarchar) as wait_type, 
ltrim(replace(wait_batch,char(13),'' '')) as blocked_batch,
ltrim(replace(block_stmt,char(13),'' '')) as blocker_stmnt
FROM master.dbo.dba_blockinfo WHERE datalength(database_id) > 0;';

IF (SELECT COUNT(*) FROM master..dba_blockinfo WHERE datalength(database_id) > 0) > 3
	EXECUTE AS LOGIN = 'Domain\UserName'; -- must be sysadmin
	EXECUTE msdb.dbo.sp_send_dbmail 
		@profile_name = @profile_name_in,
        @subject = @title,  
		@body = 'Please see attchment for more details',    
		@body_format = 'Text',
		@query = @sql_query,
		@execute_query_database = 'master',
		@query_result_header = 1,
		@attach_query_result_as_file = 1,
		@query_result_separator = '	',
		@query_result_no_padding = 1, 
		@query_attachment_filename = 'BlockingDetails.csv';    

IF EXISTS(SELECT * FROM master..dba_blockinfo WHERE datalength(database_id) > 0)
	--wait 2 minutes until next check...
	waitfor delay '00:02:00';


USE [msdb]

SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Alert' AND category_class=1)
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Alert'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Alert DBA - Blocking occured', 
		@description=N'No description available.', 
		@notify_email_operator_name=N'SQL_DBAs', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'send email alert', 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'EXECUTE AS LOGIN = ''Domain\UserName'';
exec [master].[dbo].[dba_AlertBlocksWarning_csv]  @profile_name_in = ''SQL Mail Profile'', @recipients_in = ''lcohan@mail.com'', @servername_in = ''MySuper Cluster''; 

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'every 10 seconds with 1 m inute delay', 
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
GOTO EndSave


The code should work fine on SQL 2008 and up to the current version.

Please feel free to adjust the code, make any suggestions or comments.


  • 4
  • 3
LVL 11

Expert Comment

by:Robb Hill
How would we add multiple email recipients here.

Also probably should make all the db and stored procs check for exists or not and then do  the create.
LVL 40

Author Comment

To add multiple recipients one option is to have a Exchange (or other email) Group and add that group to the distribution list. This is so you don't need to make the @recipients_in list "custom" to each server. If you want you can also add them separated by ";" like in any mailing list so you would pass a parameter like:

@recipients_in = 'lcohan@mail.com;SomoneElse@NewMail.com;OneMore@SomeMail.com' --ETC...

Indeed that IF EXISTS and CREATE instead of ALTER could be added to improve the script however one thing I hate to do is to drop custom/client objects by accident due to the name coincidence - vary small chance indeed but happened to me so I leave the IF EXISTS...DROP...part for the user and I changed the ALTER PROCEDURE to CREATE PROCEDURE for same reason just explained.
LVL 11

Expert Comment

by:Robb Hill
This is good.

So I have an idea for a tweek.  Many times you might get a block..that will resolve itself.  This job runs every 10 seconds...which is great.
It would be nice it it detected the block in 10 seconds(variable)....then checked it again in 30 seconds (variable)

Then email the block.  Many times I use this ..by the time I log in to the server the block resolved itself.

This scripts behavior is check for blocks every 10 seconds.

Then it will email on the first find..
Then it will wait 2 minutes(variable) before emailing any other found blocks.

The second part about how long to wait to email again is fine.

But based on how I use it ...it would be nice to give the first email a reasonable amount of time to determine if its a block that will remain before I alert everyone in my helpdesk.

Please let me know if you have any ideas on how to refactor to that method.
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

LVL 11

Expert Comment

by:Robb Hill
How could we take this and modify as follows.

the blocks are determined....but then after the timer...that would normally come back in x amount of minutes to email again...instead of emailing that the block was still there ..would kill the blocking spid...and perhaps we could be so bold as to only kill the spid if we saw that it was sleeping...or we new the blocking spid of the blocking process contained certain sql text?

Any ideas on how we could ramp this up to do that?
LVL 40

Author Comment

@Robb Hill -  sorry for late reply, just very busy lately..anyway, I would not modify the script I have for general usage as I believe it serves the purpose of notifying DBA's or sysadmins about locking/blocking occurring in their database servers however please see attached  script that you could adapt and use to serve your purpose. This script will kill the root blocker and the threshold is set at "@blocked_time > 120000" in the code where that value is in milliseconds so if root blocker is still blocking other processes after ~2 minutes it gets killed. I have it set on some older legacy 3rd party systems running SQL 2008 and 2008 R2 as a SQL job executed every 1 minute.

HTH and...please,please...use it cautiously and test it first in an environment where no damages/harm is done IF some SQL SPID gets killed.
Obviously with note above you or anyone else using it AS IS in a production environment and causing issues is sole responsible for the damages done.
LVL 11

Expert Comment

by:Robb Hill
One other thing that would make the initial email notification much more useful.

It currently only shows the blocked SQL versus the SQL that is blocking.

Any ideas on how to reformat the output to show both?

Expert Comment

by:John Perry
Thank for the short tutorial, but it seems it's much easier to use some third party tool, which will be backed up your databases and send email notifications. I can recommend you to try SQLBackupAndFTP https://sqlbackupandftp.com or SqlBak https://sqlbak.com By the way, they both have a Free plan. Also, SqlBak has "Health Checks" option, you can find more details at https://sqlbak.com/blog/why-do-you-need-sql-server-automatic-health-check/
LVL 40

Author Comment

Adjusted the output to be in the form like below in order to quickly identify the blocker SPID/statement besides the blocked process.

database_id      blocker_SPID       blocked_SPID      blk_object      lock_type      lock_req            wait_time      wait_type      blocked_batch      blocker_stmnt

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Join & Write a Comment

This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month