How to Shrink All Database Log Files Using T-SQL Script

 As a DBA, managing log file sizes is crucial to ensure your databases run smoothly. Below is a T-SQL script to shrink all database log files at once, excluding the system databases (master, tempdb, model, msdb, rdsadmin). This script uses cursors to iterate through each database and its corresponding log files.

Script to Shrink All Database Log Files

DECLARE @dbName NVARCHAR(128);

DECLARE @logFileName NVARCHAR(128);

DECLARE @sql NVARCHAR(MAX);

DECLARE db_cursor CURSOR FOR

SELECT name 

FROM sys.databases 

WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb', 'rdsadmin'); -- Exclude system databases

OPEN db_cursor;

FETCH NEXT FROM db_cursor INTO @dbName;

WHILE @@FETCH_STATUS = 0

BEGIN

    DECLARE log_cursor CURSOR FOR

    SELECT name 

    FROM sys.master_files 

    WHERE type_desc = 'LOG' AND database_id = DB_ID(@dbName);

    OPEN log_cursor;

    FETCH NEXT FROM log_cursor INTO @logFileName;

    WHILE @@FETCH_STATUS = 0

    BEGIN

        SET @sql = 'USE [' + @dbName + ']; DBCC SHRINKFILE (N''' + @logFileName + ''', 128);';

        EXEC sp_executesql @sql;

        FETCH NEXT FROM log_cursor INTO @logFileName;

    END;

    CLOSE log_cursor;

    DEALLOCATE log_cursor;

    FETCH NEXT FROM db_cursor INTO @dbName;

END;

CLOSE db_cursor;

DEALLOCATE db_cursor;

Explanation

1. Declare Variables: The script begins by declaring variables to hold database names, log file names, and the SQL commands to be executed.
2. Database Cursor: A cursor (db_cursor) is declared and opened to select all databases excluding the system databases.
3. Log File Cursor: For each database, another cursor (log_cursor) is opened to select all log files.
4. Construct and Execute SQL: For each log file, a SQL command to shrink the log file is constructed and executed using sp_executesql.
5. Close and Deallocate Cursors: Both cursors are properly closed and deallocated after their use to free up resources.

Usage

• Copy the Script: Copy the provided script into your SQL Server Management Studio (SSMS).
• Run the Script: Execute the script to shrink log files for all user databases.

Note

• DBCC SHRINKFILE: The DBCC SHRINKFILE command with a target size of 128 MB is used here. Adjust this size as per your requirements.
• Monitoring: Always monitor the impact of shrinking operations on your database performance.

By using this script, you can automate the log file maintenance task, ensuring that your databases remain efficient and well-maintained.