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