INF: How to Shrink the SQL Server 7.0 Transaction Log

Article translations Article translations
Article ID: 256650 - View products that this article applies to.
Expand all | Collapse all

On This Page

Summary

There are some common reasons why a transaction log might not shrink when you use the DBCC SHRINKFILE or DBCC SHRINKDATABASE command. The SQL Server Books Online topics "DBCC SHRINKFILE" and "DBCC SHRINKDATABASE" provide detailed information, but a brief summary follows.

More information

  • In Microsoft SQL Server 7.0, the SHRINKFILE and SHRINKDATABASE commands set a target size for shrinking. Each log file is marked by these commands, but it is actually a log backup or log truncation that tries to shrink the files. Therefore, after you use the SHRINKFILE or SHRINKDATABASE command you must also issue a command that truncates the log before there is any chance that it will shrink.
  • You cannot shrink a log to a size smaller than what is allowed by these criteria:

    • To shrink a log smaller than its original size you must shrink individual files with DBCC SHRINKFILE. You cannot use DBCC SHRINKDATABASE to shrink a log to a size smaller than its original or explicitly defined size. The original size is defined as the size of the log due to CREATE DATABASE plus any explicit ALTER DATABASE commands. The original size does not include automatic growth of the log.

    • The physical log file can never be smaller than the amount of space currently used within the log file. You can use the DBCC SQLPERF (LOGSPACE) command to monitor the amount of space used.

    • The current size of the model database's log is the minimum size for any database's log on that server. By default, the model database's log is less than 1 MB.

    • Because a log can be shrunk only to a virtual log file (VLF) boundary, it is not possible to shrink a log file to a size smaller than a VLF even if the space is not being used. Likewise, if a portion of a VLF is in use you cannot shrink any of the space in that VLF. For more information, see the "Virtual Log Files" and "Transaction Log Physical Architecture" topics in SQL Server Books Online.


  • The transaction log is a "wrap-around" log. This means that at any given time there may be VLFs with "free" or "reusable" space at the beginning, middle, and/or end of the log. To shrink the log there must be "free" space at the end of the log, not just free space anywhere in the log. Also, you can only shrink whole VLFs. To shrink the transaction log the VLFs at the end of the log file must be inactive and truncated. For more detailed information, refer to the "Truncating the Transaction Log" topic in SQL Server Books Online.
Here are a few things to keep in mind:
  • Always perform system database and user database backups before and after you make changes that affect the system. DBCC SHRINKFILE and DBCC SHRINKDATABASE are not logged operations, and running them invalidates further transaction log backups. You must make a full database backup after you run either the DBCC SHRINKFILE or the DBCC SHRINKDATABASE commands.

  • Make sure that there are no backups scheduled to occur during the time the shrink is supposed to occur.

  • Make sure that there are no old, long-running, or unreplicated transactions. To do so, use code similar to:
    DBCC OPENTRAN (database_name)
    					
  • Run the DBCC SHRINKFILE or DBCC SHRINKDATABASE command to mark a shrinkpoint. DBCC SHRINKFILE and DBCC SHRINKDATABASE permissions default to members of the sysadmin fixed server role or the db_owner fixed database role, and are not transferable. For information about the differences between these commands, refer to the following topics in SQL Books Online (note the different parameters):

    DBCC SHRINKFILE     (file_name, target_size)
    DBCC SHRINKDATABASE (database_name, target_percent)
    					
  • Create some dummy transactions to make the log wrap around and then issue a BACKUP command to truncate the log. The BACKUP statement is what actually attempts to shrink the log to the marked target size.

    Here is a sample of how to create a dummy transactions that wraps the log for a single logical log file and causes it to truncate, allowing for shrinkage. Modify the sample as needed for your environment.
       SET NOCOUNT ON
       DECLARE @LogicalFileName sysname,
               @MaxMinutes INT,
               @NewSize INT
    
       -- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
       USE     [Test DB]              -- This is the name of the database
                                      -- for which the log will be shrunk.
       SELECT  @LogicalFileName = 'Test DB Log',  -- Use sp_helpfile to 
          -- identify the logical file 
          -- name that you want to shrink.
               @MaxMinutes = 10,      -- Limit on time allowed to wrap log.
               @NewSize    = 10       -- in MB
    
       -- Setup / initialize
       DECLARE @OriginalSize int
       SELECT @OriginalSize = size -- in 8K pages
         FROM sysfiles
         WHERE name = @LogicalFileName
       SELECT 'Original Size of ' + db_name() + ' LOG is ' + 
               CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + 
               CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
         FROM sysfiles
         WHERE name = @LogicalFileName
    
       CREATE TABLE DummyTrans
         (DummyColumn char (8000) not null)
    
       -- Wrap log and truncate it.
       DECLARE @Counter   INT,
               @StartTime DATETIME,
               @TruncLog  VARCHAR(255)
       SELECT  @StartTime = GETDATE(),
               @TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
       -- Try an initial shrink.
       DBCC SHRINKFILE (@LogicalFileName, @NewSize)
    
       EXEC (@TruncLog)
    
       -- Wrap the log if necessary.
       WHILE     @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
             AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  -- the log has not shrunk    
             AND (@OriginalSize * 8 /1024) > @NewSize  -- The value passed in for new size is smaller than the current size.
         BEGIN -- Outer loop.
           SELECT @Counter = 0
           WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
             BEGIN -- update
               INSERT DummyTrans VALUES ('Fill Log')  -- Because it is a char field it inserts 8000 bytes.
               DELETE DummyTrans
               SELECT @Counter = @Counter + 1
             END   -- update
           EXEC (@TruncLog)  -- See if a trunc of the log shrinks it.
         END   -- outer loop
       SELECT 'Final Size of ' + db_name() + ' LOG is ' +
               CONVERT(VARCHAR(30),size) + ' 8K pages or ' + 
               CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
         FROM sysfiles 
         WHERE name = @LogicalFileName
       DROP TABLE DummyTrans
       PRINT '*** Perform a full database backup ***'
       SET NOCOUNT OFF
    					
    Check to see if the log has shrunk from its original size.Repeat the preceding steps if necessary. If the log is not shrinking, re-check the summary at the top of the article to see if you are encountering any of the common issues with shrinking the log.
After the log shrinks:
  1. Perform a full database backup of the master database.
  2. Perform a full database backup of the user database. This is necessary because the SHRINK command is not logged and invalidates future transaction log backups unless a full database backup is completed.
To determine why the log is growing so big in the first place, you can check for open transactions, long running transactions, unreplicated transactions, or transactions that touch a lot of data.

REFERENCES

For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
110139 INF: Causes of SQL Transaction Log Filling Up
62866 INFO: Reasons Why SQL Transaction Log Is Not Being Truncated
66057 PRB: Running Out of Log Space When Running Large Bulk Loads
80629 PRB: Transaction Log Partially Truncated
SQL Server Books Online; topics: "Transaction Log Physical Architecture"; "Optimizing Transaction Log Performance"

Properties

Article ID: 256650 - Last Review: June 22, 2014 - Revision: 2.0
Applies to
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbsqlsetup kbinfo kbsqlserv kbsqlserv700 KB256650

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com