1 / 15

Interrogating the Transaction Log

Dive into the crucial role of transaction logs in SQL Server management in this comprehensive guide. We'll explore the ACID properties—Atomicity, Consistency, Isolation, and Durability—defining transactions and the importance of write-ahead logging. Learn about log space reservation, how to read transaction logs using fn_dblog, and tactics for identifying active transactions. With practical examples and tips from seasoned experts, this content aims to equip you with essential skills for effective database transaction control.

aiden
Télécharger la présentation

Interrogating the Transaction Log

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Or, The Transaction Log: Why You Can’t Run Your Database Without It Interrogating the Transaction Log

  2. What We’ll Cover • 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

  3. Database 101 - ACID Properties • Atomicity • Consistency • Isolation • Durability

  4. Human sacrifice, dogs and cats living together... mass hysteria!

  5. Transactions and Write-Ahead Logging • What is a transaction? • Explicit v. Implicit Transactions • Write-ahead logging • Roll back and roll forward

  6. Log Space Reservation • SQL Server uses log space for transaction records • Additional space is reserved for potential rollback activity • Committed transaction releases reserved space

  7. Log Space Reservation Demo • SMALLER TRANSACTIONS ARE BETTER!

  8. Reading the Transaction Log • 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

  9. fn_dblog demo

  10. sys.dm_tran_database_transactions • Returns information about active transactions • Results contain the begin and end LSN for each transaction

  11. Finding Log Records for Active Transactions – Attempt #1 • 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?

  12. Image Source - http://4.bp.blogspot.com/-DY_k_dCp9EE/TvzOLaf17JI/AAAAAAAABBM/dBff_mOtNws/s1600/wrong+answer.jpg

  13. Finding Log Records for Active Transactions – Failed Attempt #1 • 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.”

  14. Finding Log Records for Active Transactions – Attempt #2 • 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)

More Related