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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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