Report Failure when rendering - SQL SERVER 2005 - Report manager

Putoch
Putoch used Ask the Experts™
on
Hi,
i am using SQL SERVER 2005
I have a report that sits on the report manager and exports nightly to a named drive.
this has been working fine until this week
one night it works the next night it does not

when i check the subscription to see if it has saved down it says
''Failure writing file ReportName : An error occurred during rendering of the report.''

When i checked out the log files for this it says:
''e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: An error occurred during rendering of the report., ;
 Info: Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: An error occurred during rendering of the report. ---> Microsoft.ReportingServices.ReportRendering.ReportRenderingException: An error occurred during rendering of the report. ---> System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.''


The report is huge
its well over 160k rows
I can run the report but can no longer export it on the rpeort manager using the Export Tool
I had been able to ftp or set up a subscription to save the file to a drive but now thats not working.

Can anyone give any advise on what to do please?
Kind Regards,
Putoch snapshot-of-report-not-rendering.txt snapshot-of-report-not-rendering.txt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
Add more memory on your SSRS server - from message above:

<<< System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown >>>

Author

Commented:
i have 7.7GB of memory on the server.
That is i checked on Management studio
Right clicked on the Server name there and hit properties and it said on the General tab that there was 7935 (MB)
So would that mean thats how much memory is on the server?

min memory in use = 0
max is set to 2147483647

How much should one have on their server?

Thank you,
Putoch
lcohanDatabase Analyst

Commented:
The message you posted is obvious plus you mentioned that "The report is huge its well over 160k rows" and is failing ocasionaly so at that time the server is runnig out of memory. Not sure what answer you expect that you need more RAM on that server OR split the SSRS on another box (computer) of its own.
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Author

Commented:
Ok thanks Icohan.
I guess i'm wondering why with 7GB of Ram that its causing this issue
When i have another server with 4GB of ram that has never had any issues.
I know they run different load items so i suppose you can't compair

I suppose the answer i was expecting was
find out how much ram is running at a perticular time see if there is any free space at that time to run your report , check the packages that are running at that time is their any other way to optimize them.

i'm not sure how to do all of that but i'll start to research that too.

Thanks a mill for the advise

Regards,
Putoch

Author

Commented:
Hi,
I was looking at this http://msdn.microsoft.com/en-us/library/aa337266.aspx
and it says that this message:

'' An error occurred during rendering of the report.'' -- which is what the results of my subscription says when i try to save the report to the defined drive.

As i had mentioned that there are over 160k rows in this report
and then on the link above it says reeasons for getting this message:
''A message that indicates that the number of rows exceeds the maximum possible rows per sheet is typically caused when the number of rows in an Excel worksheet is exceeded. Excel supports up to 65,000 rows.''

Or ''A message that indicates that the size is not supported is typically caused when the specified RDL page size is not valid. Specify a valid RDL page size and then try again.
''

So i export the files as CSV so it would avoid the limitations on EXCLE.

So is there a way does anyone know as to how to change the properties on my configuration files to allow larger size reports to be saved
The files is able to generate on the rpeort manager its just getting the inforamtion from the report manager thats the problem.

Thanks ,
Putoch
lcohanDatabase Analyst

Commented:
So what is exactly the subscription doing? Is it sending mail on snapshot updated with the report attached to it in Excel format?

I suggest you send only the link and let the user access it and download it by themselfs if they need. The link will be pointing to the history so the report is not generated again especialy is huge.

Author

Commented:
I set the report up in the subsccription to
Deliver by Windows File Share

the report Render Format is CSV

this used to work fine, so i would like to know why it does not work now,
perhaps it is to do with the RAM being limited on the systems, but i want to make sure that it is not something else either.
as i am able to run the query for the rpeort on Management studios
and then select the results and save as to the drive and save the results down in CSV.

I can't send them the link to the report, as they can not export the report from the report manager.
It causes an error when trying to do this. Even when exporting to csv.
this is why i started to save it down to the drive in the first place.

So i'm thinking, is there something limiting the files to save when it hits a certain amount of rows?
is this something i can change?
as if this is the case perhaps getting more memory will not help the issue.

thanks
Putoch
lcohanDatabase Analyst

Commented:
Obviously you don't want to give users access to Report Manager but SSRS has two default websites for that matter. One is Report Server that could/should be accessed by Users and not SSRS admins in IE or some other browser available on the users machines.
The error is clear text and there's no hidden issue that when these reports need to be created the serevr runs SOMETIMES not always out of memory and this depends on many factors. The issue is that even CSV file gets created not EXCEL I believe the backgound proccess to be executed is still the same and this needs lots of memore for your large reports.
As mentioned to fix this:

Add more RAM to the box running SQL and SSRS or
Split SSRS from SQL box or
Split the report in smaller reports.

Author

Commented:
Ok thanks for your advise Icohan.
I'll try doing this and will let you know how this has helped.

Regards,
putoch
Database Analyst
Commented:
Good luck!

Here's a little bit more from SQL memory point of view:


Optimizing Server Performance Using Memory Configuration Options: http://msdn.microsoft.com/en-us/library/ms177455.aspx

How to determine proper SQL Server configuration settings: http://support.microsoft.com/kb/319942/EN-US/

According to Microsoft, for 32-bit editions of SQL Server, it is important to make sure that the /3GB and /PAE switches are set in accordance with the amount of physical memory in the system startup as follow:

• If you have 3-4 GB memory, include the /3GB switch in the startup.
• If you have 4-8GB memory, use /3GB and /PAE
• If you have 16 GB or more, use only /PAE, as /3GB will cripple memory over 8GB.

• When you have set /PAE, go into SQL Server's configuration and set the option to use AWE to ON. If you do this, however, you need to also specify a maximum memory value in SQL Server, if you do not, then SQL Server will take all but 128MB of the computer's memory if the automatic memory management is used in SQL Server.

• The user who runs the SQL Server needs to have the 'lock pages in memory' user right in the local security policy, or it will have problems allocating the memory for SQL Backup's extended stored procedure. If you have checked everything above, please check this as well.

Author

Commented:
Thank you icohan.

One last question for you, the AWE settings
On this site http://msdn.microsoft.com/en-us/library/ms190673.aspx
it says ''This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible.''

So i was afraid to enable it on SQL SERVER 2005.
now i believe this is refering to SQL SERVER 2008, but i wasn't sure if with new patches being added to SQL SERVER 2005 it would cause issues enabling AWE

I do not currently have this enabled, and i can see from our CPE monitoring tool that the server only reaches 4GB of RAM usage at any time even though there is 16GB of RAM available. So i think perhaps by enabling the AWE and setting the ini file with the 3GB/PAE additions

have you any thoughts on this?
thank you,
Putoch.



Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial