Automatic Purge of Transaction Log Data?

Hello,

I have a database that I am setting up in SQL Server 2005. Initially, I am doing very large imports of data. Every time I run an import, I am having the increase the size of my transaction logs, and now they are approaching 2 GB. Should these be purging themselves? I have to keep increasing the max size of the log so that I can get my data in. While this will work for now, it is not a long term solution, because I can see the log size growing quite large and the amount of space on the server obviously isn't infinite. Is there a setting that I can change so they will automatically purge? If not, how do I purge this information myself?

Thanks so much!

Christine

The management of the transaction log depends on the recovery model used in your database. There is a good overview on http://msdn2.microsoft.com/en-us/library/ms189275.aspx about the available recovery models.

With a full recovery model, you will need to backup your transaction log to reclaim its space. With a simple recovery model, the system will do this for you, but there is a greater risk for data loss - be sure to make the right tradeoff for your database!

For the problem of large imports filling up the log space, you might also want to look into switching to bulk-logged recovery for the duration of the imports, although you might not need to do this if the one of the other models works for you after the log is under control (reclaimed by the system or by regular backups).

Another SQL BOL reference that will probably come in handy is Truncating the Transaction Log at http://msdn2.microsoft.com/en-us/library/ms189085.aspx, which explains a bit more about how log truncation works.

|||

Here are some good resources that will help you better understand and control the Transaction Log:

FileSize -How to stop the log file from growing
http://www.support.microsoft.com/?id=873235

FileSize -Log file filling up
http://www.support.microsoft.com/?id=110139

FileSize -Log File Grows too big
http://www.support.microsoft.com/?id=317375

FileSize -Log File issues
http://www.nigelrivett.net/TransactionLogFileGrows_1.html

FileSize -Shrinking Log in SQL Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=272318

And especially, this article about why cautions about reducing the Log file size is worth reading:

FileSize -DB Shrink Issues
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

|||Thank you very much Marcelo and Arnie. I appreciate all of the information you have provided. I will spend this afternoon and tomorrow morning looking into it! :0)