BCP Export of huge amount of data problem

BCP Export of huge amount of data problem

Post by THVraW5 » Fri, 15 Jan 2010 19:59:01


Hello,
we have a problem with exporting of huge amount of data (about 100mil.
records) from DB to .csv file. We are running following command:

bcp "SELECT * from MY_VIEW UNION SELECT * FROM MY_VIEW_HEADER ORDER BY ID
DESC" queryout MyData.csv -c -t ; -o MyDataExport.log -a 8192 -S
DbServer\MSSQL -T

And after some time following error message is displayed:
[Microsoft][SQL Native Client][SQL Server]The transaction log for database
'tempdb' is full. To find out why space in the log cannot be reused, see the
log_reuse_wait_desc column in sys.databases.

Could you please help us what should be the problem and how we can fix it ?

Thank you very much for any response.

Best regards,
Richard
 
 
 

BCP Export of huge amount of data problem

Post by Erland Som » Sat, 16 Jan 2010 07:38:14

Lukino ( XXXX@XXXXX.COM ) writes:

Did you look at sys.databases.log_reuse_wait?

Normally, when a database file does not have room for what has to go
into it, SQL Server expands the file according to its autogrow settings.
But if the settings calls for an increase for which there is no room
left for on the disk, the grow will fail. Likewise will file be reported
as full, if autogrow has been turned off.

Tempdb is often used by SQL Server for work tables, and other work areas,
such as disk spaces for sort operations that are too large to fit into
memory.

So your action is to find out how big is the log file of your tempdb
database, and what autogrow settings. If needed, see your local hardware
vendor to get more disk.

It may also help to replace UNION with UNION ALL, as UNION implies a
DISTINCT operation. which may be expensive.

--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

Links for SQL Server Books Online:
SQL 2008: http://www.yqcomputer.com/
SQL 2005: http://www.yqcomputer.com/
SQL 2000: http://www.yqcomputer.com/