🧠 Automating Log File Shrinking in SQL Server with T-SQL

In SQL Server, transaction log files (.ldf) can grow large over time — especially in environments with frequent transactions or infrequent log backups. Large log files can consume excessive disk space and impact performance if not managed properly.

While it’s not recommended to shrink log files regularly (as it can cause fragmentation and future regrowth), there are times when it’s necessary — for example, after a large data purge or migration.

This blog post demonstrates how to automatically identify and shrink large log files across all user databases in SQL Server using T-SQL.

⚙️ The Script

Here’s the complete T-SQL script:

BEGIN
DECLARE @Database VARCHAR(200)
DECLARE @logSize DECIMAL(18,5)
DECLARE @LogFile NVARCHAR(200)
DECLARE @Message NVARCHAR(500)
DECLARE @FileName3 VARCHAR(100)
DECLARE @SQL NVARCHAR(256)

-- Temporary table to store log statistics
CREATE TABLE #LogStat 
( 
    databaseName SYSNAME, 
    logSize DECIMAL(18,5), 
    logUsed DECIMAL(18,5), 
    status INT 
) 

-- Get log space usage for all databases
INSERT INTO #LogStat   
EXEC ('DBCC SQLPERF(logspace)')

-- Cursor to loop through all user databases
DECLARE CUR CURSOR LOCAL 
FOR SELECT databaseName, logSize 
    FROM #LogStat 
    WHERE DB_ID(databaseName) > 4  -- Skip system databases

OPEN CUR
FETCH NEXT FROM CUR INTO @Database, @logSize

WHILE @@FETCH_STATUS = 0
BEGIN
    -- If log file size is greater than 10 GB
    IF (@logSize > 10000)
    BEGIN
        DECLARE @logfiletoshrink VARCHAR(250)
        DECLARE logfile_cur CURSOR FOR
            SELECT f.name AS LogicalName
            FROM sys.master_files f
            INNER JOIN sys.databases d 
                ON d.database_id = f.database_id
            WHERE d.name = @Database 
              AND f.type_desc = 'LOG'

        OPEN logfile_cur
        FETCH NEXT FROM logfile_cur INTO @logfiletoshrink

        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @SQL = 'USE ' + QUOTENAME(@Database) + 
                       '; DBCC SHRINKFILE (' + @logfiletoshrink + ', 5000, TRUNCATEONLY);'
            PRINT(@SQL)
            EXECUTE(@SQL)

            FETCH NEXT FROM logfile_cur INTO @logfiletoshrink
        END

        CLOSE logfile_cur
        DEALLOCATE logfile_cur
    END

    FETCH NEXT FROM CUR INTO @Database, @logSize
END

CLOSE CUR
DEALLOCATE CUR

DROP TABLE #LogStat
END

---

🧩 How It Works

1. Collect log statistics
   The script uses DBCC SQLPERF(LOGSPACE) to retrieve log file sizes and usage for all databases.
2. Filter system databases
   It excludes system databases (IDs ≤ 4) such as master, model, msdb, and tempdb.
3. Check log size threshold
   If a database log file is larger than 10,000 MB (10 GB), it qualifies for shrinking.
4. Shrink log files
   The script finds each log file’s logical name (f.name from sys.master_files) and executes
   DBCC SHRINKFILE to reduce it to 5,000 MB, using TRUNCATEONLY to release unused space.
5. Cleanup
   The temporary table and cursors are dropped after completion.

---

⚠️ Important Notes & Best Practices

• Don’t shrink logs regularly.
  Log files grow based on your workload. Repeated shrinking and regrowth can fragment your file and degrade performance.
• Always back up logs first.
  In databases using the Full or Bulk-logged recovery model, you must back up the transaction log before shrinking it.
• Adjust thresholds.
  Modify @logSize > 10000 and DBCC SHRINKFILE (..., 5000) to match your environment’s size and capacity planning.
• Use for cleanup, not routine maintenance.
  This script is ideal after large one-time operations (e.g., bulk deletes or data archiving).