September 7, 2009 at 3:14 am
I have a database with around 27 lakhs of records and i need to show this rows as an reports by using SSRS and each report will contains around 3 lakhs or less than that. After my design part is completed when i run the reports i got the error "System Out of Memory". I have tried the following solution from microsoft "http://support.microsoft.com/kb/909678" but no luck. I have 4 gb of RAM and 160 Gb of hard disk space but even then i was getting this error. Is any wrok around for this error?
This is my requirement I need to publish or provide access to the users to access the data present in the SQL Server database through a web environment.
Is any other simple and best way too publish this data through web services?
cheers,
Arul
September 7, 2009 at 11:39 am
sory but what does it mean: "lakhs" ?
September 7, 2009 at 12:00 pm
Marcin Gol [SQL Server MVP] (9/7/2009)
sory but what does it mean: "lakhs" ?
It means 100,000. It comes from the same Indo-European root as lox. Since salmon weren't common in India, it came to mean "a great number" in the Indic branch.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 7, 2009 at 12:21 pm
thnx drew :)))
so the question is - i have a very big number of rows in my report - and i got a "out of memory exception" what should i do ?
basic answer is ... it depends ;D
Arul? can you told as more about yours report? do you really need so many rows ? did you know where is the limit of your system? maybe using /3GB in boot.ini could help ?
September 7, 2009 at 12:42 pm
You're welcome. It's nice to know that my Master's in Linguistics didn't totally go to waste.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 7, 2009 at 12:43 pm
This is my requirement I need to publish or provide access to the users to access the data present in the SQL Server database through a web environment.
Is any other simple and best way too publish this data through web services?
300,000 rows is not a lot of data you need to do design so you can create Views to run your Reports because when you run the Views you are accessing all your columns in one place. If you are in Win2003 it may be related to limitations of IIS6 and below and if you are using the file system deployment I think you need to change. And the support article is only relevant to SSRS2000 and SSRS 2005 both are IIS dependent. You should also know IIS6 and comes with most features disabled you may have to remove it and add it again this time click on details there are a lot of Asp.net features you need to enable. In IIS7 and IIS7.5 you have to go to programs in control panel and click on Turn Windows features on or off and configure IIS because it could be your problem.
Kind regards,
Gift Peddie
February 22, 2010 at 2:31 am
What are you seeing as an error in your report logs and the event logs when this issue occurs bedsides the "Out of Memory" error. I'm assuming the report your are running is "On Demand". What type of environment, server setup do you have? Are you running SSRS on a separate server that SQL Server is installed on? Is the machine that you are running SSRS on dedicated to SSRS or are there other applications running on that server? Is your data on the same instance that the RS catalog is on? Or different one? You might run the query and check the execution plan to see if there isn't something you can do to get the query to run faster/more efficient for one thing.
You could try making these changes in IIS. Go to DefaultAppPool Properties. Before making any changes document current settings just in case you need to change it back. I usually do screen prints and put into a document.
1) Set the application pool to be recycled at a specific time instead of minutes. So chose the third option and set the time to when you know there is a maintenance windows or low activity.
2. De-select the option “shutdown worker processes after being idle…” and
3. Set the web garden option to 1 time # of CPU’s. Example two dual core processors. Change to 4.
Save a copy of the file before making changes.
In the rsreportserver.config file (typically located at %:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting Services\ReportServer), change the following value:
<Add Key="DatabaseQueryTimeout" Value="120"/>
To:
<Add Key="DatabaseQueryTimeout" Value="300"/>
Also change the following value:
<Add Key="CleanupCycleMinutes" Value="10"/>
To:
<Add Key="CleanupCycleMinutes" Value="30"/>
You might take a look at this setting as well. I think the default is 60. You might could up it to 75 or 80 depending what else is on the machine.
<MemoryLimit>70</MemoryLimit>
My last recommendation is to remind you that SSRS is not a data extraction tool. If possible I would look into using SSIS to run this report. If you are having to do a lot of formatting in your report you may need to look into that cause formatting and redering that many rows to a report could be causing a problem. In that case there are some things you can avoid. You might try this link to make sure you are not designing your report improperly for the type of report you are redering out to.
http://msdn.microsoft.com/en-us/library/ms156268(SQL.90).aspx
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply