Solved

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

Posted on 2008-06-14
2
1,056 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
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

747 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

8 Experts available now in Live!

Get 1:1 Help Now