Transaction Log Truncation
In response to the editorial from Friday one reader asks for a difinitive answer on how to keep a transaction log from growing in a fully logged database.
Leon Writes:
Hoping you can settle an discussion we are having amongst a bunch of seasoned dba’s and could point me to a respected article about it that would show that one side or the other is accurate.
One side: Full recovery mode does not need tlog backups; a full backup is enough to recover the space in the tlog so that it won’t grow endlessly. The full backup will truncate the logs after the backup and allow backed up virtual logs to be reused rather than grow the log.
Other side: If your db is in full recovery mode you need to take full and tlog backups so that the log doesn’t grow continuously and allow reuse of virtual logs that have been backed up.
Paul Randal is fully qualified to answer this question without ambiguity:
In his editorial in TechNet Magazine (http://lab.technet.microsoft.com/en-us/magazine/dd392031) he writes:
There are two common issues that can prevent log truncation:
- A long-running active transaction. The entire transaction log since the first log record from the oldest active transaction can never be truncated until that transaction commits or aborts.
- Switching to the FULL recovery model, taking a full backup, and then never taking any log backups. The entire transaction log will remain active, waiting to be backed up by a log backup.
In earlier versions of SQL Server, there was the ability to truncate the transaction log…
DUMP TRAN [Database Name] WITH TRUNCATE_ONLY
This was dropped in SQL Server 2005. However, you note that you are still doing a transaction log dump in the syntax I provided. NOT a full backup.
I believe a full backup in SQL Server 2000 and earlier may have truncated the transaction log.
In SQL Server 2005 and later, the transaction log is a running series of backups…a chain it is called. The chain is never broken. The difference is that you don’t have to go back to the first backup and restore each transaction log in the chain. In this case, full backups may be used at any point. If you wish, you can then apply any transaction log in the chain occurring after that full or incremental backup.
For example, with a full backup daily. And a transaction backup hourly and a restoration event on Friday you could do any of the following.
1) Restore Monday Full followed by each transaction log backup occurring from Monday thru Friday
2) Restore Tuesday Full followed by each transcation log backup occurring from Tues thru Friday
3) …
In short, Full backups are not integral to transaction logs…they simply allow you to skip some transaction logs over time.
The biggest thing to note is this…
On a fully logged database, ONLY TRANSACTION LOG BACKUPS RELEASE SPACE IN THE TRANSACTION LOG FILE(S).
Hope this helps…I know Pauls article helped me a LOT.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Using Nulls in DB2
A null represents missing or unknown information at the column level. If a column “value” can be null, it can mean one of two things: the attribute is not applicable for certain occurrences of the entity, or the attribute applies to all entity occurrences, but the information may not always be known. Of course, it could be a combination of these two situations, too.
Featured White Paper(s)
Key Management in the Multi-Platform Environment
Written by Townsend Security
This White Paper discusses the challenges … (read more)
$$SWYNK$$
Featured Article(s)
Using Nulls in DB2
A null represents missing or unknown information at the column level. If a column “value” can be null, it can mean one of two things: the attribute is not applicable for certain occurrences of the entity, or the attribute applies to all entity occurrences, but the information may not always be known. Of course, it could be a combination of these two situations, too.
Featured White Paper(s)
How to Use SQL Server’s Extended Events and Notifications to Proactively Resolve Performance Issues
read more)