Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In some cases, sessions are blocked in databases. There are many reasons for blocking. In this article, I will not go into details of this topic.
In this article, I will explain how to send mail when the blocked session count in a database exceeds a certain number. Because if we do not know about this situation, the application will be locked and will not work. So we need to create a warning mechanism to intervene before the application becomes inaccessible.
We have written a script for this process as follows. I want to briefly explain what is happening in this script.
It checks the blocked session count in the instance you are working in every minute. If the blocked session count is greater than the number we have specified, it sends it to the mail address specified in the script by using the Database Mail profile specified in the script. If you did not configure database mail before, you may want to read the article named “How To Configure Database Mail On SQL Server”
Script To Send Email When the Blocked Session Count Exceeds a Certain Number
You need to change the following parameters for the Script to work properly.
@your_email_address ==> The e-mail address to which you would like to send an email about Lock
@lockcount ==> If the blocked session count exceeds this value, an email will be sent to the above email address. (default value is 100)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 | USE [msdb] GO BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 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 alert', @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 EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'lock_script', @step_id=1, @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 @lockcount int, @msj nvarchar(max) = (select @@SERVERNAME+'' Blocked session count exceeds the specified value. Check the instance!''), @profle nvarchar(max) =(select name from msdb.dbo.sysmail_profile) set @lockcount=( select count(t1.resource_type) from sys.dm_tran_locks t1, sys.dm_os_waiting_tasks t2 where t1.lock_owner_address = t2.resource_address) if @lockcount>100 --Blocked Session Count begin EXEC msdb.dbo.sp_send_dbmail @profile_name =@profle, --Profile Name @recipients = @your_email_address, @subject = ''Lock Alert'', --Subject Title @body=@msj --Mail Content 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_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 |
