blocks & deadlocks

In the production server we are noticing blocks & dead-locks. I have used trace flag 1204 to capture info for deadlock which hapens very rarely.
I am seeing blocks every now & then. Is there any tools/system tables to query to get more info abt blocking & blocked spids. I used info from syslockinfo & sysprocess. dbcc inputbuffer gives info like 'sp_cursorunprepare;1' or 'sp_cursorfetch;1' and puts exclusive locks on bunch of tables. However no activity is seen for that spid.

Any help will be appreciated. I am using SQL Server 2000 with SP2

RachaelTry with sp_who2 and adding (NOLOCK) in your selects.
Csar.

Blocking

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.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.