AlwaysOn How to truncate a log

Once the database is AlwaysOn, the log cannot be used with a simple log, and a full log must be used. The full log of the production environment will be huge and how to handle it.

Create a Tools\bat_tools folder under D disk

1. Create Backup_log.sql file, script as follows:

Use [dbname]
declare @bakfile nvarchar (MB)--@bakfile backup file name
set @bakfile = ' D:\database_bak\log_bak_ ' +convert (nvarchar (8), GETDATE (), 112) + '. Log '
BACKUP log [dbname] to disk= @bakfile with retaindays= 1,compression-- dbname the database name

DBCC SHRINKFILE (dbname_log,100)--dbname_log to the database file logical name, 100 to the number of MB you want the log to shrink to go

2. Create del_log.bat files, as follows:

Sqlcmd-s localhost-u loginname-p password-i d:\tools\bat_tools\backup_log.sql
@echo off

Perform a backup operation, and then delete the files for more than 7 days from the end of all. Log in the backup folder.
3. Create a Scheduled task:

This process is simpler and will not be discussed in detail.