15 49.0138 8.38624 1 0 4000 1 https://kadirkozan.com.tr 300 0

SQL Server AlwaysOn Yapısındaki Database’lerinde Log’larının Küçültülmesi

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)
Paylaş:
ÖNCEKİ YAZI
GET-WMIObject ile HBA kartına ait detaylı bilgileri görüntülemek
SONRAKİ GÖNDERİ
Exchange Server, Active Directory Üzerine Kullanılması Neden Önerilmez?