December 17, 2007 at 2:52 pm
It "Depends". What type of backup and what recovery model is the database using?
A full backup and differential backup do not truncate the transaction log. Enough of the transaction log is backed up during these backups to provide a consistant backup.
The transaction log is truncated (when a database is in bulk-logged of full recovery model) when a transaction log backup is completed.
😎
December 17, 2007 at 4:06 pm
We're using Full for the recovery model in SQL Server 2005. So, does that mean that the log file won't be truncated when a backup occurs?
In our test database server we have all databases' recovery models set to Simple. Would those get truncated after a backup?
Kindest Regards, Rod Connect with me on LinkedIn.
December 17, 2007 at 4:11 pm
Simple recovery model, the transaction log is truncated when the data is checkpointed. In full recovery model, the transaction log is only truncated when a transaction log backup is completed. The transaction log is not truncated by either a full or differential backup.
Books On Line covers this pretty well if you need to read more about the recovery models and backups.
😎
December 17, 2007 at 6:29 pm
If your database is set to FULL recovery mode - you MUST do transaction log backups. If you don't, your transaction log will continue to grow until there is no room left for it to expand. You can't just shrink the tlog file either, it has to be backed up.
-SQLBill
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply