March 10, 2017 at 2:37 pm
We've got a report with a vexing problem. When executed directly through Report Manager, all is fine: the data is pulled, rendered, and displayed in just over six minutes, with a row count of 2.6M. We can then export the report to a pipe-delimited text file, 246Mb, in another six minutes.
The problem arises when we try to execute the same report via a job that ultimately employs a CLR to automate the generation and download of the report. At any row count above 2.47M, the process hangs somewhere, and the SP that invoked the CLR takes about an hour to return (we're guessing that it finally gets cut off by some GC process in SSRS). But when we look in the execution log for SSRS, we see plainly that the data was pulled, processed, rendered, and (supposedly) exported in the expected amount of time: ± 13 minutes (six to process the data, six to create the text file). But, no text file ever appears in the folder! If we use TOP to limit the row count to 2.47M or less, everything runs fine, SP returns, file's there. A real poser!
We've run through all the possible timeout settings in the config file, with no luck. We've run traces, extended events, nothing seems out of the ordinary, save the time taken for the SP to return at the full row count, and the missing file.
We are thoroughly vexed and frustrated. Has anyone had and resolved a similar issue? We don't have access to the CLR code, but suppose we could get it if we had to, but even then we're not sure there's anything in there that would explain the anomaly.
Any help/insight/suggestions welcome. We're not posting the config file, but it's been restored to the defaults (since none of the changes helped). Happy to provide other info as deemed necessary.
Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
March 15, 2017 at 3:36 pm
Roland Alexander STL - Friday, March 10, 2017 2:37 PMWe've got a report with a vexing problem. When executed directly through Report Manager, all is fine: the data is pulled, rendered, and displayed in just over six minutes, with a row count of 2.6M. We can then export the report to a pipe-delimited text file, 246Mb, in another six minutes.The problem arises when we try to execute the same report via a job that ultimately employs a CLR to automate the generation and download of the report. At any row count above 2.47M, the process hangs somewhere, and the SP that invoked the CLR takes about an hour to return (we're guessing that it finally gets cut off by some GC process in SSRS). But when we look in the execution log for SSRS, we see plainly that the data was pulled, processed, rendered, and (supposedly) exported in the expected amount of time: ± 13 minutes (six to process the data, six to create the text file). But, no text file ever appears in the folder! If we use TOP to limit the row count to 2.47M or less, everything runs fine, SP returns, file's there. A real poser!
We've run through all the possible timeout settings in the config file, with no luck. We've run traces, extended events, nothing seems out of the ordinary, save the time taken for the SP to return at the full row count, and the missing file.
We are thoroughly vexed and frustrated. Has anyone had and resolved a similar issue? We don't have access to the CLR code, but suppose we could get it if we had to, but even then we're not sure there's anything in there that would explain the anomaly.
Any help/insight/suggestions welcome. We're not posting the config file, but it's been restored to the defaults (since none of the changes helped). Happy to provide other info as deemed necessary.
I doubt I can help much here as the issue appears to be the CLR piece but a few other things to check...
Go through the Report Server Service log file in your ReportingServices\LogFiles directory.
You might see what's going on there when the report runs.
If the job your doing this through is a SQL Agent job, the go to the step, select Edit, then Advanced. From here you can add an output file to capture the output from the job step. It may give you nothing at all but worth a try.
Sue
March 16, 2017 at 6:58 am
Sue_H - Wednesday, March 15, 2017 3:36 PMRoland Alexander STL - Friday, March 10, 2017 2:37 PMWe've got a report with a vexing problem. When executed directly through Report Manager, all is fine: the data is pulled, rendered, and displayed in just over six minutes, with a row count of 2.6M. We can then export the report to a pipe-delimited text file, 246Mb, in another six minutes.The problem arises when we try to execute the same report via a job that ultimately employs a CLR to automate the generation and download of the report. At any row count above 2.47M, the process hangs somewhere, and the SP that invoked the CLR takes about an hour to return (we're guessing that it finally gets cut off by some GC process in SSRS). But when we look in the execution log for SSRS, we see plainly that the data was pulled, processed, rendered, and (supposedly) exported in the expected amount of time: ± 13 minutes (six to process the data, six to create the text file). But, no text file ever appears in the folder! If we use TOP to limit the row count to 2.47M or less, everything runs fine, SP returns, file's there. A real poser!
We've run through all the possible timeout settings in the config file, with no luck. We've run traces, extended events, nothing seems out of the ordinary, save the time taken for the SP to return at the full row count, and the missing file.
We are thoroughly vexed and frustrated. Has anyone had and resolved a similar issue? We don't have access to the CLR code, but suppose we could get it if we had to, but even then we're not sure there's anything in there that would explain the anomaly.
Any help/insight/suggestions welcome. We're not posting the config file, but it's been restored to the defaults (since none of the changes helped). Happy to provide other info as deemed necessary.
I doubt I can help much here as the issue appears to be the CLR piece but a few other things to check...
Go through the Report Server Service log file in your ReportingServices\LogFiles directory.
You might see what's going on there when the report runs.
If the job your doing this through is a SQL Agent job, the go to the step, select Edit, then Advanced. From here you can add an output file to capture the output from the job step. It may give you nothing at all but worth a try.Sue
Much appreciated, Sue. Alas, the logs are not helpful (should have mentioned that we've pored over these). It's a rather generic message indicating the problem could be with disk space, memory pressures, or other causes. There's plenty of disk space, not that much memory pressure, and nothing else going on with the server. <Sighs> We've pretty much thrown in the towel for now. Thankfully it's a pipe-delimited file we send to the client, so we're able to generate it via BCP. Still vexing, though.
Thanks again for your suggestions 🙂
Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply