Readers' Tips and Tricks - 21 Jun 2000

Editor's Note: Share your SQL Server discoveries, comments, problems, solutions, and experiences with products and reach out to other SQL Server Magazine readers. Email your contributions (400 words or less) to [email protected]. Please include your phone number. We edit submissions for style, grammar, and length. If we print your submission, you'll get $50.

Shrinking Active Log Files—Revisited

Christine Chang's Reader to Reader tip, "Shrinking an Active Log File in SQL Server 7.0" (June 2000), can have disastrous consequences for DBAs. DBCC REBUILD_LOG asks the DBA to run DBCC CHECKDB for a very good reason: By running REBUILD_LOG, the DBA might have corrupted a perfectly good database.

SQL Server uses the transaction log to ensure that a database is transactionally and physically consistent (for example, when you're recovering a database after a cold boot of the system). When a DBA runs REBUILD_LOG, the DBA is essentially saying, "I don't want the database to be transactionally or physically consistent if unapplied transactions exist." This course of action isn't wise. A subsequent run of DBCC CHECKDB might suggest a minimum repair level of REPAIR_ALLOW_DATA_LOSS to fix those deliberately generated inconsistencies. Thus, a DBA who runs REBUILD_LOG in emergency-bypass recovery mode might have just made a career-limiting move and caused millions of dollars of data loss.

In addition, if you publish (replicate) the database, a REBUILD_LOG permanently deletes all the database's unread replicated transactions, as stored in the transaction log. So, you must resynchronize the publisher and subscriber. Synchronizing 20GB over a 56Kbps connection, for example, can also be a risky move for a DBA. The prudent action is to run DBCC OPENTRAN before even considering a rebuild of the log. If you find unreplicated transactions, investigate why (e.g., are log-reader and cleanup tasks failing, is the subscriber available?), and fix those problems. Such fixes will shrink the transaction log.

SQL Server Books Online (BOL) discusses why DBCC SHRINKFILE doesn't immediately shrink the database, which was the problem Chang was trying to solve. Make sure you read all the topics that talk about virtual log files. It seems that DBCC LOGINFO is reporting that the last virtual log file is also the active portion of the log file. BOL states that "the end of the log grows through each virtual log file in each physical file before circling back to the first virtual log file in the first physical file" and that "a log file can only be shrunk to a virtual log file boundary." Thus, if the last virtual log file is active, the DBA must make SQL Server circle the active virtual log file back to the first virtual log file in the database.

Knowing this, you can make the SHRINKFILE technique work far more safely by not running REBUILD_LOG. Listing 1 shows a quick script that adds transactions and truncates the log. The script assumes that the database name is 'test_this,' that the DBA has already run DBCC SHRINKFILE or DBCC SHRINKDATABASE, and that the log file has to shrink (per sp_helpdb). The script adds transactions that force the active virtual log file to circle back to the first virtual log file. Then, the previously run DBCC SHRINKFILE can shrink the log file.

Alternatively, if you have only one log file, you can run sp_detach_db, then remove (or rename) the log file. You can then run sp_attach_db, supplying only the primary data file name. SQL Server will rebuild the log file to a minimum size. This technique works no matter how many data files (.mdf and .ndf) you have, as long as all the data files are available. Yet another alternative is to use Data Transformation Services (DTS) to transfer to another database and use the sp_rename_db procedure to change the old database name to something else, then change the new database name to the original name.

DBAs should consider all the alternatives before contemplating DBCC REBUILD_LOG. Running DBCC REBUILD_LOG can cause irretrievable data loss. As Chang notes, this method of shrinking log files is undocumented and unsupported; the consequences of using it explain why. If you do decide to run DBCC REBUILD_LOG, back up the database or copy all data files (the database's and master's) first. These backups and file copies will be your only fallback if you discover DBCC REBUILD_LOG's undocumented, disastrous consequences.

—Bill Hollinshead
[email protected]