Shrinking Log Files in Always On Databases in SQL Server

Hello everyone,

In this article, I will try to give information about shrinking log files in databases with Always On structure in SQL Server.

In SQL Server, in some cases, you may want to shrink Log files in databases with Always On structure.

You can easily do this using the code below.

USE [master];
SET NOCOUNT ON

IF OBJECT_ID('tempdb..#ao_databases') IS NOT NULL
	DROP TABLE #ao_databases
CREATE TABLE #ao_databases (
	[primary] VARCHAR(255)
   ,[ag_name] VARCHAR(255)
   ,[db_id] INT
   ,[db_name] VARCHAR(255)
)
INSERT INTO #ao_databases
	SELECT
		'primary' = sdhags.[primary_replica]
	   ,'ag_name' = sags.[name]
	   ,'db_id' = sd.[database_id]
	   ,'db_name' = sadc.[database_name]
	FROM sys.dm_hadr_availability_group_states sdhags
	INNER JOIN [master].sys.availability_groups sags
		ON sdhags.[group_id] = sags.[group_id]
	INNER JOIN [master].sys.availability_databases_cluster sadc
		ON sags.[group_id] = sadc.[group_id]
	INNER JOIN [master].sys.databases sd
		ON sadc.[database_name] = sd.[name]
	WHERE [primary_replica] = @@servername
	ORDER BY sdhags.[primary_replica]
	, sags.[name]
	, sd.[database_id]
	, sadc.[database_name]

DECLARE @ao_shrinkfile VARCHAR(MAX)
SET @ao_shrinkfile = ''
SELECT
	@ao_shrinkfile = @ao_shrinkfile +
	'use [' + [db_name] + '];' + CHAR(10) +
	'backup log [' + [db_name] + '] to disk=''NUL:'' with no_checksum, continue_after_error;' + CHAR(10) +
	'checkpoint;' + CHAR(10) +
	'dbcc shrinkfile (' + CAST(smf.[file_id] AS VARCHAR) + ', emptyfile);' + CHAR(10) + CHAR(10)
FROM #ao_databases aod
JOIN [master].sys.master_files smf
	ON aod.[db_id] = smf.[database_id]
WHERE smf.[type] = '1'

SELECT
	*
FROM #ao_databases

SELECT
	*
FROM #ao_databases aod
JOIN [master].sys.master_files smf
	ON aod.[db_id] = smf.[database_id]
WHERE smf.[type] = '1'

EXEC (@ao_shrinkfile)

Good luck to everyone in business and life.