Log growth issues under the database Full recovery model

Most recently, I encountered a disk full when I was doing alwayson, this is because the database participating in AlwaysOn must be the full recovery model, and the full recovery model, the database shrinkage is not effective, so you can only use transaction log backups to do transaction log truncation. And thanks to @i6first and the great God of the great God, the article learned that it is possible to use transaction log backups (forgive me for being a little white, I don't know.) The full recovery model requires a transaction log backup to resolve the log size issue. is still growing up. ）。 This article is used to record the log growth issue in full mode.

The argument is as follows ↓↓↓↓

-------------------------- Create a test environment -----------------------------

-- Create a database CREATE DATABASE db_test; GO -- Create a table  Use db_test CREATE TABLE INT IDENTITY (1,1NVARCHAR());

Database log file size after environment creation:

Because of transaction log backups, a full backup is required first, or you will be prompted

"System.Data.SqlClient.SqlError: Unable to execute backup LOG because there is currently no database backup. (MICROSOFT.SQLSERVER.SMO) "

After the database is created, the default is the full recovery model, so for testing purposes, we first create a full database backup. As follows:

BACKUP DATABASE [db_test]  to  DISK = N'D:\Program Files\Microsoft SQL Server\mssql12. Mssqlserver\mssql\backup\db_test.bak' with Noformat, noinit,  = N'  db_test-Full database backup ', SKIP, Norewind, nounload,  =tenGO 

-------------------------- Start Testing -----------------------------

Write test data and view the database log file size after writing

DECLARE @i INT=0; while @i<10000BEGIN    INSERT  intoT (name)VALUES(NEWID()),(NEWID()),(NEWID()),(NEWID()),(NEWID())IF(@i% -=0)BEGIN    UPDATETSETName=GETDATE()ENDIF(@i% -=0)BEGIN     DELETETWHEREIdinch(SELECT  TOP TenId fromTWHEREId%3=0);ENDSET @i=@i+1;END

Make a contraction first to see if the allocated space after shrinking and whether the available space will be smaller. As follows

 Use [db_test] GO DBCC Shrinkfile (N'db_test_log'0)GO

As you know, shrinking the database does not reduce the log allocation space, this time trying to truncate the log in the same way as a transaction log backup. As follows

BACKUP LOG [db_test]  to  DISK = N'D:\Program Files\Microsoft SQL Server\mssql12. Mssqlserver\mssql\backup\db_test.bak' with Noformat, noinit,  = N'  db_test-Full database backup ', SKIP, Norewind, nounload,  =tenGO 

As you can tell, our database log allocation free space is increased from 45% to 89%. Although the physical memory is not reduced, the logical space is really getting bigger.

-------------------------- Conclusion -----------------------------

Therefore, when using the full recovery model of the database, to do a regular transaction log backup.

???

To study the problem, for the wool database contraction, the space is increased, because the log file inserted in the journal caused by it.

Log growth issues under the database Full recovery model