?
Solved

Out of Memory Error on SQL

Posted on 2011-02-15
14
Medium Priority
?
781 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 2
  • +1
14 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 34903026
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
ID: 34903064
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 1000 total points
ID: 34905884
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:mrwarejr
ID: 34907670
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
ID: 34907762
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
ID: 34907781
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
ID: 34907798
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
 
LVL 3

Assisted Solution

by:sunezapa
sunezapa earned 1000 total points
ID: 34907976
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
ID: 34907997
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
ID: 34908319
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
ID: 34908918
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
ID: 34909166
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
ID: 34909170
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
ID: 34909593
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

770 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