Recently, I encountered a situation where a client needed help generating a report from a large dataset in SQL Server. After analyzing the schema and understanding the requirements, I provided them with the query to retrieve the desired results. However, when they tried to save the results in an Excel sheet, they encountered an error – System.OutOfMemoryException.

The error message displayed was: “Exception of type ‘System.OutOfMemoryException’ was thrown.”

Upon further investigation, I discovered that this error occurs when the system runs out of memory while performing an operation. In this case, the client had executed the query in SQL Server Management Studio (SSMS), received a large number of rows as output, and attempted to copy the data by pressing Ctrl+C in the grid.

When we delve into the technical details of the error, we find a stack trace that provides insights into the underlying cause:

Program Location:
at System.Number.FormatInt32(Int32 value, String format, NumberFormatInfo info)
at System.Int32.ToString(String format, IFormatProvider provider)
at System.DateTimeFormat.FormatCustomized(DateTime dateTime, String format, DateTimeFormatInfo dtfi, TimeSpan offset)
at System.DateTimeFormat.Format(DateTime dateTime, String format, DateTimeFormatInfo dtfi, TimeSpan offset)
at System.DateTimeFormat.Format(DateTime dateTime, String format, DateTimeFormatInfo dtfi)
at Microsoft.SqlServer.Management.UI.Grid.StorageViewBase.GetCellDataAsString(Int64 iRow, Int32 iCol)
at Microsoft.SqlServer.Management.QueryExecution.QEResultSet.GetCellDataAsString(Int64 iRow, Int32 iCol)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.GridResultsGrid.GetTextBasedColumnStringForClipboardText(Int64 rowIndex, Int32 colIndex)
at Microsoft.SqlServer.Management.UI.Grid.GridControl.GetClipboardTextForCells(Int64 nStartRow, Int64 nEndRow, Int32 nStartCol, Int32 nEndCol)
at Microsoft.SqlServer.Management.UI.Grid.GridControl.GetClipboardTextForSelectionBlock(Int32 nBlockNum)
at Microsoft.SqlServer.Management.UI.Grid.GridControl.GetDataObjectInternal(Boolean bOnlyCurrentSelBlock)
at Microsoft.SqlServer.Management.UI.Grid.GridControl.GetDataObject(Boolean bOnlyCurrentSelBlock)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.GridResultsTabPageBase.OnCopyWithHeaders(Object sender, EventArgs a)

By analyzing the stack trace from bottom to top, we can identify the sequence of operations leading to the error:

FormatInt32
ToString
FormatCustomized
Format
GetCellDataAsString
GetCellDataAsString
GetTextBasedColumnStringForClipboardText
GetClipboardTextForCells
GetClipboardTextForSelectionBlock
GetDataObjectInternal
GetDataObject
OnCopyWithHeaders

From this analysis, it becomes evident that the error is related to the copying of data from the grid, as indicated by the presence of “Clipboard” in the stack trace. SQL Server Management Studio is not designed to handle such large data copying operations, which can result in the system running out of memory.

To overcome this issue, I recommended two possible solutions to the client:

1. Save the query output directly to a file instead of copying it to the grid or text in SSMS. This can be achieved by using the sqlcmd utility or by following the steps outlined in one of my earlier blog posts: SQL SERVER – Automatically Store Results of Query to File with sqlcmd.
2. Another option is to generate a CSV file using SQL Server Management Studio. This can be done by following the steps described in my blog post: SQL SERVER – SSMS Trick – Generating CSV file using Management Studio.

It’s important to note that even if the query execution itself is very large, it can fill the SSMS buffer and result in the same OutOfMemoryException error. Therefore, it is advisable to use the aforementioned solutions to avoid such issues.

I hope this blog post helps you understand the System.OutOfMemoryException error in SQL Server and provides you with effective workarounds to handle large result sets. Feel free to reach out if you have any further questions or concerns.