question

kmy_kaoru avatar image
kmy_kaoru asked

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
sql-server-2008ssrs
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Doug Lane avatar image
Doug Lane answered
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.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I don't think, that the server is pushing the data to the SSRS if the connection timed out or failed. In that case to command is aborted and rolled back. So probably a rollback in that case is being performed but in the connections list is visible last command executed.
0 Likes 0 ·
The database server (at least, SQL Server) will complete the requests it was given by RS, whether the report renders or not. Try this: open a report in BIDS, open Profiler against the source database server, run the report then immediately cancel it. The report will stop loading at the same time Profiler reports "SQL:BatchCompleted" for whatever text queries the report was able to send off before being cancelled. RS has no mechanism for aborting a query in progress on the database server. It can only stop sending additional queries and wait for those already requested to finish.
0 Likes 0 ·
@Doug Lane you are right about the continuous execution of the report.
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
Does your procedure populates some table or modifying some data prior displaying them? In that case when the report is interrupted probably a ROLLBACK is being performed.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

kmy_kaoru avatar image
kmy_kaoru answered
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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Reporting Services will continue the execution of a report after it is cancelled until whatever data sets it had requested have been returned. There's no way of having RS proactively terminate queries it has already sent off to the data source. Check out the following: 1) The report deployed to the server is the same as the one you're testing in BIDS. If you can, re-deploy the one you're testing with (assuming it's the one you think is already out there.) 2) Make sure you're testing with the same parameters used in the slow production environment. You can find these in the Parameters column of ReportServer.dbo.ExecutionLog2. 3) Make sure you're running against the same data source between BIDS and production. It could be your local report is running against a less-burdened dev or test server. Hopefully that helps.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.