INF: How to Shrink the SQL Server 7.0 Transaction Log (256650)

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: SQL Server Books Online; topics: "Transaction Log Physical Architecture"; "Optimizing Transaction Log Performance"