Share via


SQL Server: How to shrink automatically all t-log files for databases for whole instance

There is a situation when you need to shrink a many TLOG files once per week, month, etc.

Just to be sure, you need to have a full backup and transaction log backup of your databases. Only after that, you will be able to shrink your transaction log file.

declare  commands cursor for
  
SELECT 
  
'USE [' + d.name + N']' + CHAR(13) + CHAR(10)  
    + 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY)' 
    +  CHAR(13) +  CHAR(10) +  CHAR(13) +  CHAR(10)   
FROM 
         sys.master_files mf  
    JOIN sys.databases d  
        ON mf.database_id = d.database_id  
WHERE d.database_id > 4  AND mf.name LIKE '%log%'
  
declare @cmd varchar(max) 
  
open commands 
fetch  next from  commands into  @cmd 
while @@FETCH_STATUS=0  
begin
  exec(@cmd) 
  fetch next  from commands into @cmd 
end
  
close commands 
deallocate commands 

you can also create a procedure to your MaintenanceDB and call it using SQL Jobs, based on regular bases.

USE MaintenanceDB 
GO 
  
Create Procedure Shrink_TLOG 
  
AS
  
declare commands cursor for
  
SELECT 
  
'USE [' + d.name + N']' + CHAR(13) + CHAR(10)  
    +  'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY)' 
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)  
FROM 
         sys.master_files mf  
    JOIN sys.databases d  
        ON mf.database_id = d.database_id  
WHERE  d.database_id > 4  AND mf.name LIKE '%log%'
  
declare  @cmd varchar(max) 
  
open  commands 
fetch next  from commands into @cmd 
while @@FETCH_STATUS=0 
begin
  exec(@cmd) 
  fetch next  from commands into @cmd 
end
  
close  commands 
deallocate commands 
  
GO

After That You Can call it using EXEC command

USE MaintenanceDB 
Exec Shrink_TLOG

 
At the end, I created the script which is determined for Always ON configuration on SQL 2012 and above.
It is also usable for non AlwaysOn environment because It is checking if there IS alwayson or NOT. In AlwaysOn Environment is also important to check if you are trying to run the script against the Priamary or Secondary Replica. This script will check It and run the shrink only against the Primary replica.

USE master 
  
IF NOT EXISTS (  select  * from  sys.objects where  name LIKE '%hadr%' ) 
  
BEGIN
            declare commands1 cursor for
  
                SELECT 
          
                'USE [' + d.name + N']'  + CHAR(13) + CHAR(10)  
                + 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY)' 
                + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)  
                FROM 
                    sys.master_files mf  
                        JOIN sys.databases d  
                    ON mf.database_id = d.database_id  
                WHERE d.database_id > 4  AND mf.name LIKE '%log%'
  
            declare @cmd1 varchar(max) 
  
            open commands1 
            fetch next  from commands1 into @cmd1 
            while @@FETCH_STATUS=0 
            begin
            exec(@cmd1) 
            fetch next  from commands1 into @cmd1 
            END
              
            close commands1 
            deallocate commands1 
  
END
  
ELSE
  
BEGIN   
        DECLARE @NameOfServer NVARCHAR(250)  = @@SERVERNAME  
        DECLARE @DescRole NVARCHAR(70) 
  
        SELECT @DescRole = HARS.role_desc 
            FROM sys.dm_hadr_availability_replica_states AS HARS 
            JOIN sys.availability_replicas AS AR 
                ON AR.replica_id = HARS.replica_id 
        WHERE AR.replica_server_name = @NameOfServer 
  
        IF @DescRole = 'PRIMARY'
        BEGIN
  
            declare commands2 cursor for
  
                SELECT 
          
                'USE [' + d.name + N']'  + CHAR(13) + CHAR(10)  
                + 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY)' 
                + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)  
                FROM 
                    sys.master_files mf  
                        JOIN sys.databases d  
                    ON mf.database_id = d.database_id  
                WHERE d.database_id > 4  AND mf.name LIKE '%log%'
  
            declare @cmd2 varchar(max) 
  
            open commands2 
            fetch next  from commands2 into @cmd2 
            while @@FETCH_STATUS=0 
            begin
            exec(@cmd2) 
            fetch next  from commands2 into @cmd2 
            END
  
            close commands2 
            deallocate commands2 
  
        END
  
        ELSE PRINT 'This is the secondary Replica, Run Shrink against the primary One.'
END