Excessive Blocking in ReportServer and ReportServerTempDB


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

    Thursday, May 14, 2009 10:39 PM

All replies

  • The blocking is normal behavior so it is safe to add a rule to ignore it for your system health checker tool.

    Hope that helps,
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, May 27, 2009 4:15 PM