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
- The script in step 4 processes every database on the instance. Run it for one database at a time, and only after step 3 when the reason from step 1 is
LOG_BACKUP. - A log backup taken outside the regular backup schedule changes the log backup chain. Restores then need the log backups in order, and a third-party backup tool that also backs up the log can conflict with them.
- Shrinking moves pages and leaves the file fragmented. Avoid repeated shrink and grow cycles, and size the file for the workload.
DBCC LOGis an undocumented command, not supported by Microsoft. Use it only for diagnosis.- The tempdb step clears the plan and buffer caches for the whole instance, which affects all sessions. Run it in a maintenance window.
- The tempdb file names, and the target size of 8192 MB (8 GB) per file, are examples. Adapt them to your instance.
DBCC SHRINKFILEtargets are in MB.