Top 10 articles on the SQL Server transaction log February 13, 2014 by Ivan Stankovic Understanding Logging and Recovery in SQL Server In “Understanding Logging and Recovery in SQL Server”, Paul S. Randal has given a complete overview of the transaction log. In four sections he explains: what is logging, what is recovery, how the transaction log works, and which recovery models are available. This article can be used as a starting point in understanding fundamentals of the transaction log and the logging process Logging in SQL Server is necessary to describe what’s changed in the storage structure of the database. Each database must have a transaction log to function properly. Virtual log files (VFLs) are smaller parts of the transaction log used for simpler internal management: Choosing the right recovery model is important for future managing of the transaction log. There are three different recovery models, each with its own characteristics, advantages, and disadvantages. Here are some key points by Paul S. Randal regarding the transaction log: Do not create multiple log files, as it will lead to performance loss Be aware of the recovery model your database is using and the effect it has on the transaction log—especially around whether it can automatically truncate or not when a checkpoint occurs Be aware of the potential for transaction log growth, the factors that can lead to it, and how to get it back under control Know where to look for help when troubleshooting a full transaction log  Geek City: Exploring the Transaction Log Structure Exploring and reading the transaction log can be difficult due to the way information is recorded and the majority is in the hexadecimal format. SQL Server has, officially undocumented, the DBCC LOGINFO command which can be used to explore and read the transaction log. In “Geek City: Exploring the Transaction Log Structure”, Kalen Delaney points out the DBCC LOGINFO commands and importance of VLFs (Virtual Log Files). Using the DBCC LOGINFO command the result would be one row per Virtual log file and specific columns including: FileId, FileSize, StartOffset, FSecNo, Status, Parity, and CreateLSN. There is an example of T-SQL code which can be used to explore the transaction log with the DBCC LOGINFO command Transaction Log Full – Transaction Log Larger than Data File – Notes from the Field #001 As every active SQL Server database is constantly performing some kind of operation, its transaction log is always getting bigger. Tim Radney points out the most common error a database administrators can make: having a database in full recovery mode without regular transaction log backups. If that is the case, the transaction log will eventually fill up the whole data storage space. In “Transaction Log Full – Transaction Log Larger than Data File – Notes from the Field #001”, Tim suggests using the DBCC SHRINKFILE function and switching the database into the simple recovery mode Multiple log files and why they’re bad In “Multiple log files and why they’re bad”, Paul S. Randal presents the statistical data collected from 1,300 SQL Server instances regarding the number of transaction logs per database. If there are multiple transaction logs present that implies that the first one is large and it’s run out of space (more than 2TB). Paul points out the importance of proper transaction log file size management and suggests usage of Log Growths counter the Databases performance object The Trouble with Transaction Log Based on experience, Thomas Larock claims that majority of transaction logs are in the bad shape. The default settings when creating a database in SQL Server is the model template, with characteristic to auto-grow by 10 percent to a maximum of 2 terabytes. The number of Virtual log files (VFLs) is depending on the initial or extended size of the transaction log: Credit and copyright: Thomas Larock and Kimberly Tripp In the article, “The Trouble with Transaction Log”, there is code that will show the maximum number of Virtual log files (VLFs) for a log file that is set to grow by defaults. Also, there is a recommendation that Virtual log file should be 512MB as a starting point Undocumented function – fn_dblog Like the DBCC LOGINFO command, the fn_dblog function is undocumented officially too. In “Undocumented function – fn_dblog”, Muhammad Imran explains syntax and gives an example of code to execute. There is a useful script which converts a hexadecimal Log sequence number (LSN) to an integer Log sequence number which can only then be used as a parameter in the fn_dblog function Transaction log myths In “Transaction log myths”, Vedran Kesegić talks about popular transaction log myths. Transaction log truncation will not make a transaction log smaller, transaction log records are not written to the data storage immediately, transaction log shrinking is not a good practice, a transaction log will grow if its database is in the simple recovery model etc. sp_BlitzTM Result: Transaction Log Too Large? sp_BlitzTM is a stored procedure by Brent Ozar Unlimited which determinates the “health” of SQL Server. It’s stored in the temporary TempDB database. After running the stored procedure it shows whether the transaction log is larger than the data itself which indicate that transaction log backups are not being performed or not being performed often enough. In “sp_Blitz Result: Transaction Log Too Large?”, there is a guide and recommendation how to deal with the problem About transaction log and its truncation in SQL Server In “About transaction log and its truncation in SQL Server”, Mika Wendelius explains how the transaction log file content is categorized into three different categories: Used portion – this contains log records that are written to the transaction log but can be removed Active portion – this is the part of the transaction log which is defined by the oldest active transaction. This information cannot be removed while the transaction is still active Unused portion – this is empty space  In the second part, Mika provides step by step instruction for resolving the situation when a transaction log is full Restore your SQL Server database using transaction logs In “Restore your SQL Server database using transaction logs”, Tim Chapman gives a scenario for recovering a SQL Server database using a transaction log. He points out how important the backup plan is and why all the backups should be tested more than once References:  Paul S. Randal, Understanding Logging and Recovery in SQL Server  Mika Wendelius, About transaction log and its truncation in SQL Server See also Interested in reading the SQL Server transaction log? Consider ApexSQL Log, a 3rd party SQL Server transaction log reader, that renders transaction log information into a searchable, sortable grid and can produce Undo and Redo scripts. About Latest Posts Ivan StankovicIvan is a SQL Server professional and computer geek with years of IT and SQL Server experience. He has startedwith playing computer games, continued with computer programming and system administration. His areas of expertise are SQL Server disaster recovery, auditing, and complianceView all posts by Ivan Stankovic Latest posts by Ivan Stankovic (see all) Using Extended Events to review SQL Server failed logins - August 5, 2014 SQL Server backup – models and types - May 26, 2014 SQL Server Policy Based Management – Categories and Database Subscriptions - May 21, 2014 See also How to recover SQL Server data from accidental UPDATE and DELETE operation Recover SQL data from a dropped table without backups How to recover SQL Server data from accidental updates without backups How to recover a single table from a SQL Server database backup Related posts: SQL Server Transaction Log – Part 1 – Log Structure and Write-Ahead Logging (WAL) Algorithm A beginner’s guide to SQL Server transaction logs Reading the SQL Server Transaction Log 10 most important SQL Server transaction log myths What are virtual log files in a SQL Server transaction log?