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.
-
To Set up a Deadlock Alert, Go to-> SSMS-> SQL Server Agent->Alerts
2) From Alerts, Click on new alert and specify the name of the Alert-For eg. We will name the Alert as “Deadlock Alert”.
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-
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-
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.
To Set up a Deadlock Alert, Go to-> SSMS-> SQL Server Agent->Alerts
