Originally posted by ToddBritt
Can a Performance Alert be set up to detect blocking on a server? We do not get deadlocks but we do get a bit of blocking going on and it
would be nice to be notified when blocking is occurring.
You can acheive this by using a combination of Scheduled Tasks, stored procedures and Alerts.
Write a stored procedure that generates a user-defined error message whenever there is blocking. Blocking can be checked by querying the sysprocesses system table.
Define a scheduled task that runs this stored procedure for every n minutes.
Define an Alert on the specific error message and configure it to send a mail.