使用alwayson后如何收缩数据库日志的方法详解

--- you must execute the following script in sqlcmd mode.

: connect dbserver1 -u sa -p abc@123

use [master]

go

alter availability group [syncdb] remove database [test];

go

: connect dbserver3 -u sa -p abc@123

use [master]

go

drop database [test]

go

--- you must execute the following script in sqlcmd mode.

: connect dbserver1 -u sa -p abc@123

use [master]

go

backup log [test] to disk= 'nul:' with stats = 10

go

use [test]

go

dbcc shrinkfile (n 'test_log' , 20480)

go

use [master]

go

alter availability group [test]

add database [test];

go

: connect dbserver1 -u sa -p test@123

backup database [test] to disk = n '\\dbserver3\e$\share\test.bak' with copy_only, format, init, skip, rewind, nounload, compression, stats = 5

go

: connect dbserver3 -u sa -p test@123

restore database [test] from disk = n '\\dbserver3\e$\share\test.bak' with norecovery, nounload, stats = 5

go

: connect dbserver1 -u sa -p test@123

backup log [test] to disk = n '\\dbserver3\e$\share\test.trn' with noformat, noinit, noskip, rewind, nounload, compression, stats = 5

go

: connect dbserver3 -u sa -p test@123

restore log [test] from disk = n '\\dbserver3\e$\share\test.trn' with norecovery, nounload, stats = 5

go

: connect dbserver3 -u sa -p test@123

-- wait for the replica to start communicating

begin try

declare @conn bit

declare @ count int

declare @replica_id uniqueidentifier

declare @group_id uniqueidentifier

set @conn = 0

set @ count = 30 -- wait for 5 minutes

if (serverproperty( 'ishadrenabled' ) = 1)

     and ( isnull (( select member_state from master.sys.dm_hadr_cluster_members where upper (member_name collate latin1_general_ci_as) = upper ( cast (serverproperty( 'computernamephysicalnetbios' ) as nvarchar(256)) collate latin1_general_ci_as)), 0) <> 0)

     and ( isnull (( select state from master.sys.database_mirroring_endpoints), 1) = 0)

begin

   select @group_id = ags.group_id from master.sys.availability_groups as ags where name = n 'yorkdb'

     select @replica_id = replicas.replica_id from master.sys.availability_replicas as replicas where upper (replicas.replica_server_name collate latin1_general_ci_as) = upper (@@servername collate latin1_general_ci_as) and group_id = @group_id

     while @conn <> 1 and @ count > 0

     begin

         set @conn = isnull (( select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)

         if @conn = 1

         begin

             -- exit loop when the replica is connected, or if the query cannot find the replica status

             break

         end

         waitfor delay '00:00:10'

         set @ count = @ count - 1

     end

end

end try

begin catch

     -- if the wait loop fails, do not stop execution of the alter database statement

end catch

alter database [test] set hadr availability group = [syncdb];

go

go