Thursday, 8 June 2017

Setting a Deadlock Alert in SQL Server

Setting a Deadlock Alert in SQL Server

To set a deadlock alert in SQL Server  is quite easy. You have to follow some simple steps and you can get the things automated.

  1. To Set up a Deadlock Alert, Go to-> SSMS-> SQL Server Agent->Alerts

Setting Deadlock Alert in SQL Server via SSMS

2)  From Alerts, Click on new alert and specify the name of the Alert-For eg. We will name the Alert as “Deadlock Alert”.

Deadlock Alert in SQL Server

3) In the Type box -> Select -SQL Server performance condition alert.

4) Move on to next items then; In Object specify-> Locks, for Counter select->Number of Deadlocks/sec, select “_total” in Instance and specify rises above  value 1 in Alert if counter. You can set these values as per your requirements. Please refer the below screenshot for further details-

How to set a Deadlock Alert in SQL

5) Click on the next tab which is Response. Here you should specify if you want to execute a SQL job in case a deadlock occurs or you want to directly notify the Operators. If you already have a deadlock job that has all the granular details about the deadlock processes then you can directly invoke that job from here else click Notify Operators. Here to keep it simple, we will be selecting Notify Operators-

Deadlock Alert in SQL

6) In the Options tab, you can choose if you wish to include alert text in email, pager or net send address. Click OK, once all the details have been entered.

That’s it and you will be receiving deadlock alert in SQL Server, next time a deadlock occurs in your System.


Post a comment