SQL Server Reporting Services (SSRS) showed up to the party in SQL Server 2000.  A really awesome tool for data delivery.  Most SQL Server environments heavily leverage this powerful tool.  Microsoft has baked in some excellent views and tables for report rendering information (dbo.ExecutionLog, dbo.ExecutionLog2 and dbo.ExecutionLog3).  SSRS also supplies a text log with additional details for errors and warnings.  All are  useful when trouble shooting a failure or identifying report execution trends, but what happens if you want to know what is running, now?

Recently, I needed to  identify an active report that was creating a considerable amount of blocking.  We were able to identify a reporting login , verify the  blocking originated from SSRS. I could grab the query being executed, but needed to find the report and user or maybe subscription causing the blocking.  As I mentioned, for the most part the ReportServer database gives us historical information.  I always based most of my queries on the ReportServer database.  In a brief moment of clarity, I realized that maybe just maybe this ReportServerTempDB database might just store ‘temporary’ data about active sessions. Welcome back to the show, Jack!
Read more »