April 12, 2007 at 5:34 am
I have a text file(CSV) which have a more than 12,00,00,000 records(phone number)
After importing the records from the text file to a new SQL Server database the transaction log file become more than 40 GB while the database file in only 4 GB
Why this is happening and what I have to do to resrict the growth of the transaction log file to a reasonable size.
Thanks in advance.
April 12, 2007 at 7:31 am
The log stores all your transactions, which could be more than the data size, depending on how things are loaded.
If this was a one time, or rare thing, you can switch to simple more and import the files in a multiple batches, that way the log will clear as you go. If it's a regular thing, I'd run periodic log backups while importing, and still use multiple batches.
April 23, 2007 at 3:29 pm
Are you restricting the 'import' by batchsize ? If not then you should probably look into a 'batchsize' of around 100,000 rows. If you do not do this then the entire import is one HUGE transaction which explains the very large transaction log. In conjunction with this if your database is in full recovery mode frequent transaction log backups are needed, possibly every 10 minutes. Only a bit of testing will tell.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
April 25, 2007 at 1:27 am
try to change DB Recovery Model for Simple. or use special utility to load data command prompt utility "bcp".
Or use BULK INSERT
BULK INSERT are not operation which writes infomration about each transaction to log file, it only writes that such statement was executed!!!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply