Shrink SQL Server Transaction Log for all Databases

Problem

I need to quickly generate T-SQL commands to shrink all SQL Server user database log files or shrink a single database log file back to the original size or as small as possible. Can you provide a script?

Solution

My solution involves creating a T-SQL stored procedure in a SQL Server database called dbo.usp_ShrinkAllLogsExcludeSysDBS that will get a parameter for the database name. The parameter will default to '%' meaning all user databases are in scope if a database is not specified.

The procedures uses a VARCHAR (max) parameter (@TSQLExec) in order to execute the T-SQL shrink statement (or block of statements) dynamically.

The code executed for each user database will contain a CHECKPOINT statement and a DBCC SHRINKFILE statement. Issuing a manual CHECKPOINT statement in the SQL Server database will write the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk and also record the information in the transaction log.

The DBCC SHRINKFILE statement will shrink the current database's specified data or log file size. In this case I will not use any target database file size. If this parameter is not specified, then the DBCC SHRINKFILE statement will reduce the file size to its creation size. I don't use a target size parameter in order to avoid any errors.

The procedure joins sys.databases and sys.master_files system views in order to generate the CHECKPOINT and DBCC SHRINKFILE statement for all the user databases. It filters the system databases (DBID > 4) because these statements are not recommended for the system databases.

SQL Server Shrink Database T-SQL Code

-- ================================================================================= 
-- Author:         Eli Leiba 
-- Create date:    2020-03
-- Procedure Name: dbo.usp_ShrinkAllLogsExcludeSysDBS 
-- Description:    This procedure shrinks all user databases log files or a specific user database log 
-- ================================================================================== 
CREATE PROCEDURE dbo.usp_ShrinkAllLogsExcludeSysDBS (@dbname SYSNAME = '%')
AS
BEGIN
 
DECLARE @TSQLExec VARCHAR (MAX) = '';
SET NOCOUNT ON;
 
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
       DROP TABLE #temp
CREATE TABLE #temp (dbname sysname, dbid int, logFileSizeBeforeMB decimal(15,2), logFileSizeAfterMB decimal(15,2));
WITH fs
AS
(
    SELECT database_id, TYPE, SIZE * 8.0 / 1024 SIZE
    FROM sys.master_files
)
INSERT INTO #temp (dbname, dbid, logFileSizeBeforeMB)
SELECT 
    name, database_id,
    (SELECT SUM(SIZE) FROM fs WHERE TYPE = 1 AND fs.database_id = db.database_id) LogFileSizeMB
FROM sys.databases db
WHERE database_id > 4
AND NAME LIKE @dbname;
 
SELECT @TSQLExec = CONCAT (
  @TSQLExec,
  'USE [',
  d.NAME,
  ']; CHECKPOINT; DBCC SHRINKFILE ([',
  f.NAME + ']) with no_infomsgs;' ,
  Char (13),Char (10))
FROM sys.databases d,
     sys.master_files f
WHERE d.database_id = f.database_id
      AND d.database_id > 4
      AND f.type = 1
      AND d.NAME LIKE @dbname;
PRINT @TSQLExec;
EXEC (@TSQLExec);
 
WITH fs
AS
(
    SELECT database_id, TYPE, SIZE * 8.0 / 1024 SIZE
    FROM sys.master_files
)
UPDATE a
set a.logFileSizeAfterMB = (SELECT SUM(SIZE) FROM fs WHERE TYPE = 1 AND fs.database_id = db.database_id)
FROM #temp a
inner join sys.databases db on a.dbid = db.database_id
WHERE database_id > 4
AND NAME LIKE @dbname
 
SELECT * FROM #temp ORDER BY dbname
SET NOCOUNT OFF;
END;
GO

Here are examples for executing this stored procedure:

-- shrink database log for all user databases
USE master
GO
EXEC dbo.usp_ShrinkAllLogsExcludeSysDBS 
GO
-- shrink database log for just database Test2
USE master
GO
EXEC dbo.usp_ShrinkAllLogsExcludeSysDBS 'Test2'
GO

Here are the results for running against all databases. You can see for database Test2 the log went from 8072MB to 8MB, but the others didn’t change because it was unable to shrink the log file any further.

Next Steps

You can create and compile this simple procedure in your master database and use it as a simple SQL tool for shrinking all the user database log files or shrinking a specific user database log.
The procedure was tested for SQL Server 2014 and 2017, but should work for SQL Server 2005 and later.

Eli Leiba

Eli Leiba is a senior application DBA at Israel Electric Company, a teacher at Microsoft CTEC, and a senior database consultant. With 19 years of experience working with both SQL Server and Oracle RDBMS. He is certified in Oracle and SQL Server database administration and implementation and has a B.S. in Computer Science. He can be reached at: iecdba@hotmail.com.

MSSQLTips Awards: Rising Star (50+ tips) – 2019 | Author of the Year Contender – 2016-2019

8 Comments

Ronald
April 5, 2024 / 7:40 am Reply
Hello,
I added in the several tips and questions and some as parameters, and (hopefully) improved the logic somewhat ( putting everything in a temp table and then not actually using it seems superfluous), so heregoes:
USE [master]
GO
/****** Object: StoredProcedure [dbo].[usp_ShrinkAllLogsExcludeSysDBSv4] Script Date: 5-4-2024 13:30:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =================================================================================
— Author: Eli Leiba
— Create date: 2020-03
— Procedure Name: dbo.usp_ShrinkAllLogsExcludeSysDBS
— Description: This procedure shrinks all user databases log files or a specific user database log
— ==================================================================================
ALTER PROCEDURE [dbo].[usp_ShrinkAllLogsExcludeSysDBSv4] (
@dbname SYSNAME = ‘%’,
@ExcludeOffline bit = 0,
@ExcludeScndReplica bit = 1,
@DoTruncateOnly bit = 0)
AS
BEGIN
DECLARE @TSQLExec VARCHAR (MAX) = ”;
DECLARE @TruncateOnlyTxt varchar(30)=”;
SET NOCOUNT ON;
SET @TruncateOnlyTxt=IIF(@DoTruncateOnly=0, ”,’,0,TRUNCATEONLY’);
IF OBJECT_ID(‘tempdb..#Temp’) IS NOT NULL
DROP TABLE #temp
CREATE TABLE
#temp (
dbname sysname
, dbid int
, logName sysname
, isOffline bit
, logFileSizeBeforeMB decimal(15,2)
, TotallogFilesSizeBeforeMB decimal(15,2)
, logFileSizeAfterMB decimal(15,2) NULL
, TotallogFileSizeAfterMB decimal(15,2) NULL
, isPreferredReplica INT<br>);
WITH fs
AS
(
SELECT
database_id
, TYPE
, NAME AS LOGNAME
, SIZE * 8.0 / 1024 SIZE
FROM sys.master_files
WHERE type=1
)
INSERT INTO #temp (
dbname
, dbid
, logName
, isOffline
, logFileSizeBeforeMB
, TotallogFilesSizeBeforeMB
, isPreferredReplica)
SELECT
db.name
, db.database_id
, fs.LOGNAME
, db.state
, fs.SIZE logFileSizeBeforeMB
, (SELECT SUM(SIZE) FROM fs WHERE fs.database_id = db.database_id) TotallogFilesSizeBeforeMB
, x.is_preferred_replica
FROM sys.databases db
JOIN fs ON fs.database_id = db.database_id
OUTER APPLY (
SELECT [master].sys.fn_hadr_backup_is_preferred_replica( db_name ( db.database_id)) is_preferred_replica) x
WHERE db.database_id > 4 AND x.is_preferred_replica = 1
AND NAME LIKE @dbname;
SELECT @TSQLExec =
@TSQLExec + ‘USE [‘ + dbNAME + ‘]; CHECKPOINT; DBCC SHRINKFILE ([‘ + LOGNAME + ‘] ‘ + @TruncateOnlyTxt +’ ) with no_infomsgs;’ + Char(13) + Char(10)
FROM #temp
WHERE isOffline=@ExcludeOffline
AND isPreferredReplica=@ExcludeScndReplica
AND dbNAME LIKE @dbname;
PRINT @TSQLExec;
EXEC (@TSQLExec);
WITH fs
AS
(
SELECT
database_id
, TYPE
, NAME AS LOGNAME
, SIZE * 8.0 / 1024 SIZE
FROM sys.master_files
WHERE type=1
)
UPDATE a
set a.TotallogFileSizeAfterMB = (SELECT SUM(SIZE) FROM fs WHERE TYPE = 1 AND fs.database_id = db.database_id)
, a.logFileSizeAfterMB = (SELECT SIZE FROM fs WHERE TYPE = 1 AND fs.database_id = db.database_id)
FROM #temp a
inner join sys.databases db on a.dbid = db.database_id
WHERE database_id > 4
AND NAME LIKE @dbname
SELECT * FROM #temp ORDER BY dbname
SET NOCOUNT OFF;
END;
GO
Ronald
April 5, 2024 / 5:17 am Reply
@Jayesh, what does your comment actually add or change from the original? Is it that it uses a + in stead of concat?
Jayesh
February 7, 2024 / 12:09 am Reply
USE [master]
GO
–OWNER=Jayesh Mali
–MSSQLDBA
–For SQL server ****** Object: StoredProcedure [dbo].[usp_ShrinkAllLogsExcludeSysDBS] Script Date: 07-02-2024 10:12:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[usp_ShrinkAllLogsExcludeSysDBS] (@dbname SYSNAME = ‘%’)
AS
BEGIN
DECLARE @TSQLExec VARCHAR (MAX) = ”;
SET NOCOUNT ON;
IF OBJECT_ID(‘tempdb..#Temp’) IS NOT NULL
DROP TABLE #temp
CREATE TABLE #temp (dbname sysname, dbid int, logFileSizeBeforeMB decimal(15,2), logFileSizeAfterMB decimal(15,2));
WITH fs
AS
(
SELECT database_id, TYPE, SIZE * 8.0 / 1024 SIZE
FROM sys.master_files
)
INSERT INTO #temp (dbname, dbid, logFileSizeBeforeMB)
SELECT
name, database_id,
(SELECT SUM(SIZE) FROM fs WHERE TYPE = 1 AND fs.database_id = db.database_id) LogFileSizeMB
FROM sys.databases db
WHERE database_id > 4
AND NAME LIKE @dbname;
SELECT @TSQLExec =
@TSQLExec + ‘USE [‘ + d.NAME + ‘]; CHECKPOINT; DBCC SHRINKFILE ([‘ + f.NAME + ‘])
with no_infomsgs;’ + Char(13) + Char(10)
FROM sys.databases d,
sys.master_files f
WHERE d.database_id = f.database_id
AND d.database_id > 4
AND f.type = 1
AND d.NAME LIKE @dbname;
PRINT @TSQLExec;
EXEC (@TSQLExec);
WITH fs
AS
(
SELECT database_id, TYPE, SIZE * 8.0 / 1024 SIZE
FROM sys.master_files
)
UPDATE a
set a.logFileSizeAfterMB = (SELECT SUM(SIZE) FROM fs WHERE TYPE = 1 AND fs.database_id = db.database_id)
FROM #temp a
inner join sys.databases db on a.dbid = db.database_id
WHERE database_id > 4
AND NAME LIKE @dbname
SELECT * FROM #temp ORDER BY dbname
SET NOCOUNT OFF;
END;
GO
Jayesh
February 6, 2024 / 11:58 pm Reply
Not working for SQL 2012, CONCAT is not usable function.
Greg Robidoux
March 22, 2022 / 5:06 pm Reply
Hi John,
try to use this in place of the CONCAT statement.
SELECT @TSQLExec =
@TSQLExec + ‘USE [‘ + d.NAME + ‘]; CHECKPOINT; DBCC SHRINKFILE ([‘ + f.NAME + ‘]) with no_infomsgs;’ + Char(13) + Char(10)
-Greg
JOHN BEVILAQUA
March 22, 2022 / 4:51 pm Reply
CONCAT does not work on SQL 2008. Could you post a version that works on SQL 2008?
Hossam Aladdin
June 10, 2021 / 7:01 am Reply
–Added a filter to exclude Availability Group secondary replica databases
–Also added TRUNCATEONLY option
USE master
GO
CREATE OR ALTER PROCEDURE dbo.usp_ShrinkAllLogsExcludeSysDBS (@dbname SYSNAME = ‘%’)
AS
BEGIN
DECLARE @TSQLExec VARCHAR (MAX) = ”;
SET NOCOUNT ON;
IF OBJECT_ID(‘tempdb..#Temp’) IS NOT NULL
DROP TABLE #temp
CREATE TABLE #temp (dbname sysname, dbid int, logFileSizeBeforeMB decimal(15,2), logFileSizeAfterMB decimal(15,2),is_preferred_replica INT);
WITH fs
AS
(
SELECT database_id, TYPE, SIZE * 8.0 / 1024 SIZE
FROM sys.master_files
)
INSERT INTO #temp (dbname, dbid, logFileSizeBeforeMB,is_preferred_replica)
SELECT
name, database_id,
(SELECT SUM(SIZE) FROM fs WHERE TYPE = 1 AND fs.database_id = db.database_id) LogFileSizeMB,x.is_preferred_replica
FROM sys.databases db
OUTER APPLY (SELECT [master].sys.fn_hadr_backup_is_preferred_replica(db_name(db.database_id))is_preferred_replica) x
WHERE database_id > 4 AND x.is_preferred_replica = 1
AND NAME LIKE @dbname;
SELECT @TSQLExec = CONCAT (
@TSQLExec,
‘USE [‘,
d.NAME,
‘]; CHECKPOINT; DBCC SHRINKFILE ([‘,
f.NAME + ‘],0,TRUNCATEONLY) with no_infomsgs;’ ,
Char (13),Char (10))
FROM sys.databases d
OUTER APPLY (SELECT [master].sys.fn_hadr_backup_is_preferred_replica(db_name(d.database_id))is_preferred_replica) x
,sys.master_files f
WHERE d.database_id = f.database_id
AND d.database_id > 4
AND x.is_preferred_replica = 1
AND f.type = 1
AND d.NAME LIKE @dbname;
PRINT @TSQLExec;
EXEC (@TSQLExec);
WITH fs
AS
(
SELECT database_id, TYPE, SIZE * 8.0 / 1024 SIZE
FROM sys.master_files
)
UPDATE a
set a.logFileSizeAfterMB = (SELECT SUM(SIZE) FROM fs WHERE TYPE = 1 AND fs.database_id = db.database_id)
FROM #temp a
inner join sys.databases db on a.dbid = db.database_id
WHERE database_id > 4
AND NAME LIKE @dbname
SELECT dbname, dbid, logFileSizeBeforeMB,logFileSizeAfterMB,is_preferred_replica FROM #temp ORDER BY dbname
SET NOCOUNT OFF;
END;
GO
Nuno
January 11, 2021 / 2:37 pm Reply
Great script! I just tweaked it a bit, limiting the shrink to online databases, as I have some offline DBs: d.state = 0.

SQL Server Shrink Database T-SQL Code

8 Comments

Leave a ReplyCancel Reply