I was trying to help my client in generating a report large data set. After spending some time and understanding the schema, I could provide them the query to get the results. Now, he wanted to save the results in excel sheet. So, he ran the query in SQL Server Management Studio (SSMS), got a lot of rows as output and he hit Ctrl+C in the grid. Let us learn about System.OutOfMemoryException error.
Here is the text of the message (copied using the Copy icon at the bottom left of the message windows)
Exception of type ‘System.OutOfMemoryException’ was thrown. (mscorlib)
If we click on the technical detail icon, we can see below.
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)
Based on my understanding, we read stack from bottom to top. So, if I build a stack by ignoring parameters, it would be like below.
As we can see “Clipboard” – I would assume that its due to copy we are seeing out of memory because we are copying many rows from grid.
As we discovered above, I explained to them that SQL Server Management Studio is not design to handle such kind of requirement. If we want to save the result set into the file, we should save the query output directly to file rather than grid or text in SSMS (and then doing Ctrl + C and Ctrl + V).
Other option would be to follow steps given in one of my earlier blogs
SQL SERVER – Automatically Store Results of Query to File with sqlcmd
SQL SERVER – SSMS Trick – Generating CSV file using Management Studio
I also found that if result is very large, even query execution can fill SSMS buffer and raise same error.
Hope you would be able to work around the issue by using this blog.
Reference: Pinal Dave (https://blog.sqlauthority.com)
In my case the user was running a query that contained a group by clause with many columns in the clause and a count call in the query. Turns out the data must sorted before the grouping for counting can be applied I created a non-clustered index on the where clause column and used the includ to in clude all of the columns in the index. That solved the out of memory problem and the query ran very fast at that point.