Some scripts I found useful for when playing with reporting services.

 

Any queries below run in the ReportServer database.

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