July 31, 2001 at 6:14 am
I need some help understanding what is going on with Truncate Log on Checkpoint. We had an issue recently with some transaction logs growing huge during a large data loading (and unloading and re-loading) which almost filled our disks. All our db's are set with Truncate Log on Checkpoint. From everything I read, the transaction log should be cleared with every checkpoint, right? it doesn't. only if you manually issue a
BACKUP LOG <dbname> WITH TRUNCTATE_ONLY, or perform a db backup with SQL Server do I see the trans log actually get cleared.
Also, we are using Backup Exec for NT v8.5 to backup the databases. This does
not clear the transaction log either. Is this a bug in Backup Exec or simply not possible?
Thanks!
Jay
August 1, 2001 at 8:07 am
I will have to drag out the book to make sure - seems like the checkpoint just marks the log at a position where the log can be reused, but doesnt actually remove anything. Were you running your data load inside a transaction?
Andy
August 1, 2001 at 9:53 am
Jay,
After reading your post and struggling with this on several occasions in the past I did some further reading in BOL and found this in the Checkpoint Topic. I thought this particular piece was rather interesting and applies directly to your scenario. Hope this helps you as much as it did me.
David
A database is in log truncate mode when both these conditions are TRUE:
The database is using the simple recovery model.
One of these events has occurred after the last BACKUP DATABASE statement referencing the database was executed:
·A BACKUP LOG statement referencing the database is executed with either the NO_LOG or TRUNCATE_ONLY clauses.
·A nonlogged operation is performed in the database, such as a nonlogged bulk copy operation or a nonlogged WRITETEXT statement is executed.
·An ALTER DATABASE statement that adds or deletes a file in the database is executed.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 1, 2001 at 11:27 am
Be sure your transaction loading is not a single transaction. the log will not truncate open transactions.
Steve Jones
August 3, 2001 at 7:58 am
The data loads are not within transactions. We are using DTS to load and cleanup data for prep for the data warehouse. Transaction logs are never needed, we simply do full backups each night. If need to restore, we can reload the current day's data easily. The only thing I can think of to control the size of the logs is to have a job set which runs periodically with the command: BACKUP LOG <db> WITH TRUNCATE_ONLY
I find it odd that the terminology used (i.e. Truncate Log on Checkpoint) does not actually do what is stated. And that it is conditional on other factors according to BOL. None of the books I own describe it as such. Perhaps I am still missing something here?
Thanks!
Jay
Edited by - jayharper on 08/03/2001 08:05:40 AM
August 3, 2001 at 1:01 pm
I've run into a not really related problem - but you may find interesting anyway. We use transactional replication a lot, but with relatively huge latencies..30 mins to an hour. When maint jobs run to do index rebuilds, the log grows and grows because (I believe) all those transactions need to be cleared by the log reader before they can be purged. I'm stuck with the option of taking the log reader out of non-continuous mode while maint is running or keeping a LOT of disk space avail. I am working on a compromise where I'll replace the maint wizard with my own code, that way I can just replicate after each index is rebuilt to keep the size down.
I also use a job that fires every 30 mins that backs up logs over x megs for any one db. I have about 200 db's so it's not practical to do log backups every 30 minutes on all of them.
For what its worth!
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply