Alert for Blocked Process?

I am troubleshooting a database from a 3rd party vendor, and we keep coming
up with blocked processes. Is there any way to set up an alert that could no
tify me when a process is blocked?"Blocking" as such is not a bad thing. It supports ACID and is necessary
for data integrity. The real problem is when the blocking seems to be going
on for a considerable amount of time. That amount of time is something you
could define. Ideally, any process or client connection accessing SQL
Server is given a 'spid'. The spid information is got from the master
database, Sysprocesses table. That has columns as well for the current
status, waittime and waittype. So perhaps, you could set up an alert if
say, for a particular spid, it's status is sleeping and the waittime is
past a certain time that you define.
Hope that helps!
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.