September 13, 2010 at 7:45 am
This stored procedure (proc1) I am executing selects few records and iterate through it and execute another main procedure (main1) for each record. This main procedure (main1) is actually a container and further calls many more procedures to complete the task (complex calculations involving no of SPs and temp table etc).
In past I was able to run proc1 (from SSMS) for 130 records. On this dedicated SQL server, it used to take around 15-20 minutes to finish.
Now I am started getting following error. (no other process/app is running when I am executing this proc1)
"An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown?
Only thing that is changed - no of records increased to 200 from 130 earlier. However I am getting above error at 8th record itself or sometime even on 3rd record.
verified the server property (max server memory) and looks like it is set to max.
Attached perf mon shows the avg disk quoue lenght spikes whenever this error happens.
can anyone help me identify why I am getting this error?
thanks
September 13, 2010 at 8:36 am
This has nothing to do with system memory.
See the below information
" The error is a .NET Out of Memory exception pointing to the Management Studio running out of available memory. This is not a database engine out of memory issue. The GRID output requires a .NET Grid View to be created where as the Text output uses a Text Box to store the output returned by the database engine. The amount of memory consumed by the GRID is higher than a TexBox. It is always advisable to store the output of a query returns a large number of rows into a file (CTRL+SHIFT+F) or use SQLCMD to generate the output into a CSV/TXT file. This would help optimize the memory usage on the box that is executing the query and also prevent re-execution of the query due to client box out-of-memory conditions.
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog: http://troubleshootingsql.wordpress.com
Twitter: @BanerjeeAmit
SQL Server FAQ Blog on MSDN: http://blogs.msdn.com/sqlserverfaq
"
You can find the discussion about this error in the below link
-Karthik
September 14, 2010 at 6:12 am
selecting results to file solved the problem.
However after going through the link provided by you, it seems that the limit is 33 mil records that can be dispalyed in SSMS. In my case it was not even few hundred records when it was breaking. Although it worked, dont know real reason for it.
thanks anyway!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply