{"id":342,"date":"2014-03-11T23:47:16","date_gmt":"2014-03-12T05:47:16","guid":{"rendered":"https:\/\/jackdonnell.com\/?p=342"},"modified":"2016-01-20T16:48:39","modified_gmt":"2016-01-20T22:48:39","slug":"ssrs-active-session-information","status":"publish","type":"post","link":"https:\/\/jackdonnell.com\/?p=342","title":{"rendered":"SSRS Active Session Information"},"content":{"rendered":"

SQL Server Reporting Services<\/strong> (SSRS) showed up to the party in SQL Server\u00a02000<\/a>. \u00a0A really awesome tool for data delivery. \u00a0Most SQL Server environments heavily leverage this powerful tool. \u00a0Microsoft has baked in some excellent views and tables for report rendering information (dbo.ExecutionLog<\/strong>, dbo.ExecutionLog2<\/strong> and dbo.ExecutionLog3<\/strong>). \u00a0SSRS also supplies a text log with additional details for errors and warnings.\u00a0 All are \u00a0useful when trouble shooting a failure or identifying report execution trends, but what happens if you want to know what is running, now?<\/p>\n

Recently, I needed to \u00a0identify an active report that was creating a considerable amount of blocking. \u00a0We were able to identify a reporting login , verify the \u00a0blocking originated from SSRS. I could grab the query being executed, but needed to find the report and user or maybe subscription causing the blocking.\u00a0 As I mentioned, for the most part the ReportServer database gives us historical information.\u00a0 I always based most of my queries on the ReportServer<\/strong> database.\u00a0 In a brief moment of clarity, I realized that maybe just maybe this ReportServerTempDB<\/a> database might just store \u2018temporary\u2019 data about active sessions. Welcome back to the show, Jack!
\n<\/p>\n

ReportingServerTempDB<\/strong>(default name) has a table dbo.SessionsData<\/strong>. I used \u00a0it \u00a0for report path, \u00a0session owner \u00a0and session creation time. I wanted to also include report name , user name and data source name. I did so by dipping back into the ReportServer <\/strong>database dbo.Catalog<\/strong>, dbo.DataSource<\/strong> and dbo.users<\/strong>. \u00a0If the data source had a meaningful name, I could use it to further verify the report was indeed the one connected to the now seriously crippled SQL instance. Previously, we identified the start time of the lead blocker. \u00a0I used it to look for a session that started just before that time. Need to allow for the time a user takes to enter parameters if required.<\/p>\n

It is also a good script to remind that guy in\u00a0accounting\u00a0 not to run the same report 5 times\u00a0all at the same time. Favorite saying, ” a fool with a tool is still a fool”!<\/p>\n

Blah, blah, blah. Enough said! Here is the script:<\/p>\n

\/*\r\nSSRS Find Active Session Information\r\n3\/11\/2014 JCD\r\nUses standard DB names [ReportServer] and [ReportServerTempDB]\r\nTested: SQL 2005, SQL 2008 R2 and SQL 2012\r\n*\/\r\nSET NOCOUNT ON \r\nGO\r\nSELECT \r\n       CONVERT(VARCHAR(20),s.[CreationTime],22) [SessionStart]\r\n\t  ,u.[UserName]\r\n\t  ,c.[name] [Report]\r\n\t  ,d.[name] [DataSource]\r\n      ,s.[ReportPath]\r\n      ,s.[EffectiveParams]\r\n\t  ,DATEDIFF(minute,s.[CreationTime],GETDATE()) [RunningTimeMinutes]\r\nFROM  [ReportServerTempDB].[dbo].[SessionData]  as s with (NOLOCK)\r\nJOIN   [ReportServer].[dbo].[Catalog]  as c with(NOLOCK)\r\n\tON c.path= s.reportPath\r\nJOIN [ReportServer].[dbo].[DataSource]  as d with(NOLOCK)\r\n  ON c.ItemID = d.ItemID\r\nJOIN [ReportServer].[dbo].[Users] as u with(NOLOCK) \r\n\ton u.UserId = s.ownerID\r\nORDER BY s.[CreationTime];\r\nGO<\/pre>\n
<\/h2>\n","protected":false},"excerpt":{"rendered":"
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?<\/p>\n
Read more »<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[273,266,22,51,5],"tags":[303,328,399,396,397,398,327,400,315,402,401,249,35,36,295,326,52,298,291],"class_list":["post-342","post","type-post","status-publish","format-standard","hentry","category-administration","category-dba","category-sql-server","category-ssrs","category-t-sql","tag-dba","tag-dbo-catalog","tag-dbo-datasource","tag-dbo-executionlog","tag-dbo-executionlog2","tag-dbo-executionlog3","tag-dbo-sessiondata","tag-dbo-users","tag-query","tag-reportingserver","tag-reportingservertempdb","tag-script","tag-select","tag-sql","tag-sql-server","tag-sql-server-reporting-services","tag-ssms","tag-ssrs","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/342","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=342"}],"version-history":[{"count":12,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/342\/revisions"}],"predecessor-version":[{"id":459,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/342\/revisions\/459"}],"wp:attachment":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=342"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=342"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=342"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}