• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1069
  • Last Modified:

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)
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
MaxC07
Asked:
MaxC07
1 Solution
 
ibrusettCommented:
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
 
MaxC07Author 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.

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now