SQL Server 2012 Internal Note_Logging and recovery

Transaction log internals

1. The transaction log

   1. records changes made to the database and stores enough information to allow SQL Server to recover the database

   2. Physically, the transaction log is one or more files associated with a database at the time the database is created or altered.

   3. Each log record is labeled with a unique log sequence number (LSN)

   4. Transaction log is written before the changes to the database are written (Buffer Manager)

      1. Every time a page is changed, the LSN corresponding to the log entry for that change is written into the header of the data page

         Log records for a transaction are written to disk before the commit acknowledgement is sent to the client process

2. Recovery is the process of reconciling the data files and the log.

3. A restart recovery runs every time SQL Server is started

   1. The process runs on each database because each database has its own transaction log.

4. A restore recovery (or media recovery), is run by request when a restore operation is executed

Phases of recovery

During recovery, only changes that occurred or were in progress since the last checkpoint are evaluated to determine whether they need to be redone or undone

The three phases of the SQL Server recovery process

Phase 1: Analysis

1. Starting at the last checkpoint record in the transaction log
2. Constructs a dirty page table (DPT) consisting of pages that might have been dirty at the time SQL Server stopped
3. Build an active transaction table that consists of uncommitted transactions at the time SQL Server stopped

Phase 2: Redo

1. This phase returns the database to the state it was in at the time the SQL Server service stopped
2. If the SQL Server service stops after a transaction commits but before the data is written out to the data pages, when SQL Server starts and runs recovery, the transaction must be rolled forward

Phase 3: Undo

1. It rolls each of these active transactions back individually.
2. Any uncommitted transaction at the time SQL Server stopped is undone so that the database reflects none of the changes

SQL Server uses multiple threads to process the recovery operations on the different data bases simultaneously.

Page LSNs and recovery

Every database page has an LSN in the page header that reflects the location in the transaction log of the last log entry that modified a row on that page

Comparing LSNs to decide whether to process the log entry during recovery

A transaction log can’t be truncated before the point of the earliest transaction that’s still open

If a transaction remains open, the log must be preserved because it’s still not clear whether the transaction is done or ever will be done

Log reading

The transaction log enables SQL Server to guarantee recoverability in case of statement or system failure and to allow a system administrator to take backups of the changes to a SQL Server database

The log cache

SQL Server caches log records before they are physically written

1. The log cache contains up to 128 entries on 64-bit systems or 32 entries on 32-bit systems
2. Each entry can maintain a buffer to store the log records before they get written to disk as a single block (from 512 bytes to 60 KB)
3. As the log buffers are written to disk, SQL Server can reuse the buffer

Log pool

1. The log pool is a hash table hashed on the block ID and the database ID
2. The log pool allows SQL Server to access sets of log records needed for different technologies that need access to the log

Changes in log size

SQL Server always treats the log as one contiguous stream

Understanding virtual log files

The transaction log for any database is managed as a set of virtual log files (VLFs) whose size is determined internally by SQL Server based on the total size of all log files and the growth increment used when enlarging the log.

A log always grows in units of entire VLFs and can be shrunk only to a VLF boundary

Multiple VLFs that make up a physical log file

A VLF can be in one of four states

Active

The active portion of the log begins at the minimum LSN representing an active transaction. The active portion of the log ends at the last LSN written.

Recoverable

The portion of the log preceding the oldest active transaction is needed only to maintain a sequence of log backups for restoring the database to a former state

Reusable

If transaction log backups aren’t being maintained or you’ve already backed up the log, VLFs before the oldest active transaction aren’t needed and can be reused. Truncating or backing up the transaction log changes recoverable VLFs into reusable VLFs.

Unused

One or more VLFs at the physical end of the log files might not have been used yet if not enough logged activity has taken place

Observing virtual log files

DBCC LOGINFO to observe the key properties of virtual log files.

Viewing VLFs

FileId: indicates which of the log’s physical files contains the VLF

FileSize StartOffsetin bytes

FSeqNo: the use order (logical order), the FSeqNo values don’t start at 0 or 1

Status: indicates whether the VLF is reusable. 2: either active or recoverable, 0: reusable or completely unused

CreateLSN: lists the current LSN value at the time the VLF was added to the transaction log, 0 means that the VLF was part of the original log file created when the database was created

The first physical page of a log file contains header information, not log records, so the VLF is considered to start on the second page

If all the VLFs in the log have a status of 2, SQL Server needs to add new VLFs to the log to record new transaction activity.

Try to keep your VLFs less than 1 GB in size; try to keep the number of VLFs to no more than a few hundred, with possibly 1,000 as an upper limit.

Using multiple log files

All VLFs in one physical file are used before any VLFs in the second file are used.

If none of the VLFs in multiple physical log files is available for reuse when a new VLF is needed, SQL Server adds new VLFs to each physical log file in round-robin fashion

Understanding automatic truncation of virtual log files

SQL Server assumes that you’re not maintaining a sequence of log backups if any of the following is true (in autotruncate mode):

1. You have configured the database to truncate the log regularly by setting the recovery model to SIMPLE.
2. You have never taken a full database backup
3. You haven’t taken a full database backup since switching the database to FULL or BULK_ LOGGED recovery model from SIMPLE recovery model.

sys.database_recovery_status column last_log_backup_lsn, if null, then in autotruncate mode.

Autotruncate mode

SQL Server truncates the database’s transaction log every time it gets “full enough”

“Full enough” means that more log records exist than can be redone in a reasonable amount of time during system startup—the recovery interval

Truncation: all log records prior to the oldest active transaction are invalidated and all VLFs not containing any part of the active log are marked as reusable.

The checkpoint process

The thread checks the database and then truncates the inactive portion of the log.

If the log is regularly truncated, SQL Server can reuse space in the physical file by cycling back to an earlier VLF when it reaches the end of the physical log file.

Maintaining a recoverable log

The active portion of the log cycling back to the beginning of the physical log file

If a log backup sequence is being maintained, the part of the log before the minimum LSN can’t be overwritten until those log records have actually been backed up. The VLF status stays at 2 until the log backup occurs. After the log backup, the status changes to 0 and SQL Server can cycle back to the beginning of the file.

Automatically shrinking the log

A database should be truncated so that it’s the most shrinkable, and if the log is in autotruncate mode and the autoshrink option is set, the log is physically shrunk at regular intervals.

Autotruncate means only that VLFs considered recoverable are marked as reusable at regular intervals, but VLFs in an active state aren’t affected.

Viewing the log file size

DBCC SQLPERF(‘logspace’)

DBCC result

sys.dm_os_performance_counters

SELECT instance_name as [Database],
 cntr_value as "LogFullPct"
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Percent Log Used%'
 AND instance_name not in ('_Total', 'mssqlsystemresource')
 AND cntr_value > 0;

View result

Database backup and restore

Understanding the types of backups

Full backup

Backup copies all the pages from a database onto a backup device

Differential backup

Backup copies only the extents that changed since the last full backup was made.

Each time a full backup is made, all bits in the DIFF are cleared to 0. When any page in an extent is changed, the bit in the DIFF page corresponding to that extent is changed to 1.

Log backup

Backup copies all log records that have been written to the transaction log since the last log backup was made.

File and filegroup backup

SQL Server records the current log sequence number (LSN) at the time the backup starts and again at the time the backup ends.

Understanding recovery models

Minimally logged operations

Minimally logged operations are ones that don’t write every single individual row because doing so modifies the transaction log. At a minimum, enough information has to be logged when a minimally logged operation is performed to allow SQL Server to rollback a transaction that has failed. Even when SQL Server logs the changed data, it doesn’t actually write it to the log in the sequence that it was changed. It’s a feature that minimizes the amount of logging for the operations configured.

FULL recovery model

1. the least risk of losing work
2. all operations are fully logged

BULK_LOGGED recovery model

When you execute one of these minimally logged operations in a database in BULK_LOGGED recovery, SQL Server logs only that the operation occurred and logs information about space allocations.

Because of the reduced logging for minimally logged operations, the operations themselves can potentially be carried out much faster than in the FULL recovery model.(the speed improvement isn’t guaranteed)

Minimally logged operations might actually be slower in BULK_LOGGED recovery than in FULL recovery in certain cases

The BULK_LOGGED recovery model allows you to restore a database completely in case of media failure and gives you the best performance and least log space usage for certain minimally logged operations

The time it takes to restore a log backup made in the BULK_LOGGED recovery model is similar to the time it takes to restore a log backup made in the FULL recovery mode

SIMPLE recovery model

the logging that takes place in SIMPLE recovery is exactly the same as the logging in BULK_LOGGED recovery, but transaction log is truncated whenever a checkpoint occurs, the only types of backups that can be made are those that don’t require log backups

Switching recovery models

When created, a database starts in whatever recovery model is used by the model database on the instance

ALTER DATABASE <database_name>
 SET RECOVERY [FULL | BULK_LOGGED | SIMPLE]

# returns the recovery model and the state of database
SELECT name, database_id, suser_sname(owner_sid) as owner ,
 state_desc, recovery_model_desc
FROM sys.databases
WHERE name = 'AdventureWorks2008R2'

Choosing a backup type

A differential backup

1. Is faster if your environment includes many changes to the same data. It backs up only the most recent change, whereas a log backup captures every individual update
2. Captures the entire B-tree structures for new indexes, whereas a log backup captures each individual step in building the index.
3. Is cumulative. When you recover from a media failure, only the most recent differential backup needs to be restored because it contains all the changes since the last full database backup

A log backup

1. Allows you to restore to any point in time because it is a sequential record of all changes.
2. This allows you to recover right up to the point of the failure.
3. Is sequential and discrete, all log backups must be applied in the order that they were made.

Restoring a database

The combined use of log and differential backups, which reduces total restore time