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.
- 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.
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.
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
-- *** 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
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'
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
-- Wrap log and truncate it.
DECLARE @Counter INT,
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
-- 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.
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'
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
After the log shrinks:
- Perform a full database backup of the master database.
- 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.
For additional information, click the article numbers below
to view the articles in the Microsoft Knowledge Base:
INF: Causes of SQL Transaction Log Filling Up
INFO: Reasons Why SQL Transaction Log Is Not Being Truncated
PRB: Running Out of Log Space When Running Large Bulk Loads
PRB: Transaction Log Partially Truncated
SQL Server Books Online; topics: "Transaction Log Physical Architecture"; "Optimizing Transaction Log Performance"
Article ID: 256650 - Last Review: June 22, 2014 - Revision: 2.0
- Microsoft SQL Server 7.0 Standard Edition
|kbsqlsetup kbinfo kbsqlserv kbsqlserv700 KB256650|