Skip to content

2016🔗

  • 5 min read

Transaction Logging & Recovery (part 3)

Continuation of some notes regarding the excellent content by Paul Randal in Pluralsight: SQL Server: Logging, Recovery, and the Transaction Log. Please consider supporting his excellent material by using Pluralsight and subscribing to his blog. He's contributed a vast amount to the SQL server community through SQLSkills This is my absorbing of key elements that I never had worked through

Jackalopes Are Real....so are Virtual Log Files

Ever seen a picture of a jackalope? Image by Mark Freeman (Jackalope, Grand Canyon North Rim, Oct 07) Creative Commons License

This is how I used to feel about Virtual Log Files. Folks were saying things like

  • "Your server may be slowing down because of those darn VLF's".....
  • "Have you checked your VLF count"...
  • "My VLF count was x" and more

Finding clarification on VLF (Virtual Log Files) can be difficult, as what is considered a high count for some may be contradicted by another with another "target VLF count" claim. Paul Randal unpacks this excellently in his class, providing some great transparency.

jackalopes-are-real-so-are-virtual-log-files_ibbrwc

Why Should I Care About VLFs?

In an excellent article regarding the performance impact analysis of VLF's, Linchi Shea provides some valuable insight into the impact. For more detailed analysis & graphs please look at this great article: Performance impact: a large number of virtual log files - Part I (2009)

  1. Inserts were about 4 times as slow
  2. Updates were about 8 times slower
  3. Deletes were about 5 times slower
  4. Recovery time can be impacted Slow recovery times and slow performance due to large numbers of Virtual Log Files (2008)
  5. Triggers & Log Backups can be slowed down Tony Rogerson article (2007)

Virtual Log Files

  • At the beginning of each log file is a header. This is 8kb header that contains settings like autogrowth & size metadata.
  • Active VLF's are not free for usage until they are marked as available when clearing the log (see previous post about backups)
  • When you create a db you have one active VLF file, but as you progress more VLF's will be used.
  • Too few or too many VLF's can cause problems.

VLF Count

  • You cannot change the number and size of VLF's in a new portion of the transaction log. This is SQL server driven.
  • The VLF size is determined by a formula.
  • For detailed breakdown of the changes that SQL 2014 brought for the VLF algorithm, see this excellent post by Paul Randal: Important change to VLF creation algorithm in SQL Server 2014 Since I'm working with SQL 2014, I found it interesting as the increased VLF count issue that can be impacting to server performance has been greatly improved. Paul's example cited that the number of VLF's in his example would result in 3192 VLF prior to 2014, but with SQL 2014 it decreased down to 455, which is a substantial improvement. Paul indicated that the prior algorithm was designed primarily for around 1997-1980's, when log files wouldn't be sized as large. Also note a critical question that he answers: COMPATIBILITY LEVEL IS IGNORED BY THE STORAGE ENGINE PROCESSOR This is great information he's shared, as I've found it confusing at times to separate out the Query Engine impact from compatibility level, and understanding this scope of impact can help with assessing possible impact.

More Detail than You Ever Wanted to Know on VLF's

  • VLF's internally contain log block sizes. 512-60KB.
  • When the log block is filled it must be flushed to disk.
  • Within the log block are the log records.
  • VLF's contain a header. This indicates whether or not the VLF is active or not, LSN, and parity bits.
  • VLF log records support multiple concurrent threads, so the associated transaction records don't have to be grouped.
  • LSN. I've heard the term used, but until you understand the pieces above, the term won't make sense. - Log Sequence Number = VLF Sequence Number : Log Block Number : Log Record
  • They are important as the LSN is stamped on the data file to show the most recent log record it reflects, letting sql server know during crash recovery that recovery needs to occur or not.

Number of Log Files

This is determine by a formula that has been updated for 2014.

  • Different size growths have different number of VLFs.
  • VLF's don't care about the total size, but instead about the growth.
  • For instance, Above 1 GB growth events on log file will split into 16 new VLF's, 1/16.

FAQ (I've asked and looked for some answers!)

**Create small log and then expand or create larger log initially? **

Paul Randal answered: No. If I was creating, say a 64 GB log, I'd create it as 8GB then expand in 8GB chunks to 64GB to keep the number of VLFs small. That means each VLF will be 0.5 GB, which is a good size. What is the ideal l number of VLFs? Some key articles I've found for detailed answers on understanding proper VLF count:

  1. Transaction Log VLFs - too many or too few (2008)
  2. 8 Steps to better Transaction Log throughput (2005)
  3. A Busy/Accidental DBA's Guide to Managing VLFs (2009) Resources
  4. Brentozar SP_BLITZ will check VLF counts How do I ensure my log file gets marked as available for reuse when in full recovery? Full recovery is required for point-in-time recovery after a failure. This is because every change to data or to database objects are written to the transaction log prior to being committed. These transactions are then written to the data file as SQL Server sees fit after this initial write to disk. The transaction log is a rolling history of all changes in the database and will allow for redo of each transaction in case of failure to rebuild the state of the data at failure. In the case of Full Recovery, the transaction log continues to expand until a checkpoint is issued via a successful transaction log backup. Top 13 SQL Server Mistakes and Misteps (2012) This great article by Tim Ford should be reviewed, as it's one of the best simple breakdowns of growth issues and prevention that I've read.
  • 2 min read

Verifying Instant File Initialization

Ran into a few issues verifying instant file initialization. I was trying to ensure that file initialization was enabled, but found out that running the xp_cmd to execute whoami /priv could be inaccurate when I'm not running it from the account that has the privileges. This means that if my sql service account has different permissions than I do, I could get the incorrect reading on if it is enabled.

Paul Randal covers a second approach using the sysinternals tool Accesschk, which seems promising. However, in my case, I didn't have permissions to run in the environment was I was trying to check. I found a way to do this by rereading original article in which Paul Randal demonstrates the usage of trace flags 3004,3605. This provided a very simple way to quickly ensure I was getting the correct results back. For even more detail on this, I highly recommend his Logging, Recovery, and Transaction Log course. I adapted pieces of his script for my quick error check on this issue.

Successfully Verifying

Successfully added instant file initialization should mean when you review the log you will not have any MDF showing up in the error log for zeroing. I adapted the sql script for reading the error log in a more filtered manner from this post: SQL Internals Useful Parameters for XP Reader (2014)

successfully-verifying_v1khio

  • 4 min read

Transaction Logging & Recovery (part 2)

Continuation of some notes regarding the excellent content by Paul Randal in Pluralsight: SQL Server: Logging, Recovery, and the Transaction Log. Please consider supporting his excellent material by using Pluralsight and subscribing to his blog. He's contributed a vast amount to the SQL server community through SQLSkills

Transaction Log File

  • The initial size of the log file is the larger of 0.58 MB or 25% of the total data files specified in the create database statement. For example, if you create a database with 4 separate files, it would increase the initial size the log file is set to.
  • This would be different if you've changed MODEL database to set the default log and database size.
  • The log file physically created must be zero initialized. Note that the benefits of instant file initialization apply to the data file, but the log file still has to be fully zero initialized, so a large log file creation doesn't benefit from instant file initialization. Previous Post on Enabling File Initialization --- Examine the errorlog (after you've enabled trace flag 3605,3004) EXEC xp_readerrorlog; GO
  • When examining the results, you can see the zeroing of the log file, but not the datafile if you have instant file initialization enabled.

transaction-log-file_daxina

Virtual Log Files

  • The transaction log is divided into virtual log files. This helps the system manage the log file more efficiently.
  • New VLF's are inactive & not used.
  • Active VLF's contain the log record activity and can't be reused until they have been noted as available by SQL server. My seque based on the fun experience of giant log files.
  • Note: In Brentozar Office Hours Brent talked about the common misconception of SIMPLE VS FULL logging. Most folks (guilty) think that SIMPLE reduces the amount of logging SQL server performs, thereby improving the overall performance. However, in a general sense this is a misconception. Logging, as previously discussed from my previous post on (101), is the core of SQL server, and required for transaction durability. The difference between SIMPLE and FULL is mostly to do with how the transaction log space is marked as available for reuse.
  • SIMPLE: after data files are updated and the data file is now consistent with the changes the log has recorded, the transaction logs are now marked as free and available.
  • FULL: all the transaction log records, even after hardened to the data file, are still used. This is what can cause the common issue of exponential log file growth with folks not aware of how it works. 300 GB log file on a small database due to now one watching? Been there? This is because the log file will keep appending the log entries overtime, without freeing up space in the existing transaction log, unless some action is taken to let SQL server know the transaction log file space is available for reuse.
  • Marking the space as available is done by ensuring you have a solid backup solution in place that is continually backing up the transaction log in the backup set) thereby letting SQL server know that the transaction log has been backed up and space can be reused in the existing log file.

  • The normal process would be to ensure you have a full backup, incremental backups, and transaction log backups running on a schedule.> Under the full recovery model or bulk-logged recovery model, if the transaction log has not been backed up recently, backup might be what is preventing log truncation. If the log has never been backed up, you must create two log backups to permit the Database Engine to truncate the log to the point of the last backup. Truncating the log frees space for new log records. To keep the log from filling up again, take log backups frequently.

    MSDN Troubleshooting a Full Transaction Log

  • My past experience was running into this challenge when performing a huge amount of bulk transactions. I ran the space out on a drive because the log files continued to grow with no backups on the log file running. The solution in my particular situation was to take a full backup, change the database recovery to Bulk-logged or SIMPLE, perform the massive changes, then get right back to full-recovery with backup. This helped ensure the log file growth didn't keep escalating (in my case it was the appropriate action, but normally you want to design the size of the transactions to be smaller, and the backup strategy to be continual so you don't run into this issue)

  • 3 min read

Transaction Logging & Recovery (101)

Logging & Recovery Notes from SQL Server Logging, Recovery, and the Transaction Log (pluralsight) Paul Randal Going to share some key points I've found helpful from taking the course by Paul Randal at pluralsight. I've found his knowledge and in detail presentation extremely helpful, and a great way to expand my expertise. Highly recommend pluralsight.com as well as anything by Paul Randal (just be prepared to have your brain turned to mush... he's definitely not writing "how to write a select statement" articles

Logging & Recovery

Why is it important to know about this?

  • One of the most critical components to how SQL server works and provides the Durability in ACID (Atomicity, Consistency, Isolation, Durability) is the logging and recovery mechanisms.
  • If log files didn't exist we couldn't guarantee after a crash the integrity of the database. This recovery process ensures after a crash even if the data file hasn't be changed yet (after a checkpoint) that SQL server can reply all the transactions that had been performed and thereby recover to the consistent point, ensuring integrity. This is critical and why we know even with a crash that no transactions will be left "halfway", as we'd require the transactions to be harden to the log file before SQL server would allow the data file to be changed. If this was done in the reverse order of writing to the data file, then if a crash happened, the log file might be out of sync, and you couldn't reply actions that might not have been fully made to the log file as the data file and log file wouldn't be in sync with the transactions noted.
  • The logging & recovery mechanism is actually the backend architecture driving most of the disaster recovery options like Mirroring, Replication, Availability Groups and more. They each have different ways of handling/processing, but underneath, they all rely on utilizing the transaction logs. - Logging = the backbone of sql server.

Data Files Aren't Changed Immediately

  • Unlike what you might think initially, data files are actually note being written to realtime with transactions. This would be inefficient. Instead, the log is written to, hardened, and then periodic checkpoints (default I believe is 1 minute) take these changes that have been hardened and ensure the changed pages in the buffer (dirty pages at this point) are updated as well.

Can't get away from transactions

  • Note that all actions occur in transactions. If not explicitly stated, then an implicit transaction is gathered by SQL server in how it handles the action (when dealing with DML). For example, if we alter 2 tables, we could manually set a transaction for each, so if one fails, both were rolled back to the original state, allowing us to commit one table changes even if the other experienced a failure. If we combined both of these statements without defining a transaction then SQL server would imply a transaction, and this might result in a different behavior.

For example:

    alter table foo
    alter table bar
    GO
    -- if foo failed, then both tables would not be changed, as the transaction itself failed

on the other hand the statement below would be promised in SSMS as a batch separator and SQL server would have two separate transaction for each. If one had an error, then the other would still be able to proceed.

    alter table foo
    GO
    alter table bar

Everything gets logged!

  • Everything has some logging to describe changes in log file (even in simple recovery)
  • Version store & workfile changes in tempdb

Commitment Issues

Transaction Has Committed

  • Before the transaction can commit, the transaction log file has to be written through to disk. - Once the transaction log is written out to disk, the transaction is considered durable
  • If you are using mirroring, the system will stop and wait for the replica/mirror to harden the transaction to the mirror db log file on disk, and then can harden the transaction log to the disk on the primary.
  • The Log file basically represents an exact playback of what changes have been made, so even if the buffer was cleared (removing the pages that were changed in buffer), SQL crashed, or your server went down, SQL server can recover the changes that were made from the log file. This is the "description" of the changes that were made.