Take the 2-minute tour ×
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.

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
add comment

3 Answers 3

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
    
add comment

Since you are having blocks in db server ,one strategy I would suggest is storing the results of your query to a table select * into copy_sysprocess from sys.sysprocesses where blocked>0 and this should be stored whenever a block occurs for that you should set an alert in the sql server agent which inturn runs this query which stores the contents to the table.So now you need to just query this copy_sysprocess table to find the details of blocked process

share|improve this answer
    
While technically correct, this answer relies on an outdated approach. This is a SQL Server 2008 question and there are far better ways to obtain this information - the answer Remus shares above is an excellent approach. Even in SQL Server 2000, you want more details/data then just querying sysprocesses. –  Mike Walsh Nov 17 '13 at 18:48
    
@MikeWalsh ,that's right a lot of dmv's give you great information regarding the blocking sessions,since the user needs the data which is prior to 30 minutes the only way to get that is to capture the info and store it into a table.we uses some techniques developed by this mvp(german I think)deadlock detection –  Biju jose Nov 18 '13 at 2:58
    
Correct - Which is what Remus' answer can do with logging sp_whoisactive to a table. Your approach is also an approach that will log the requested data for sure. Good suggestion. –  Mike Walsh Nov 18 '13 at 2:59
add comment

Are you familiar with the Blocked Process Report? It's an xml report on blocking which you can configure and then collect with a trace. As blocking is fairly normal in a database, you configure the threshold in seconds eg you might think processing blocking for 5 seconds is ok, and only collect processes blocked for over 30 seconds, eg

EXEC sp_configure 'blocked process threshold', 30
RECONFIGURE

Then configure the trace to collect the report. The event is located in the 'Errors and Warnings' section of the Profiler Events:

Profiler Blocked Process Event

Consider a server-side trace rather than a Profiler GUI trace to reduce the overhead. Save the results to a table and then interrogate them using XQuery. Post back if you need any further help with this.

If you are finding blocking excessive in your database, you might consider the snapshot isolation levels, although there is some overhead to these.

share|improve this answer
add comment

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.