567
Chapter 21: Backup and Recovery Planning
21
Truncating the Log
Updates and deletes might not increase the size of a data fi le, but to the transaction log
every transaction of any type is simply more data. Left to its own devices, the transaction
log will continue to grow with every data modifi cation.The solution is to back up the inactive portion of the transaction log and then remove
it. By default, backing up the transaction log also truncates the log (refer to
Figure 21-3).BACKUP LOG WITH NO_LOG and BACKUP LOG WITH TRUNCATE_ONLY were discontinued in SQL Server- To truncate the log, either take regular transaction log backups or put the database in simple recovery model.
The Transaction Log and Simple Recovery Model
When the database uses a simple recovery model, the transaction log ensures that
each committed transaction is written to the data file, and that’s it. When SQL Server
performs a checkpoint and the transaction log is truncated, the free space of the
transaction log fluctuates, but the minimum is the size of the active portion of the
transaction log.Under the simple recovery model, performing a manual checkpoint truncates the log and
frees the log space.Truncating the log marks the inactive portion of the log for reuse and does not reduce the physical size of the
transaction log. To reduce the physical size you need to run DBCC SHRINKFILE to manually shrink the log fi le.
There are many DBAs that run the DBCC SHRINKFILE command to shrink the log fi le right after the log backup.
This action is highly discouraged because DBCC SHRINKFILE can cause severe fi le-system fragmentation
because the fi les will likely need to grow again after they have been shrunk and cause performance degradation.
Instead, you must correctly size the transaction log at the time of creation and perform frequent log backups to
keep the size in check.To discover the operation preventing log truncation, use the log_reuse_wait_desc column of the sys
.databases catalog view.c21.indd 567c21.indd 567 7/31/2012 9:22:51 AM7/31/2012 9:22:51 AM
http://www.it-ebooks.info