Message : An error occurred while executing batch. Error message is: Exception of type ‘System.OutOfMemoryException’ was thrown
Last Friday I was working on some SQL Script which needs to be delivered to Production environment to fix very huge duplicate data (Millions of rows). So I was writing some scripts and started executing in the development environment. The script ran for 4 hrs and still executing. So I thought since there are millions of rows need to be corrected and each record has multiple child which also need to be updated, so it might take some 8 – 10 hrs to complete. So I left for the day thinking of can check on Monday.
Came to office on Monday, logged into development machine and saw it just processed only half of the records. So for more than 2 complete days it could process only half the records which cannot be tolerated by Production environment. So reworked the script and improved the performance. So while executing the script some 400+ records processed very quickly and after that the following error has occurred “An error occurred while executing batch. Error message is: Exception of type ‘System.OutOfMemoryException’ was thrown.” At first point I thought some RAM issue or some Disk I/O issues. So checked those and everything seems to be fine. Again tried to run the script, now the above error occurred after processing 220 records…..
So after some googl’ing I could find the actual problem and also solution for the same. In my modified SQL script I used many #Temp tables and inserted some records in the #temp tables which happens in a loop. So each insert into #temp table on each loop the result is shown in Grid i.e., Results to Grid option is enabled. Then I realized the above exception is not due to SQL engine, it is due to .NET Framework (CLR). the Grid displayed below in SQL editor in SSMS is nothing but an .NET Grid. So due to heavy volume of data , the Grid utilizes more memory to occupy all the data. Hence CLR throws SystemOutOfMemory exception, not the SQL engine.
Changed the Results to Grid option to Results to Text. So that the results were shown in Text editor, (Probably it should be an typical .NET TextBox) which consumes very less memory. Then the entire script ran flawlessly and got completed in just 2 hrs.
While executing any long running scripts try to show the results to text so that very less memory is consumed and the performance will be improved a lot.
One more point to note: I have included the main script inside the TRY/CATCH block. Inside the CATCH block included the code for Rollback, obviously TRAN in TRY block. So every time an exception occurred, CATCH block invoked hence transaction will be rolled back. But in the above SystemOutOfMemory exception scenario, the CATCH block did not invoked and hence none got Rollbacked. This is due to TRY/CATCH exception handling is handled by CLR Integrated with SQL. SystemOutOfMemory exception occurred due to non-availability of Stack space. So in TRY block due to high utilization of memory, Stack space is full and hence the exception thrown. Due to Non-availability of Stack space CATCH block won’t execute as it needs some stack space memory to execute. Hence CATCH block fails to handle the exception and obviously code inside CATCH block won’t execute.