Home / server / Questions / 57985
In Process
Chris Burgess
Asked: 2009-08-25 07:11:38 +0800 CST

SQL Server logging blocks somewhere?

  • 772

Is there a way to identify processes that were blocking or were blocked by another process after the fact without setting something up ahead of time?

My weekly maintenance job (rebuild indexes, update stats) ran extremely long. I'm thinking that it was trying to work on something and encountered blocking to prevent it from continuing, but I don't know what that might be.

Thanks!

sql-server-2005

2 Answers

  1. i checked an overview of the SQL 2005 DMV's and everything seems to indicate that the systems views and functions only gather from real time for transaction information. the default trace can be accessed using fn_trace_gettable, you may find some details there.

    the best way to capture this information is as you suggested, to capture it ahead of time. we actually have a 3rd party tool for exactly this purpose; to capture query information that we can then use to investigate performance problems after the fact.

    • 1

  2. No, you would need to setup some logging yourself into a table so that you can query it after the fact.

    • 0