Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

In SQL Server, I am using the following command to see blocked sessions: 

select * from sys.sysprocesses where blocked>0

This is super. The problem is I need to see what block sessions there were in the last 30 minutes. This isn't easy because, the blocked sessions can be come unblocked.can go a so they won't be returned by :

select * from sys.sysprocesses where blocked>0

Any ideas?

Thanks

share|improve this question
You know you'll always have transient, short duration blocks? And SPIDs can be re-used? So you can't track a block on the SPID simply (you can look at last_batch, status etc of course). So: what problem are you trying to solve...? – gbn Mar 8 '12 at 15:59
I want to see where there has been contention in the last 30 minutes – dublintech Mar 8 '12 at 16:12

1 Answer

up vote 1 down vote

Adam Machanic wrote the everuseful sp_whoisactive that can handle the task. A description of the procedure can be found at SP_WhoIsActive: Get detailed information about the sessions running on your SQL Server system

share|improve this answer
Nice tool. But looks like it only does current sessions? – dublintech Mar 8 '12 at 16:13
@dublintech if you want to keep historical information you need to log historical information or run a trace – JNK♦ Mar 8 '12 at 16:59
@JNK thank. Can you suggest the trace, I need? Sorry I am not a DBA. – dublintech Mar 8 '12 at 18:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.