Post reply

Query to give reports currently running on report server

  • September 6, 2011 at 1:52 pm

    #256724

    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

    #1377672

    Try dbo.RunningJobs

  • September 6, 2011 at 2:16 pm

    #1377677

    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

    #1377678

    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

    #1377683

    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

    #1377686

    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]

  • September 6, 2011 at 2:29 pm

    #1377689

    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

    #1377692

    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

    #1378019

    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

  • September 7, 2011 at 8:59 am

    #1378039

    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

    #1378042

    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

    #1378044

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

  • September 7, 2011 at 9:06 am

    #1378045

    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

    #1378049

    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

    #1378051

    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 21 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy