Interrogating the Transaction Log
Download
Report
Transcript Interrogating the Transaction Log
Or, The Transaction Log: Why You Can’t Run Your Database Without It
ACID Properties
Transactions
Write-ahead Logging
Log Space Reservation
Reading the Log with fn_dblog
Identifying Active Transactions
Finding Log Records Associated with Active
Transactions
Atomicity
Consistency
Isolation
Durability
Human sacrifice, dogs and cats living
together... mass hysteria!
What is a transaction?
Explicit v. Implicit Transactions
Write-ahead logging
Roll back and roll forward
SQL Server uses log space for transaction
records
Additional space is reserved for potential
rollback activity
Committed transaction releases reserved
space
SMALLER TRANSACTIONS ARE BETTER!
Table-based function that allows the
transaction log to be read
Takes 2 parameters, starting and ending log
sequence numbers (LSN)
SELECT * FROM fn_dblog(NULL,NULL)
returns the contents of the log
Returns information about active
transactions
Results contain the begin and end LSN for
each transaction
Both the DMV and function contain a
transaction_id
The DMV id is hexadecimal and the function
id is decimal, so converting one of them
should work, right?
Image Source - http://4.bp.blogspot.com/DY_k_dCp9EE/TvzOLaf17JI/AAAAAAAABBM/dBff_mOtNws/s1600/wrong+answer.jpg
Converting the transaction IDs from either
source did not result in a match
After several attempts to make it work (octal,
double and add 30, multiply by 9/5 and add
32) I wrote Paul Randal
His response? “They don’t match. It’s
annoying.”
Both the DMV and the function contain begin
and end LSNs
The function returns colon-delimited hex
LSNs (0000001a:000027a5:0001)
The DMV returns a decimal value
(26000001014900001)