Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to shrink the database log after using alwayson. Xiaobian thinks it is quite practical, so share it with you as a reference. I hope you can gain something after reading this article.

Problem Description:

After using alwayson, the master and slave databases are synchronized in real time. The principle is to synchronize through transaction logs, so the transaction logs of the master database are always in use, and the transaction logs of the master database cannot be contracted.

Shrinkage databases do not work when master and slave databases are synchronized. Since the master database cannot shrink, the logs of the slave database will keep growing, causing disk space to keep growing.

A large number of online contraction log methods, basically do not work, suspected that there is no actual environment used, the following program is my actual use in the summary of records.

Solution:

After discovering this problem at the beginning, it was also studied for a long time. The method discovered was manual operation first. Because these operations cannot be automated with statements, they have always been handled manually.

Maybe people are lazy (only lazy people can promote mechanical automation, there will be a variety of publications and creations! Isn't that right? Oh, I always wanted to be able to pass scripts and achieve automation.

Finally found out today, sum it up.

The general idea is as follows

Alwayson is removed from the database and availability database through scripts, that is, master-slave synchronization is cancelled, so that the master database becomes a single database mode. Then shrink the transaction log and add the master-slave database after shrinking.

Considering that there is a point, the operation needs to delete the database from the database, in order to prevent operational errors, the database of the master database is deleted, this operation is quite dangerous ah, so the script is divided into three. Back and forth between two machines.

The first script is executed on db1.

The second script is executed on db3

The third script executes on db1

(Here db1 is the master library and db3 is the slave library.) Don't ask me db2, because db2 was created first and then db2 was deleted due to problems. You can replace it according to your actual situation.)

When the time is ripe, or with judgment, consider combining the following three scripts into one script, and then execute it with one click, or add it to a timed task and automatically execute it once a month.

The following scripts are available after hand-testing

syncdb is the name of alwayson synchronization,

DBSERVER1 and DBSERVER3 are the names of the master-slave databases. DBSERVER1 is the master and DBSERVER3 is the slave.

:Connect DBSERVER1 -U sa -P abc@123 is to connect to the database using SQLCMD mode. Please modify the following password.

test is the database name.

1. Cancel master-slave synchronization

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

2 Delete the database from the library, and add it after tidying up.

:Connect DBSERVER3 -U sa -P abc@123 USE [master]GO DROP DATABASE [test]GO

3. Backup transaction logs, shrink log files, add slave databases.

--- 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 = 10gouse [test]goDBCC 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 communicatingbegin trydeclare @conn bitdeclare @count intdeclare @replica_id uniqueidentifier declare @group_id uniqueidentifierset @conn = 0set @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 endendend trybegin catch -- If the wait loop fails, do not stop execution of the alter database statementend catchALTER DATABASE [test] SET HADR AVAILABILITY GROUP = [syncdb];GOGO

Remember to turn on SQLCMD mode before executing:Connect

When opened, you can see that SQLCMD commands are grayed out.

About "how to shrink database log after using alwayson" this article is shared here, I hope the above content can be of some help to everyone, so that you can learn more knowledge, if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.