Report aborted in SSRS 2008, but still active in the database
Hi all, I got some annoying problem, in Reporting Service 2008.. I'm using stored procedure in the data warehouse (dwh) to generate some recordset to feed on to the static report on SSRS, this method is running well.. But, if the user aborted the report execution, or the report got timeout error.. Why does the stored procedure execution is still running on the dwh ? is there something wrong with my design, or this is the nature of SSRS ? i will appreciate any help on this issue.. Thank u
There's nothing wrong with your design -- Reporting Services is built that way. Once your request is sent to the data warehouse, it's in the hands of that server. The process runs like this: 1. Data is requested from the database server (or other source) by Reporting Services. 2. The database server processes the request and generates results while Reporting Services awaits a response. 3. The database server sends results back to Reporting Services. 4. Reporting Services processes the results it received from the database server. If Reporting Services gives up (either from timeout or user cancellation) before step 4, the database server continues its work for steps 2 and 3 anyway, sending the results back whether RS still wants them or not. If user cancellations are a problem, you can check the ExecutionLog2 view to see who's aborting (look for TimeDataRetrieval > 0 and TimeRendering = 0). It may be one particularly impatient user.
Thank u for all of your responses, i really apriciate it :) My stored procedure populates and processing the data required for the report, and doesn't have begin tran or rollback tran. If it's the nature of the Reporting Service, just to send the request to the database server (I'm using SQL Server 2008 also).. Is there any workaround, if the report aborted, then it will send some query back to the SQL Server (ie. kills the session at the SQL Server).. ? Because I have some ghost sessions that are still running for hours and making the server slow, the CPU utilization is higher than normal.. Maybe because of the stored procedure processing is quite big also. Let me explain the case : For example I have report A that triggers the execution of Stored procedure SP-A. SP-A itself when triggered and run directly at the SQL Server, it will cost 2 minutes before the result showed. When I'm trying to view the report through SQL Server BI Development Studio, it will cost more than 2 mintues to show. But when I tried to view the report through Reporting service web, it will get timeout after more than 30 minutes running. After timeout, I got the ghost session still running at the SQL Server for hours (more than 6 hours) Do you know why the execution for the reports is different depends on from which program I called the stored procedure ? Pls help me to resolve this problem :) Thank u :D