[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL 2005 - Export / Import - Wizard get an ERROR   ( A buffer failed while allocating  ...)

Posted on 2008-06-14
2
Medium Priority
?
1,067 Views
Last Modified: 2013-11-30
I tried to export all data from a DB to a new one on the same server.
My steps:
Create DB
Create tableschema
create users
set loggin from new db to simple
start export / import wizard
select all needed table ( not the views)
start

The DB has around 70 GB, one maintable has around 50 GB.

If I copy this table ( alone ore in compination with all others) the wizard  errors: ( also see reportfile in attachment)
* ERROR : errorCode=-1073450990 description=A buffer failed while allocating 10485760 bytes

First I increased the pagefile.
After a short time I found some articles via Goggle regarding: DefaultMaxBufferSize

1) How can a set this parameter, and is /are there other parameters to set ?
2) Would this parameterssettings a way of working that leads to the desired results ?


br









Report1.txt
Report2.txt
0
Comment
Question by:MaxC07
[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
2 Comments
 
LVL 3

Expert Comment

by:ibrusett
ID: 21786592
Did you use the /3Gb switch to boot.ini to make SQL server use all 8 GB of Memory?
The parameter you are looking for can be the default buffer size: default value is ~10Mb but changing it shouldn't affect your process...

I've got this suggestion from Michael Entin at MSFT for a similar issue:
"If you can, split the package (if it has multiple data flows) into sub-packages and execute them with out-of-process option in Execute Package Task (so that each sub-package gets its own VM space)."

HTH
0
 

Accepted Solution

by:
MaxC07 earned 0 total points
ID: 21802187
Sorry for my late answer.

In normal use the 3GB-switch isn't set but I also tried to export/import the table while 3GB is on.
--> Same result :-(

Yesterday I tried to export the table with bcp --> seems this way is ok
At this time I try to import it to the new DB with bcp.

I use following command:
to export:
bcp [DB_Name].[dbo].[Tablename] out D:\test.txt -U sa

to import:
bcp [DB_Name_new].[dbo].[Tablename] in D:\test.txt -U sa

BCP-Tool ask at th beginn from the export/improt for some parameter from the table. I take the proposed values.

Questions:

1) Are there some more parameters to set on BCP-Toolto do the export/import right
2) It is ok to use the BCP-recommantation-values ?

br




0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

650 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