Getting Started with SQL Server Transaction Log

SQL server transaction log records of all the changes made on the database while the actual records are placed in a separate file. This file is known as transaction log file and the file extension for transaction log file is .ldf. Transaction log holds enough record to undo all the changes made on the database. It is the most critical component in the SQL server that can recover database if there is a system failure.

Note: Never delete or move transaction log unless you know the effect of this.

As we have seen above transaction log file uses a transaction log file (.ldf) to records all the changes made on the database and the size of this file grows as per every change on the database. SQL server offers two designed to counterbalance the size of transaction log: transaction log truncation & log file shrinking.

Transaction Log Truncation: SQL server automatically removes the entries from the transaction log file. The frequency of record removal depends upon the recovery model used in the database. If you have chosen full or bulk-logged recovery model for your database then SQL server truncates the transaction log file when you perform backup. In case of simple recovery model, sql server truncates the transaction log file at every transaction checkpoint.

Note: It is recommended to use full recovery model for production database.

Transaction Log File Shrinking: Transaction log truncation removes the entries only from the log file; it does not reduce the size of transaction log file. If you know there is a unused space in your transaction log file then you can shrink your log file and this process is know as transaction log file shrinking. Run below T-SQL command to shrink a transaction log file:

DBCC SHRINKFILE (, )

Sometime, you do not finish with above command. Try following code to shrink the transaction log file.

USE DatabaseName

GO

DBCC SHRINKFILE(, 1)

BACKUP LOG WITH TRUNCATE_ONLY

DBCC SHRINKFILE(, 1)

GO

Note: You can't shrink a transaction log file when your database is offline.

Backup Transaction Log: If you are using simple recovery model for your database then you can't backup the transaction log, if you still want to backup then you have to change the recovery model for your database first. Read my article “Twoways to Change the Recovery Model”. After changing the recovery model for your database, you can perform backup by following T-SQL command:

BACKUP LOG Your-Database-Name

TO Your-desired-location;

GO