Query to give reports currently running on report server

Question

Query to give reports currently running on report server

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Marios Philippopoulos (9/7/2011)
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?

I tested that and I was never able to get anything returned from that table (even with 1 minute reports). I didn't test the subscription part because you guys said this is what the table was holding. First time I hear of that table and I can't find anything useful on google besides bug reports.

Maybe you're down to killing to connections directly at this point.

eccentricDBA SSCarpal Tunnel Points: 4844 More actions · Answer 2

Marios Philippopoulos (9/7/2011)
Is there a way to "kill" reports running for too long, as identified from dbo.RunningJobs?

The only way I know of how to do it outside of Managment Studio is via PowerShell. You would just change the $reportServer and $JobID value.

CLS

[string] $reportServer = "localhost"

[string] $JobID = "ihpln0fxamxd1cai0isxldq2"

[string] $uri = "http://{0}/ReportServer/ReportService2010.asmx?WSDL" -f $reportServer

$reportWebService = New-WebServiceProxy -uri $uri -UseDefaultCredential -namespace "ReportingWebService"

$reportWebService.CancelJob($JobID)

The following code would return a list of running jobs.

CLS

[string] $reportServer = "localhost"

[string] $uri = "http://{0}/ReportServer/ReportService2010.asmx?WSDL" -f $reportServer

$reportWebService = New-WebServiceProxy -uri $uri -UseDefaultCredential -namespace "ReportingWebService"

$reportWebService.ListJobs() | select JobID, Path, Name, Machine, User, Action, Status

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 3

eccentricDBA (9/7/2011)
Marios Philippopoulos (9/7/2011)
Is there a way to "kill" reports running for too long, as identified from dbo.RunningJobs?
The only way I know of how to do it outside of Managment Studio is via PowerShell. You would just change the $reportServer and $JobID value.
CLS
[string] $reportServer = "localhost"
[string] $JobID = "ihpln0fxamxd1cai0isxldq2"
[string] $uri = "http://{0}/ReportServer/ReportService2010.asmx?WSDL" -f $reportServer
$reportWebService = New-WebServiceProxy -uri $uri -UseDefaultCredential -namespace "ReportingWebService"
$reportWebService.CancelJob($JobID)
The following code would return a list of running jobs.
CLS
[string] $reportServer = "localhost"
[string] $uri = "http://{0}/ReportServer/ReportService2010.asmx?WSDL" -f $reportServer
$reportWebService = New-WebServiceProxy -uri $uri -UseDefaultCredential -namespace "ReportingWebService"
$reportWebService.ListJobs() | select JobID, Path, Name, Machine, User, Action, Status

That's exactly what I was looking for, 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]

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 4

Ninja's_RGR'us (9/7/2011)
I tested that and I was never able to get anything returned from that table (even with 1 minute reports). I didn't test the subscription part because you guys said this is what the table was holding. First time I hear of that table and I can't find anything useful on google besides bug reports.
Maybe you're down to killing to connections directly at this point.

I seem to be getting nothing now from RunningJobs, even though I am running a report that takes about 1 min to complete.

It looks like the powershell script, kindly shared above, is the way to go for identifying and terminating run-away reports.

__________________________________________________________________________________
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]

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 5

You might want to see the SQL table ExecutionLog2

Raunak J

Revenant SSC-Forever Points: 42467 More actions · Answer 6

Raunak Jhawar (9/25/2011)
You might want to see the SQL table ExecutionLog2

Jobs do not show in ExecutionLog2 until they complete; i.e., until they get TimeEnd in ExecutionLogStorage -- check the join in the FROM clause of the view.