Truncate Log Files

Question

Truncate Log Files

peacesells

SSCrazy

Points: 2882
More actions
May 2, 2012 at 11:16 am

#273563

I am using 2008 R2. The DB is in full recovery mode.The transaction log is full (99.99%) and now I can't take a back up in the same recovery mode. What i did was switched my DB to simple recovery and take a full back up. What I want to do is truncate my log but when I google/bing around, looks like its more complicated then it appears. Please advise on how to truncate the log?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

rraja Default port Points: 1486 More actions · Answer 1

When you changed the database into simple recovery by default check point flushed the committed logs. so now its not require to take a log backup.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

While the database was using the full recovery model, were you taking regularly scheduled transaction log backups? If not, that is why your transaction log kept growing. The only way to manage the transaction log when using the BULK_LOGGED and FULL recovery models is to run regular transaction log backups.

You can use DBCC SHRINKFILE to shrink your t-log. This is not the same thing as truncating the transaction log.

Please take the time to read the last article I refereance below in my signature block.

peacesells SSCrazy Points: 2882 More actions · Answer 3

Lynn Pettis (5/2/2012)
The only way to manage the transaction log when using the BULK_LOGGED and FULL recovery models is to run regular transaction log backups.

Thanks Lynn. School me a little more here. How is running a regular back up reduce the log files. Does it clear out from log files whatever is backed up?

Obviously, since this is not live machine, i didn't take up the backup and now the log is filled up to the fullest and i am trying to shrinking the database and its been running for quite a while now...waiting patiently.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

NevaMind (5/2/2012)
Lynn Pettis (5/2/2012)
The only way to manage the transaction log when using the BULK_LOGGED and FULL recovery models is to run regular transaction log backups.
Thanks Lynn. School me a little more here. How is running a regular back up reduce the log files. Does it clear out from log files whatever is backed up?
Obviously, since this is not live machine, i didn't take up the backup and now the log is filled up to the fullest and i am trying to shrinking the database and its been running for quite a while now...waiting patiently.

Have you read the last article I reference below in my signature block? It will help answer your questions.

The quick answer, full and differential backups do not truncate the transaction log. This is only done when you run transaction log backups when the database is using the BULK_LOGGED and FULL recovery models. You can't run a transaction log backup when a database is using the SIMPLE recovery model.