SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 OutOfMemoryException
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dr223
Constraint Violating Yak Guru

434 Posts

Posted - 07/07/2014 :  06:14:28  Show Profile  Reply with Quote
Hi,

I have a large database with a table of 200 Million records stored in SQL Server 2008 Standard.

The server that holds the SQL Server has the following specs;
Windows Server 2008 R2 Standard 64-bit.
16GB RAM
4 CPU's
Memory = 500 GB with 220 GB used by the database.

SQL Server
SQL Server 2008 Standard Edition

At runtime when I try to open a column from the table;

i.e.,

select ConsultationGuidDigest from dbo.Consultation


It failed at record 33,554,432 and throws the error message

An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

I googled and the option of output the results as text - same issue
Output the results to a file - is not an option I want to go ahead with with the tasks I have.

If, I need to upgrade the specs for SQL Server or do anything to accomodate this so that the tables could open ok - please advice

Thank you in advance

James K
Flowing Fount of Yak Knowledge

3758 Posts

Posted - 07/07/2014 :  11:58:26  Show Profile  Reply with Quote
This is not a limitation of SQL Server; it is a limitation of SQL Server Management Studio which is a 32 bit application. What is the purpose of sending 33 million records to the output window of SQL Server Management Studio? I would venture a guess that Microsoft has not tried to improve or increase the capacity of SSMS to output that much volume of data because they couldn't forsee a reason for someone to scroll through that much data in a SSMS window.

If outputting to file is not an option, you can try the other alternative - using SQLCMD.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37446 Posts

Posted - 07/08/2014 :  12:40:36  Show Profile  Visit tkizer's Homepage  Reply with Quote
Why are you returning all rows of this table to the client? Use bcp if you need all the data for some other task.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

rajbtm05
Starting Member

India
3 Posts

Posted - 07/09/2014 :  03:40:57  Show Profile  Reply with Quote
It's a limitation on SSMS and not with SQL Server. Try restarting the machine hosting SSMS.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000