©2011 Hewlett-Packard Development Company, L.P. The information contained herein is subject to change without notice

Recovery, Logging and

the Transaction Log

Miroslav Dimitrov

Agenda

• Transaction Log Architecture• Log Records• Checkpoints• Transaction Log operations• Recovery and Crash Recovery• Recovery Models and Minimal Logging

Transaction Log Architecture

• Creating and growing the log file• Log is always zero-initialized• TF 3004 and 3605• VLFs, Log blocks and LSNs• Undocumented DBCC LOGINFO • DBCC SQLPERF (Logspace)

Log Records

• What are they?• Where they stayed?• Are they all part of transactions?• TVF fn_dblog (startLSN,endLSN) – TF 2537• Compensation records (rollback) – only roll forward

Conundrum

If a full backup is being taken, then you have transaction updated 100 000 records but it is rolled back why is the diff backup so large? Surely nothing has changed.

Checkpoints

• Why they exists? • Thresholds 20 ms and 100 ms?!• Log records during checkpoint• Log records are written to disk via three ways:

– When we have commit/rollbacked transaction– When a data file page is written to disk– When a log block hits the maximum of 60 kb size and must be flushed to disk

Checkpoints (cont.)

• Types:– Automatic– Indirect (new in SQL 2012)– Internal– Manual

• TF 3502 (for which db), 3504 (write latency), 3605

• XE Events - checkpoint_begin and checkpoint_end

Transaction Log operations

• VLF is either active or inactive• DBCC OPENTRAN• VLF is made inactive by log clearing (truncation)

• Circular nature of log• log_reuse_wait_desc in sys.databases – why log clearing did not succeed last time it was attempted

Recovery and Crash Recovery

• Recovery brings db to a consistent point in time

• Types:– Crash recovery after a system failure– Recovery at the end of restore– Recovery after a failover (CL, AG, Mirroring)

• REDO• UNDO

Recovery and Crash Recovery (cont.)

• Recovery during restore• Crash Recovery• Where the crash recovery stops?!• How SQL is recovering DBs? – TF 3408 single thread

• Fast Recovery (from SQL 2005 onwards – only ENT) – does not apply for restore

Recovery Models and Minimal Logging

• Minimal Logged vs Efficiently Logged• FULL• SIMPLE• BULK-LOGGED (why to use it only temp)• Minimally Logged:

– Creating, Dropping, Rebuilding indexes (reorganize is always fully logged)

– Bulk operations – bcp, bulk insert etc.

Recovery Models and Minimal Logging (cont.)

• pseudoSimple recovery model?• Is switching between FULL and BULK-LOGGED breaking the log backup chain?

• Deferred drop and TRUNCATE table (two ways)

Questions?

Demo time