SQL Server Shrink Database T-SQL Code

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.

-- ================================================================================= 
-- 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