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

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)

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 ?


Who is Participating?
MaxC07Connect With a Mentor Author Commented:
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.


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 ?


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)."

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.