Query to give reports currently running on report server

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

  • Try dbo.RunningJobs

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

  • 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

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

  • Marios Philippopoulos (9/6/2011)


    eccentricDBA (9/6/2011)


    Try dbo.RunningJobs

    Thanks, 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]

  • 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.

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

  • Marios Philippopoulos (9/6/2011)


    eccentricDBA (9/6/2011)


    Try dbo.RunningJobs

    Thanks, 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

    http://msdn.microsoft.com/en-us/library/ms156500.aspx

  • 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.

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

  • Just a guess but cancelling the job could do it...

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

  • 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. 😉

  • 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