How to truncate SQL server2008+ transaction log space

The SQL database transaction log records any changes to the database, and the user can roll back the transaction log to recover the data, but the log files become large as the database is used longer.

To prevent the database's transaction log from filling up, routine backups are critical. When a log backup is made, inactive VLF is released, increasing the available space for the log. However, by default the log is backed up and the log file size does not change, as

Before backup, total size 24.13MB

650) this.width=650; "title=" clip_image002 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clip_ image002 "src=" http://s3.51cto.com/wyfs02/M02/72/DB/wKiom1XuoEqwgR70AAD1Oy8G79o145.jpg "height=" 282 "/>

The total size is still 24.13MB after backup, but has been reduced from 93.1% to 11% with space

650) this.width=650; "title=" clip_image004 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clip_ image004 "src=" http://s3.51cto.com/wyfs02/M02/72/D8/wKioL1XuonWTnRddAADvQPdRSm8964.jpg "height=" 284 "/>

That is, the used log space is reduced, but the unused log space is not freed. In fact, if there is enough disk space, you can not shrink the space

What if you need to free up space to conserve disk space?

If you want to shrink the database log size, you need to truncate the transaction log

The following commands are no longer supported in SQL SERVER 2008 + environments (available under SQL 2005)

BACKUP LOG database name with TRUNCATE_ONLY or

BACKUP LOG database name with NO_LOG

To shrink the database log, you first need to truncate the file by setting the database recovery mode to simple.

Use DATABASENAME;

GO

ALTER DATABASE DATABASENAME SET RECOVERY simple; Changing the recovery model of a database is simple

GO

DBCC Shrinkfile (Databasename_log, 5); Manually compress log files to 5MB

GO

ALTER DATABASE DATABASENAME SET RECOVERY full; Change the recovery model of the database back

GO

The following is a test environment

Before truncation

650) this.width=650; "title=" clip_image005 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clip_ image005 "src=" http://s3.51cto.com/wyfs02/M00/72/D8/wKioL1XuonWzVHQKAAAvJr68ZlE061.jpg "height="/>

After truncation

650) this.width=650; "title=" clip_image006 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clip_ image006 "src=" http://s3.51cto.com/wyfs02/M00/72/DB/wKiom1XuoEvQpggiAAAuAoZsS38493.jpg "height="/>

Description

1. The above operation can also be done through the graphical interface;

2. How to determine the size of shrinkage, you can view the following commands

DBCC Sqlperf (Logspace)

3. It is recommended to do a full backup immediately after log truncation

How to truncate SQL server2008+ transaction log space