We have an Enterprise edition SQL server 2005 64-bit instance running on a 64-bit windows 2003 2-node cluster.
We have a homegrown blocking alert that uses a blocking trace to identify processes that have blocking that has lasted over a long period of time (approx. 240 seconds). Periodically throughout the day these blocks occur apparently related reporting services and references an object in ReportServerTempDB that I cannot identify by ObjectID (perhaps a transient temporary data table?).
Our RedGate Response tool reports that the blocking is caused by CheckSessionLock being blocked by WriteLockSession. From what I can tell this is normal behaviour in the Reportsevertempdb that happens when a long running query is refreshing a data snapshot on a report.
I need to determine if this long lasting blocking is normal behaviour and should be excluded from our reporting as noise or is it indicative of a problem with the database server?
If I need to address the issue, how do I determine the actual cause? ie the report that is causing the blocking.