Reporting Services Metadata Cheat Sheet
Is the server busy?
Call stored procedure ListRunningJobs
Alternatively, in windows performance counters on the server, for Object "MSRS 2005 Web Service", counter "Report Requests" shows count of reports currently running (initialed from web site). Object "MSRS 2005 Windows Service", counter "Report Requests" shows reports currently running (initiated from subscriptions).
Show report execution history
This shows the last 100 reports run, along with parameters.
select TOP 100 EL.TimeStart, C.Path, EL.[Parameters], * from dbo.ExecutionLog EL LEFT JOIN [Catalog] C ON C.ItemID = EL.ReportID ORDER BY EL.TimeStart DESC
Show most recently run subscriptions
select top 20 CAT.[Path] as Report, S.[Description] as Subscription, S.[LastStatus], S.[LastRunTime] from [Subscriptions] S inner join [Catalog] CAT on S.[Report_OID] = CAT.[ItemID] inner join [Users] Owner on S.OwnerID = Owner.UserID inner join [Users] Modified on S.ModifiedByID = Modified.UserID left outer join [SecData] SD on CAT.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType left outer join [ActiveSubscriptions] A with (NOLOCK) on S.[SubscriptionID] = A.[SubscriptionID] --order by CAT.[Path], S.[Description] order by S.[LastRunTime] desc
Manually run a subscription
Using the script below, you can find which SQLAgent job is used by a subscription. You can then manually run the SQLAgent job to force the subscription to run.
select R.[Path], S.ScheduleID, CONVERT(varchar(50), JS.next_run_date)+' ' +SUBSTRING(CONVERT(varchar(50), next_run_time+100000000000), 7,6) as NextRun from ReportSchedule S LEFT JOIN [Catalog] R ON R.ItemID = S.ReportID LEFT JOIN MSDB.dbo.sysjobs J ON convert(varchar(250), J.Name) = convert(varchar(250), S.ScheduleID) LEFT JOIN MSDB.dbo.sysjobschedules JS ON J.job_ID = JS.Job_ID Order by [Path]
Once you have found the name of a job, you can execute it.
exec msdb..sp_start_job '1FBFA8E3-FF55-4B1B-9249-2E9E43A02804'
What data sources do my reports use?
select Report.Path AS Report, Report.ModifiedDate, Coalesce(DS.Path,' not set') AS DataSource from Catalog Report LEFT JOIN DataSource DSL ON Report.ItemID = DSL.ItemID LEFT JOIN Catalog DS ON DSL.Link = DS.ItemID where Report.Type = 2 order by Report.Path