SQL Server AlwaysOn log shrinking

At present, many projects are gradually adopting SQL Server AlwaysOn architecture as a high-availability cluster technology for databases.

And most of Microsoft's products, Citrix XenDesktop, XenApp, PVS and XenMobile also support this technology. AlwaysOn has the dual advantages of Mirror and Cluster. It can not only access the unique hostname and IP address, but also save the data as multiple copies like Mirror, instead of having to use shared storage like a cluster. At the same time, AlwaysOn also has a multi-read and multi-write architecture, which can effectively improve database performance. A single AlwaysOn group can support up to 8 secondary replicas, and can also support the AlwaysOn architecture of different sites to achieve remote disaster recovery of the database and access the database nearby.

Back to the point, let's talk about the problem of full database logs that we often encounter when using SQL Server, especially the deployment of more Citrix XenDesktop virtual desktop projects. In a thousand-point scale, database logs grow very fast. In the traditional Mirror environment, we use scripts to do corresponding pruning. Let's talk about the simplest shrinking method in AlwaysOn.

SQL Server logs will automatically release the original space as long as they are backed up. For example, the logs generated every week are 10GB, and the logs on disk will always be kept around 10GB.

Therefore, the effective way to shrink is to make regular backups of transaction logs for SQL Server, which can be done directly in the SQL Server management tool to make maintenance backup plans.

In a general deployment, we make two backup plans, a full backup and a transaction log backup, and keep the backup for a month or more according to security requirements, and automatically delete the old backup.

SuperDream