September 6, 2011 at 1:52 pm
Is there a query I can run on the ReportServer database that will give reports currently running on the Report Server?
It looks like the ExecutionLog view is for reports that have already completed.
I am on SQL 2008 SP2.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 6, 2011 at 2:13 pm
Try dbo.RunningJobs
September 6, 2011 at 2:16 pm
eccentricDBA (9/6/2011)
Try dbo.RunningJobs
Thanks, but won't this only give me reports running as subscriptions?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 6, 2011 at 2:16 pm
SELECT job_name
,run_datetime
,run_duration
,run_status
FROM
(
SELECT job_name
,run_datetime
,SUBSTRING(run_duration, 1, 2) + ':' +
SUBSTRING(run_duration, 3, 2) + ':' +
SUBSTRING(run_duration, 5, 2) AS run_duration
,run_status
FROM
(
SELECT DISTINCT
j.name as job_name
,run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +
( run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6
)
,run_status
FROM msdb..sysjobhistory h
INNER JOIN
msdb..sysjobs j
ON h.job_id = j.job_id
) t
) t
ORDER BY job_name, run_datetime
September 6, 2011 at 2:20 pm
Revenant (9/6/2011)
SELECT job_name
,run_datetime
,run_duration
,run_status
FROM
(
SELECT job_name
,run_datetime
,SUBSTRING(run_duration, 1, 2) + ':' +
SUBSTRING(run_duration, 3, 2) + ':' +
SUBSTRING(run_duration, 5, 2) AS run_duration
,run_status
FROM
(
SELECT DISTINCT
j.name as job_name
,run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +
( run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6
)
,run_status
FROM msdb..sysjobhistory h
INNER JOIN
msdb..sysjobs j
ON h.job_id = j.job_id
) t
) t
ORDER BY job_name, run_datetime
Thanks, but I think this script provides info on running jobs in general.
I am interested in getting info on currently executing SSRS reports and how long they have been running.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 6, 2011 at 2:23 pm
Marios Philippopoulos (9/6/2011)
eccentricDBA (9/6/2011)
Try dbo.RunningJobsThanks, but won't this only give me reports running as subscriptions?
Actually, this seems to be the answer for what I need. I just checked it and it was showing me a report running, which has no subscriptions.
Thanks!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 6, 2011 at 2:29 pm
Marios Philippopoulos (9/6/2011)
Revenant (9/6/2011)
. . .Thanks, but I think this script provides info on running jobs in general.
I am interested in getting info on currently executing SSRS reports and how long they have been running.
Sorry, I was apparently too much in hurry -- lunchbreak is over -- and misunderstood what you wanted.
September 6, 2011 at 2:35 pm
Revenant (9/6/2011)
Marios Philippopoulos (9/6/2011)
Revenant (9/6/2011)
. . .Thanks, but I think this script provides info on running jobs in general.
I am interested in getting info on currently executing SSRS reports and how long they have been running.
Sorry, I was apparently too much in hurry -- lunchbreak is over -- and misunderstood what you wanted.
No problem.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 7, 2011 at 8:47 am
Marios Philippopoulos (9/6/2011)
eccentricDBA (9/6/2011)
Try dbo.RunningJobsThanks, but won't this only give me reports running as subscriptions?
It gives you current excutions. Although it won't help for a report that is cached. I use it for finding long running reports.
The only other option is that there are some log settings that creates a flat file log on the server.
Report Server Service Trace Log
September 7, 2011 at 8:59 am
eccentricDBA (9/7/2011)
. . . It gives you current excutions. Although it won't help for a report that is cached. I use it for finding long running reports. . . .
Well, cached reports are not executed, so the list is IMO correct.
September 7, 2011 at 9:02 am
Is there a way to "kill" reports running for too long, as identified from dbo.RunningJobs?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 7, 2011 at 9:02 am
Just a guess but cancelling the job could do it...
September 7, 2011 at 9:06 am
Ninja's_RGR'us (9/7/2011)
Just a guess but cancelling the job could do it...
But these are not SQL jobs, they are reports running on a user's machine.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 7, 2011 at 9:08 am
Marios Philippopoulos (9/7/2011)
Ninja's_RGR'us (9/7/2011)
Just a guess but cancelling the job could do it...But these are not SQL jobs, they are reports running on a user's machine.
Subscriptions are setup as jobs, so if the job is running, then it can be stopped.
Sounds good in theory. 😉
September 7, 2011 at 9:12 am
Ninja's_RGR'us (9/7/2011)
Marios Philippopoulos (9/7/2011)
Ninja's_RGR'us (9/7/2011)
Just a guess but cancelling the job could do it...But these are not SQL jobs, they are reports running on a user's machine.
Subscriptions are setup as jobs, so if the job is running, then it can be stopped.
Sounds good in theory. 😉
True, subscriptions are jobs under the covers;
however, my impression, based on limited testing, is that RunningJobs also gives running reports initiated by a user.
How can those be stopped?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply