Solved

Out of Memory Error on SQL

Posted on 2011-02-15
14
765 Views
Last Modified: 2012-06-27
I have scripted out a SQL2008R2 DB in order to recreate the DB as a 2005 version.  I ran the script with no issues, but when I run the script for the 2005 DB I receive an "Out of Memory" error.  How much memory do I need to complete this as it is an urgent matter to get htis back to 2005 for our client.

The DB is 210MB.  I deleted the Log file so that is under a meg.  The script is 240MB itself.

I just need to know how I can get this script run to create my sql2005 DB.

Please help.
0
Comment
Question by:mrwarejr
  • 7
  • 4
  • 2
  • +1
14 Comments
 
LVL 33

Expert Comment

by:knightEknight
Comment Utility
Assuming the issue is with the size of the script, why don't you break the script into three or four separate scripts, and run them consecutively.
0
 
LVL 3

Expert Comment

by:sunezapa
Comment Utility
You may consider doing it with SQL  Management Studio 2008, if you have access to a 2005 Server from the 2008 SQL server.
I found this to try:
-> Open Your Management Studio 2008

-> Connect to a MS SQL Server 2005 (The 2005 SQL Server which You wish to copy data to, or another one, and you just copy the result backup-file)

-> Greate a Temp Database (name it whatever) -> Right click your temp database

->Task ==> Import Data

-> Choose your 2008 Database Engine (with Specific Databases--The database with all the data )

-> Use the Import Wizard to Import them

->(This might take a while)

-> After everything done, you have successfully transfered all your 2008 data(tables or row data) into 2005 SQL server

->Then make a database restore file now-

>You can use this file to restore data for both 2008/2005
0
 
LVL 15

Accepted Solution

by:
Aaron Shilo earned 250 total points
Comment Utility
hi

the problem is the script size it self.
i would suggest on of the two.

A:
1. backup the database on 2k8r2
2. restore it on sk8r2 ith a different name
3. change the compatibility level to 2005
4. backup the db again this time with comp level 2005
5. restore it to the 2005 server
6. rename the db to the original name.

B:
cut the script into smaller parts.
0
 

Author Comment

by:mrwarejr
Comment Utility
Okay here is where I am.  

Ashilo:
The Source 2008r2 DB is already set to Compatibility Level of 90 (2005).  I tried to just backup the DB from 2008R2 and restore it to 2005 and get an error as seen in the image.

Sunezapa:
I then tried your way and received the following warnings.  I am only showing a portion of the copying rows messages as there are too many to fit in one screen shot.

Not sure if the import warnings matter at all, but I am going to test the DB now.
Compatibility-Level.jpg
Data-Import.jpg
0
 

Author Comment

by:mrwarejr
Comment Utility
On the import it did not get everything needed either.  It is missing all the stored procs and who knows whatelse.
0
 
LVL 15

Expert Comment

by:Aaron Shilo
Comment Utility
hi

it looks like your using some kind of a multiplexd or partitioned backup file
and not using all the files in the restore.

0
 

Author Comment

by:mrwarejr
Comment Utility
I am just creating a Full backup and then restoring.  All from a single bak file.  Do I need to do this a different way.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 3

Assisted Solution

by:sunezapa
sunezapa earned 250 total points
Comment Utility
all the Informations are just fine and to forget...
The Warnings MAY be something, but general I do not look, if it is working - it could issues as "I can't find exactly match in types, so I just use the nearest type available"...
i any ERRORS I would study, but then the import stops and you will know...
If the Big Script is because of a lot of data, you may use the import, and then script the Stored Procedures... and the wahtever's.

Or: try the proposed way of dividing the full Scripts into smaller ones.
0
 

Author Comment

by:mrwarejr
Comment Utility
How would I divide the script and know where to split it up at as it is scripting Schema and Data?  I guess I can create the DB with the Schema script which works fine.  Then split up the data only.  I will try that.  
0
 
LVL 3

Expert Comment

by:sunezapa
Comment Utility
I guess it is the data that fill the Script. So you need to split in between some data.
The script comes with creating schema - fill data, create next schema - fills data, if I remember correct.
So split somewhere where creating a new schema is starting...

But of course the script will depend on the tool you used for creating the script.
If the many data is all in one table, then you can not benefit from splitting between inserting data, and have to split somewhere (at end of a line) and edit the ending of the first script and the beginning of the next part (by inserting a copy of the "INSERT INTO... )...

You can use tools as ultraedit to edit those large files.
0
 

Author Comment

by:mrwarejr
Comment Utility
Okay I created the table with the script for schema only and worked perfectly.  I then split the data only script into 3 scrips.  The first ran perfectly, but the second one ran and received the following error.  How do I fix?

Cannot insert explicit value for identity column in table 'netformdetail' when IDENTITY_INSERT is set to OFF.
0
 

Author Comment

by:mrwarejr
Comment Utility
Just found out it was missing the

SET IDENTITY_INSERT [dbo].[netformdetail] ON

In the beginning.  I am still working on getting the data in but splitting the script up seems to be the best route so far.  I will update the question after I am able to test or if I run into more issues.
0
 
LVL 3

Expert Comment

by:sunezapa
Comment Utility
add before the insert:
SET IDENTITY_INSERT netformdetail ON
GO

Open in new window


you will have to do similar for other tables that have Identity column, if you want to keep the values already created in the Identity fields.
You may do it by Replace some text in the script, and add the sentences - BUT you have to change the TABLENAME to the actual tablenames for inserting...
Don't panic - just enjoy-  when all the job is suddenly done :-)
0
 

Author Closing Comment

by:mrwarejr
Comment Utility
Appreciate the help from both of you.  Splitting the script into multiple scripts worked perfectly.  I actually had to split it into 3 scripts to get it to work.  Once again thanks for all you help.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now