Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

911 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

16 Experts available now in Live!

Get 1:1 Help Now