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 [1]

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 [2]

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