BCP HUGE TEXT FILE
-
I have a 5.6 gigabyte text file that holds all the data for one very large table. It has more than 50 million rows, maybe as many as 80 million rows. Before bcp'ing this data, I backup the log with truncate only and shrink the database, whose growth rate is 50% for both data and log files. I then begin to bcp in the text data, which never seems to make it further than about 27,000,000 rows. Anyone have any ideas? Thanks for the suggestions.
-
Sounds to me that your log and data files are trying to increase by 50%, but there is no more physical space to get.
Either increase your physical disk space, or import the file in batches periodically truncating and shrinking the log file. Think about putting it in simple mode before you run the bcp. Will save you some more space.
------------------------------
The Users are always right - when I'm not wrong! -
What batch size are you using for the import? You could set it at say 1,000,000 rows and put the database in simple recovery mode. Then for each 1,000,000 rows that are loaded you'll get a checkpoint that'll empty the log.
You should also make sure that no-one else is using the database while you've got the recovery mode set to simple. Once a checkpoint occurs you won't be able to recover any lost transactions.
--------------------
Colt 45 - the original point and click interface
-
Why are you shrinking the database before bcp'ing the data, when you know you are going to need more space you should actually be increasing the size of db before.
Dynamically increasing the DB size during bcp will cause resource competition between bcp and the alter database when it happens.
Also, depending on the data row size try using a smaller batch size may be 100,000 will be a good starting reference.
-
Are there any indexes present ?
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply