SQL Server transaction log (iii)

The log files for SQL Server become larger as data modifications are processed, and when processing log files, it is common for us to truncate the logs and shrink them.

Backup log databasename to disk= "

DBCC SHRINKFILE (Databasename_log)

Of course, if the disk space is tight, you can change the recovery mode to ' simple ' so that the log is truncated and then shrink the log

ALTER DATABASE test set recovery simple;

DBCC Shrinkfile (Test_log)

ALTER DATABASE test set recovery full;

Sometimes, the database log cannot be shrunk even if it is backed up, and may be caused by the following reasons

1. Database mirroring or replicated logs are not read;

2. Long-term uncommitted transactions

3. No backup of logs

4. Index Collation

5. Batch Data Write

Specific reasons can be seen from the log_reuse_wait_desc of the sys.databases view

Log_reuse_wait_desc has the following enumeration values:

1. Nothing: There is currently one or more virtual log files available

2. CheckPoint: No checkpoint has occurred since the last log truncation;

3. Log_backup: Log backup required

4. Active_backup_or_restore: Data backup or restore in progress, preventing log truncation

5. Active_transaction: Transaction is active

6. Database_mirring: Database mirroring is paused or the repository log is behind the main library

The above is the enumeration value used by version 2008, and after SQL Server 2012, a new enumeration value is used

0 = None
1 = Check Point
2 = log backup
3 = active Backup or restore
4 = active Transaction
5 = Database Mirroring
6 = Copy
7 = Database Snapshot creation
8 = Log Scan
9 = AlwaysOn Availability Group secondary replica is applying transaction log records for this database to a corresponding secondary database.

SQL Server transaction log (iii)