Stuart Moore looks at how SQL Server builds log sequence numbers:
If you’ve ever dug down in the SQL Server transaction logs or had to build up restore chains, then you’ll have come across Log Sequence Numbers (LSNs). Ever wondered why they’re so large, why they all look suspiciously the same, why don’t they start from 0 and just how does SQL Server generate these LSNs? Well, here we’re going to take a look at them
Below we’ll go through examples of how to look inside the current transaction log, and backed up transaction logs. This will involve using some
DBCC
commands and the undocumentedfn_dblog
andfn_dump_dblog
function. The last 2 are very handy for digging into SQL Server internals, but be wary about running them on a production system without understanding what’s going on. They can leave filehandles and processes behind that can impact on your system.
It’s an interesting look into SQL Server’s internals.