Transaction Log File & Recovery Models

SQL Server transaction log file is used to store recent database transactions. When you modify or alter any entity in SQL server database then it will be result in a write action in the transaction log file. Generally we do several activity on the SQL server database like add, delete, rename, update, insert and many more and this will also be the result of write action in the transaction log file but a read action on the SQL server database does not change the transaction log file.

Does recovery models affect transaction log? 

• The answer is NO. Recovery models (simple, full & bulk-logged) do not affect what has written into the transaction log but it effects the time period of entries that present in the transaction log, detail description is as follows:

1. Simple Recovery Model: In this recovery model, transaction log entries are keeping for the database integrity not for the database recovery purpose. When we run checkpoint operation on the transaction log files then all the inactive entries are deleted and space is ready for reuse. It is also the best way to manage the size of transaction log file but you can prefer another way also to manage size of transaction log file.
2. Full Recovery Model: In this recovery model, entries are key for the both integrity as well as for database recovery purpose. All the entries are remains present in the transaction log file until a log backup does not perform on the file. Sometimes it difficult to manage the size of transaction log files through this recovery model.  
3. Bulk-logged Recovery Model: It is almost same as full recovery model but in this recovery model bulk operations are minimally logged.

How to Monitor Size of Transaction log file?

• You can monitor the size of transaction log file by DBCC SQLPERF command. When will you run this command then it returns information about amount of log space used currently and it also indicates when transaction log is in need to truncate. Log truncation frees the used disk space but does not reduce the size of physical transaction log file. To reduce transaction log file size, you will have to shrink the log file.

How to shrink transaction log file in full recovery model?

• You can shrink the transaction log file manually by the using of DBCC SHRINKFILE command. This command may be differ for the different SQL server database.

Read More