Out of Memory Error when running a Select Statement

Hi all,

In the object Explorer in SQL Server 2005 Management Studio, if I right click on the table I want to open and choose open, I eventually get an out of memory error with an error source: MSCORLIB.

This table contains 40m records, but when it opens the table it goes past the 40m and wants to open 83m records, and then eventually comes up with an out of memory error.

The properties of the table indicates that there are 40m records, but when you open the table, at the bottom, where it says retrieving data, it is counting of numbe rof records that surpass the 40m total.

Is it because the database is too big, or is there a way to configure SQL Server 2005 to use X amount of memory?

Regards

Mark:

You might want to temper opening large tables with Management Studio or other tools. If your table contains 40 million rows of data and each row of data is on average 1000 bytes per row, opening this table might take 40 GB of memory to open and display the data. Be careful about viewing all rows of large tables.

Dave

|||

Cheers Dave,

I have used Management Studio, but you are probably right about the memory requirement.

Thanks

Other Generate Script problems in SQL Server Management Studio

Why have often used scripting settings been removed from SQL Server 2005:

Issues associated with Scripting options:

Query Analyzer allowed the following generic options that have been removed from SSMS:
Identifier Delimiter : None, Version dependent, [] or ""
Do not script the collation.
Generate Transact-SQL to remove referenced component. Script tests for existence prior to attempt to remove component.
Do not include 'ON <filegroup>' clause directing filegroup use.
and table Scripting Options:
Qualify object name by its owner.
Do not include definition of identity property, seed, and increment.
Generate SET ANSI_PADDING ON and SET ANSI_PADDING OFF statements before CREATE TABLE statements.

Also Enterprise Manager Generate SQL Scripts tool had:
Files to Generate: Create one file or one file per object - Gone!
Create Drop object options - now it's one or the other.

As

someone else said in another post - why have Microsoft removed useful functionality

from the scripting options. It doesn't make sense - were these options

over looked or was it a conscious decision.

When will these

settings be reinstated - (and they need to be able to be set for the

whole IDE as well as in the wizard (as per Query Analyzer:Tools|Options

)

3rd Party Scripter for me until this is fixed - damn just need to find one now!

Alex

Hi Alex,

Some of these you will see addressed in SP2 -- Such as Create Drop options.

The others are in backlog of work items for the next general release of SQL Server.

A partial - (as in brief) explanation for why some disappeared had to do with 'forward' compatibility.

I will log your input in our Team Server database if you don't mind.

Thanks,

Terrence Nevins

SQL Server Program Manager

Other Generate Script problems in SQL Server Management Studio

Why have often used scripting settings been removed from SQL Server 2005:

Issues associated with Scripting options:

Query Analyzer allowed the following generic options that have been removed from SSMS:
Identifier Delimiter : None, Version dependent, [] or ""
Do not script the collation.
Generate Transact-SQL to remove referenced component. Script tests for existence prior to attempt to remove component.
Do not include 'ON <filegroup>' clause directing filegroup use.
and table Scripting Options:
Qualify object name by its owner.
Do not include definition of identity property, seed, and increment.
Generate SET ANSI_PADDING ON and SET ANSI_PADDING OFF statements before CREATE TABLE statements.

Also Enterprise Manager Generate SQL Scripts tool had:
Files to Generate: Create one file or one file per object - Gone!
Create Drop object options - now it's one or the other.

As someone else said in another post - why have Microsoft removed useful functionality from the scripting options. It doesn't make sense - were these options over looked or was it a conscious decision.

When will these settings be reinstated - (and they need to be able to be set for the whole IDE as well as in the wizard (as per Query Analyzer:Tools|Options )

3rd Party Scripter for me until this is fixed - damn just need to find one now!

Alex

Hi Alex,

Some of these you will see addressed in SP2 -- Such as Create Drop options.

The others are in backlog of work items for the next general release of SQL Server.

A partial - (as in brief) explanation for why some disappeared had to do with 'forward' compatibility.

I will log your input in our Team Server database if you don't mind.

Thanks,

Terrence Nevins

SQL Server Program Manager

OT Question: SQL2005 Management Tools?

Hi;

Although this question is unrelated to Linq, but since everyone here uses SQL2005, I thought it's a good place to ask.

I need a light weight management tool for creating database, tables, fields, indexes and FK. A tool that is easy to use, to view fields and quick to add fields one after another. The SQL Management Studio is great, but I like to have something on the side for quick changes.

I looked around and the few that I found are bigger and more cumbersome than SQL Studio.
Any recommendations?

Thank you in advance!

Hi Ben,

You have a number of options.

Server Explorer within Visual Studio can manage your database - quick and easy.

SQL Server Management Studio Express (http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en). Cut down version aimed at SQL Server Express, this could be your best bet.

SQLCMD - Do everything via the command line

I've heard that Joe Albahari's LinqPad can be used to manage databases and is very lightweight. But I think it can only replace SSMS if you can write the SQL

http://www.albahari.com/linqpad.html

Also, he has done a query express tool http://www.albahari.com/queryexpress.html

Hope this helps.

Ben

|||>>SQL Server Management Studio Express (http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en). Cut down version aimed at SQL Server Express, this could be your best bet.<<

Hi Ben;
I tried to install the express edition, but it tells me that I already have the full blown version installed and the express can not be installed.
Do you known of any other third party products that are light weight.

Thanks for your reply!

OS backup

Hello.

New MSSQL guy coming from Oracle. I've read and understand that you can create backup files for a database using the server management studio.

However, I'm wondering if that is really necessary. Suppose I do not care about the loss of transaction logs, is copying the .mdf database files via OS command a viable backup strategy? If so do I need to bring down the database engine before starting the file copy?

Thanks a lot

You can get a valid backup of a SQL Server database by shutting down the instance and then taking a full OS backup, but you do lose significant functionality by doing so:

Obviously, you're giving up the ability to take your backups with the database online. Native SQL database backups are all online.|||Thank you Kevin.

Our production operations run on Oracle systems. We have to run 1 small MSSQL instance since some MSFT application only use that. So, I'm not really concerned about that DB's size, around-the-clock availibility, or point-in-time recovery.

Given the above requirements and the fact that I have no experience in MSSQL, backing up via the OS route seems to be the path of least resistence :)

Thanks again!