locked
question about long running reports RRS feed

  • Question

  • We are using SQL Server 2016 enterprise edition. We have SSRS reports running in production for long time & impacting application performance. The application team is fine, if we can terminate the long running reports. Do those adhoc reports or scheduled subscription reports run as a SQL job in the database? I am trying to figure out the best way of terminating those long running reports. Any ideas please? Thanks

    sqldev

    Tuesday, July 28, 2020 11:11 PM

All replies

  • Hi ,

    Yes, the subscriptions run as a SQL job.

    If those report has already run and being processed, you could try open the Executionlog3 view in report server database. It will record the total running time for reports.  See this : Report Server ExecutionLog and the ExecutionLog3 View

    From this view we could know which part of the report rendering takes too long time, see whether it is data retrieving or processing or else take too long time.

    Another suggestion is you could try arrange  processing the report in the idle time of the server, set subscription running at middle night or some other time.

    Back to your topic , you could see this thread :Identify SSRS reports that have been running over 5 minutes

    Regards,

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Naomi N Wednesday, July 29, 2020 2:40 AM
    Wednesday, July 29, 2020 2:34 AM
  • Thanks for the response. Once identified, how can we terminate those long running reports please? 

    sqldev

    Wednesday, July 29, 2020 1:15 PM
  • In SQL Server Reporting Services, a job will be created by SQL Server Agent if any of the following processes is underway:

    • query execution on a remote or local database server
    • report processing
    • report rendering

    To cancel a job that is running on the report server, we can cancel the job directly or reduce the report execution time-out value in the SQL Server Management Studio. Please refer to the steps below:

    1. Open SQL Server Management Studio, and connect to “Reporting Services”.
    2. Under the Report Server node, right-click on the “Jobs” folder and select “Refresh”. Then, right-click on “Jobs” again and click “Cancel All Jobs”.
    3. Right-click on the Report Server node and open the “Server Properties” dialog.
    4. Click the “Execution” option, and set the “Limit report execution to the following number of seconds:” to a much smaller number. After this issue is resolved, this configuration should revert to the previous state.  

    Reference:

    In addition, we can also use the KILL Transact-SQL statement to terminate a normal connection by terminating the transactions that are associated with the specified session ID. For the details information, you can refer to:
    KILL (Transact-SQL)


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 30, 2020 1:42 AM