none
How to cancel a query running on back-end of an SSRS report? RRS feed

  • Question

  • Hello,

    How can I cancel a query running on back-end (on SQL Server) of an SSRS report in SQL Server 2008 R2?  Just cancelling the report by closing the tab on the Reports Manager site does not actually cancel/kill the query; it is still running. The query may still be running due to a very large results set or a loop. I am not sure how to terminate the actual query that renders the report.

    Please advise.

    -Lenny


    Lenny Finkel

    Monday, April 9, 2012 6:08 PM

Answers

  • Hi,

    I think Dan and Mike have already answered the question about KILL command.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    • Marked as answer by Lenny F. _ Monday, April 16, 2012 4:18 PM
    Wednesday, April 11, 2012 1:43 PM

All replies

  • Hi,

    You cannot cancel, but kill the session at the DB back-end.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    Monday, April 9, 2012 6:32 PM
  • Janos, 

    How do you kill the session at the DB back-end?


    Lenny Finkel

    Monday, April 9, 2012 8:06 PM
  • Hi Janos,

    How can I kill the session at the DB back-end - as you have replied?


    Lenny Finkel

    Tuesday, April 10, 2012 9:50 PM
  • Does anyone know how to kill the session at the DB back-end?


    Lenny Finkel

    Tuesday, April 10, 2012 10:40 PM
  • You'll need to identify the problem session and use the KILL command.  See http://msdn.microsoft.com/en-us/library/ms173730.aspx.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Wednesday, April 11, 2012 1:43 AM
  • Hi Lenny,

    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)

    Regards,
    Mike Yin

    Wednesday, April 11, 2012 9:41 AM
    Moderator
  • Hi,

    I think Dan and Mike have already answered the question about KILL command.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    • Marked as answer by Lenny F. _ Monday, April 16, 2012 4:18 PM
    Wednesday, April 11, 2012 1:43 PM
  • Is there a automated way or a setting that would clean up any SpId/session for cancelled/abandoned SSRS report run.

    Thanks


    Mahesh

    Friday, October 7, 2016 1:50 PM