LLI Wiki — My Digital Knowledge Base

Shrink a transaction log file

Steps to find why a transaction log cannot be reused, back up the log, shrink the log file and shrink tempdb, with the scripts and their cautions.

A transaction log file can stay large after the work that filled it is finished. Before shrinking it, find out why its space cannot be reused, free that space with a log backup, then shrink the file. Read the Notes before running the shrink scripts.

1. Check why the log cannot be reused

SELECT d.name, d.log_reuse_wait_desc
FROM sys.databases AS d;
log_reuse_wait_desc Meaning
NOTHING No blocking reason: the log space can be reused, so the file can be shrunk
OLDEST_PAGE Problem with the checkpoint
LOG_BACKUP A log backup is needed before the space can be reused; shrinking is not possible until then

2. Look at the open transactions and the log space

DBCC OPENTRAN;
DBCC LOG ('AppDb', 4);

EXEC sp_spaceused;
DBCC SQLPERF (LOGSPACE);

3. Take a log backup

This example uses the DatabaseBackup procedure of the Ola Hallengren SQL Server maintenance solution.

EXECUTE master.dbo.DatabaseBackup
    @Databases = 'USER_DATABASES',
    @Directory = 'D:\Backup',
    @BackupType = 'LOG',
    @ChangeBackupType = 'Y';

4. Shrink the log files of every database

The script shrinks the log file of each database to 1 MB. It runs a checkpoint before each shrink.

DECLARE @Name AS VARCHAR(100)
DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @ressult varchar(1000);
declare @size int;

DECLARE dbCur CURSOR FOR
SELECT name FROM SYS.databases
OPEN dbCur
FETCH NEXT FROM dbCur
INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @ParmDefinition = N'@database varchar(1000), @size int OUTPUT, @ressult varchar(1000) OUTPUT';
    SET @SQLString = 'use [' + @Name + '];
                      CHECKPOINT;
                      select @ressult=name,@size=size from sys.database_files where type_desc=''log''
                      select name,size from sys.database_files where type_desc=''log''
                      CHECKPOINT;
                      '

    print @SQLString
    begin try
        EXEC sp_executesql @SQLString, @ParmDefinition, @database=@Name, @size = @size OUTPUT, @ressult = @ressult OUTPUT
        print @ressult
        SET @SQLString = 'use [' + @Name + '];
                          DBCC SHRINKFILE (['+ @ressult +'], 1)
                          CHECKPOINT;'
        print @SQLString
        EXEC sp_executesql @SQLString, @ParmDefinition, @database=@Name, @size = @size OUTPUT, @ressult = @ressult OUTPUT
        print (@size/1024)
    End try
    begin catch
        print 'test'
    end catch
    --exec sp_executesql 'use'
    FETCH NEXT FROM dbCur
    INTO @Name
END
CLOSE dbCur
DEALLOCATE dbCur

5. Follow the shrink progress

SELECT
    a.session_id,
    command,
    b.text,
    percent_complete,
    done_in_minutes = a.estimated_completion_time / 1000 / 60,
    min_in_progress = DATEDIFF(MI, a.start_time, DATEADD(ms, a.estimated_completion_time, GETDATE())),
    a.start_time,
    estimated_completion_time = DATEADD(ms, a.estimated_completion_time, GETDATE())
FROM sys.dm_exec_requests a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
WHERE command LIKE '%dbcc%';

6. Shrink tempdb

The following steps clear the caches, shrink the tempdb data files, then shrink the database and each file to a target size in MB.

USE tempdb
GO
CHECKPOINT
GO
DBCC FREEPROCCACHE -- clean cache
GO
DBCC DROPCLEANBUFFERS -- clean buffers
GO
DBCC FREESYSTEMCACHE ('ALL') -- clean system cache
GO
DBCC FREESESSIONCACHE -- clean session cache
GO

DECLARE @name VARCHAR(MAX);
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.master_files
WHERE database_id = DB_ID('tempdb') AND type = 0;

OPEN db_cursor;

FETCH NEXT FROM db_cursor INTO @name;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @name;
    DBCC SHRINKFILE(@name, 0); -- shrink the data file
    FETCH NEXT FROM db_cursor INTO @name;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;

USE [tempdb]
GO
DBCC SHRINKDATABASE(N'tempdb', 3)
GO

USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev', 8192)
GO
USE [tempdb]
GO
DBCC SHRINKFILE (N'temp2', 8192)
GO
USE [tempdb]
GO
DBCC SHRINKFILE (N'temp3', 8192)
GO
USE [tempdb]
GO
DBCC SHRINKFILE (N'temp4', 8192)
GO
USE [tempdb]
GO
DBCC SHRINKFILE (N'temp5', 8192)
GO
USE [tempdb]
GO
DBCC SHRINKFILE (N'temp6', 8192)
GO
USE [tempdb]
GO
DBCC SHRINKFILE (N'temp7', 8192)
GO
USE [tempdb]
GO
DBCC SHRINKFILE (N'temp8', 8192)
GO

Notes

Mastodon LLIMastodon SocialFosstodon