March 18, 2008 at 2:08 am
Hello,
I have a new database which I have created for a specific, large, data load exercise. I don't want any of the transactions logged as I'm short of disk space and I can always re-run the data load if I have problems. Can anyone tell me how I prevent SQLServer 2000 from filling up the log file?
My DBA is away on a 2 week holiday, so I can't turn to him for help.
Regards
March 18, 2008 at 2:12 am
Hello,
you have to change database recovery model to simple. Select the database and by right-clicking and selecting options you can change the recovery model.
By default the recovery model of a database is set to full after its creation.
PSA
March 18, 2008 at 2:36 am
March 18, 2008 at 9:27 am
If you read the articles suggested by Anirban Paul, you'll understand that you can't eliminate the use of transaction logs in SQL Server. You should use the simple recovery model, as PSA suggested, so the t-log will be truncated on checkpoint and use user-defined transactions to load the data in manageable chunks so the t-log doesn't fill up.
Greg
March 18, 2008 at 9:54 am
The big thing that Greg mentioned is to load in chunks, or reduce the batch size. If you load with BCP, there's a setting to commit after xxx rows, same for most DTS tasks as well. This will allow the transaction log to clear after those rows instead of requiring the entire load to be a single transaction, which would mean that your recovery model doesn't matter.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply