Hundreds of expert level books, articles, videos and blogs has talked much about Transaction Logs and How well to manage it.
Everything from basics to best practices are documented in various format except "How to read Transaction Log Entries"
Yes. this series of articles is just an attempt to try reading transaction log records *of some basic DDL & DML commands*
Eventhough We are going to confirm our learning with examples, We can expect mistakes or inaccuracy in this journey.
Is this so easy to understand? Nope. good level understanding about storage internals and system tables and little experience is required.
Is this so hard to understand? Nope. good level of storage internals and system tables knowledge is enough to understand how this works.
Who are all the target audiences? dedicated SQL Server students having curious minds.
Are We going to learn everything about T-Logs? Its an Ocean. Our focus is only around Understanding T-Logs & Interpreting T-Log records.
Prerequisites (for the first time I am suggesting my blog posts as reference):
1. Storage Internals Basics - http://www.sqlservercentral.com/blogs/livingforsqlserver/2010/12/13/sql-server-storage-internals-part-1-basics/
2. Partitions and Allocation Units - http://www.sqlservercentral.com/blogs/livingforsqlserver/2010/12/16/sql-server-storage-internals-part-2-partitions-and-allocation-units/
3. Architecture of Heaps - http://www.sqlservercentral.com/blogs/livingforsqlserver/2010/12/19/sql-server-storage-internals-part-3-architecture-of-heap-structure/
4. How to read a Heap Page - http://www.sqlservercentral.com/blogs/livingforsqlserver/2010/12/21/sql-server-storage-internals-part-4-how-to-read-a-heap-page/
5. Properties of Clustered and Non Clustered Index Pages - http://www.sqlservercentral.com/blogs/livingforsqlserver/2010/12/27/sql-server-storage-internals-part-5-properties-of-clustered-and-non-clustered-index-pages/
Understanding Logging and Recovery in SQL Server (By Paul Randal)
Not satisfied with this? Here are some simple excercises for you
1. Create a database. Just run below statement and check how many columns are familiar to you?
SELECT * FROM fn_dblog(null, null)
Not familiar with lots of columns? Don't worry. You have a friend. Lets learn together.
2. Create some test databases with different log file sizes such as 1 MB, 50 MB, 100 MB and 2 GB an run below statement.
Do you observe any difference in output?
Paul Randal and Kalen Delaney