Shrink SQL Server Transaction Log for all Databases

By:   |   Updated: 2020-05-19   |   Comments (5)   |   Related: More > Database Administration


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?


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 = '%')
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));

    SELECT database_id, TYPE, SIZE * 8.0 / 1024 SIZE
    FROM sys.master_files
INSERT INTO #temp (dbname, dbid, logFileSizeBeforeMB)
    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;
  'USE [',
  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;
    SELECT database_id, TYPE, SIZE * 8.0 / 1024 SIZE
    FROM sys.master_files
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
SELECT * FROM #temp ORDER BY dbname

Here are examples for executing this stored procedure:

-- shrink database log for all user databases
USE master
EXEC dbo.usp_ShrinkAllLogsExcludeSysDBS 

-- shrink database log for just database Test2
USE master
EXEC dbo.usp_ShrinkAllLogsExcludeSysDBS 'Test2'

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.

Last Updated: 2020-05-19

get scripts

next tip button

About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips
Related Resources

Comments For This Article

Monday, January 11, 2021 - 2:37:59 PM - Nuno Back To Top (88028)
Great script! I just tweaked it a bit, limiting the shrink to online databases, as I have some offline DBs: d.state = 0.

Tuesday, July 28, 2020 - 6:04:44 AM - Dennis Back To Top (86204)

FYI : Not working on SQL Servers prior to 2012 because of missing CONCAT

Tuesday, June 16, 2020 - 12:51:30 PM - Narin Back To Top (86011)

Thank you very much.

Wednesday, May 20, 2020 - 6:23:52 AM - Eli Leiba Back To Top (85705)

Good point about the NULL issue

Tuesday, May 19, 2020 - 11:55:25 AM - Jim Back To Top (85697)

The temporary table needs to allow for NULLs in the logFileSizeAfterMB column. So add the NULL to the CREATE TABLE line.

CREATE TABLE #temp (dbname sysname, dbid int, logFileSizeBeforeMB decimal(15,2), logFileSizeAfterMB decimal(15,2) NULL);

Thanks for this. It save a lot of space.


Recommended Reading

How to rename a SQL Server database

How to determine SQL Server database transaction log usage

How to read the SQL Server Database Transaction Log

How to stop and start SQL Server services

SQL Server Download Quick Links

get free sql tips
agree to terms