CODEWITHSUNDEEP
sql-serversql-server-2008reporting
BastianBuhrkall
BastianBuhrkall

Reputation: 348

MS SQL Server, OutOfMemeryExceptions

I am working on an Excel reporting tool for a customer. The tool is being used over a wide array of databases. Basically it takes the data from specific tables, and writes a report in Excel for the customer. It has been working so far, yet while testing I ran into a table which exceeds the allocated memory for retrieving data.

Is there any way to work around this?

I am not allowed to change the allocated momory at the SQL Server.

I will provide any code if necessary, yet i didn't think this would add anything to my question.

Upvotes: 2

Views: 70

Answers (1)

Jon Dysinger
Jon Dysinger

Reputation: 191

I have run into this before returning very large data sets to the grid while using SSMS (SQL Server Management Studio). The error does not occur if it is run in some other process, for example in a stored procedure kicked off from a SQL agent job. So it may be that you are only introducing this error due to your testing methods.

SSMS has a memory limitation of 2 GB as it is a 32-bit process. Likely because it is based on Visual Studio.

Two alternatives: change the results to text (Ctrl+T) which uses less memory, or change the result to save to a file (Ctrl+Shift+F) which uses drive storage instead of loading all of the results to memory.

Note: These settings are also in the tool bar next to the execution plan options.

There is a Microsoft KB article regarding it. (https://support.microsoft.com/en-us/kb/2874903)

SSMS is a 32-bit process. Therefore, it is limited to 2 GB of memory. SSMS imposes an artificial limit on how much text that can be displayed per database field in the results window. This limit is 64 KB in "Grid" mode and 8 KB in "Text" mode. If the result set is too large, the memory that is required to display the query results may surpass the 2 GB limit of the SSMS process.

Upvotes: 1

Related Questions