Log trasacciones sql

The World’s Largest Community of SQL Server Professionals

Inside the SQL Server Transaction Log
Brad M. McGehee Director of DBA Education Red Gate Software www.bradmcgehee.com/presentations

My Assumptions About You
• • • • • You are probably a DBA (production or developer) who has at least one year’s experience. You have a basic understanding of how the SQL Server data cache works. Youunderstand the ACID properties of databases. You understand the differences between the full, bulk-insert, and simple recovery models. You understand how to perform full, differential, and transaction log backups.

What We Are Going to Learn Today
• • • • • • Why Does the Transaction Log Exist How the Transaction Log Works How Are Log Records Written to the Transaction Log How the Transaction Log CanBecome an IO Bottleneck How to Determine if the Transaction Log is a Bottleneck How to Deal with Transaction Log Bottlenecks and Boost Performance

Why Does The Transaction Log Exist
• • The transaction log stores a record of all the data modifications performed in a database. In the event of an unexpected shut-down, the data in the transaction log can be used during recovery to roll forward anytransactions completed, but not written to the database file on disk at the time of the shut-down. In addition, any uncompleted transactions that were partially written to the database file on disk before the failure can be rolled back during recovery. Both of these actions ensure database integrity and the ACID properties of transactions. This is the reason the Transaction Log exists.

•

•

Howthe Transaction Log Works
• In the following slides, I am going to start out with the big picture of how the transaction log works. • For the purposes of my explanation, I am going to assume that the database uses the full recovery model and that transaction log backups are performed periodically. • After discussing the big picture, I will provide a specific example, with more detail, so you betterunderstand what happens under the covers. • I won’t be covering every detail of how the transaction log works, as there is not enough time. See Inside Microsoft SQL Server 2008 for more details.

How We Often Think of Database and T-Log Files

MDF File (let’s ignore NDF files for now)

LDF File

This is a logical view, not a physical view of the MDF and LDF files.

Looking at a MDF File inDetail
Extent 64 kb Extent 64 kb

• •

8K Pages
• •

This is an example of how data is physically stored in an MDF file on disk. SQL Server always reads and writes data at the page level. Some data pages may be full, others partially full, and others empty. Data may be written randomly or sequentially.

Looking at a LDF File in Detail
LDF files are not physically divided into pages like MDF files.Instead, they are divided into virtual log files (VLFs), often of uneven sizes from small to large. They are used to determine which parts of a log can be set to reusable after a transaction log backup. SQL Server creates the VLFs and determines their sizes. LDF files are circular, and don’t grow if they don’t have to.

Unlike database pages, which hold rows of data, VLFs store log records. Logrecords are written sequentially in the log file, into available VLFs as needed, in a circular fashion. In other words, multiple log records for a single transaction can overflow from one VLF to another. (DEMO DBCC LOGINFO)

What are Log Records
• • • • • Every modification to the database creates multiple log records. Log records describe what is being changed and what the change is. A log record’ssize will vary, depending on the nature of the change. Log records are written sequentially (although mixed with other log records) to the log file and stored in VLFs, as previously discussed. Besides log records, extra space in the log is reserved in case a roll back occurs and compensation log records need to be written to the log. This is one reason why logging takes up more space that the...