SQL Server Always-on yapısındaki Log database’lerinin küçültülmesi için aşağıdaki query’i kullanabilirsiniz.
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'
-- get primary server, availability group, and database names
select * from #ao_databases
-- get all log files for shrink operation
select * from #ao_databases aod join [master].sys.master_files smf on aod.[db_id] = smf.[database_id] where smf.[type] = '1'
-- run nul shrink operation across all AlwaysOn databases
exec (@ao_shrinkfile)