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];

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
		'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 = ''
	@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'

FROM #ao_databases

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.


Yavuz Selim Kart

I try to explain what I know in software and database. I am still improving myself by doing research on many programming languages. Apart from these, I am also interested in Graphic Design and Wordpress. I also have knowledge about SEO and Social media management. In short, I am a determined person who likes to work hard.

You may also like...

Don`t copy text!