I’ve been doing SQLskills training recently, and Paul Randal (blog | Twitter) reminded our class that zeroing out a transaction log file does not use zeroes (0x00). Well, not since SQL Server 2014 at any rate. As you can see from this post, the stamping of the transaction log since SQL Server 2016 is done with 0xC0 instead:
SQL Server 7.0 changed the LDF format from the original [2KB] Sybase database page design to sector aligned log blocks stamped with all zeros (0x00). Creation or expansion of the LDF file involves writing the entire series of 0x00’s to the new bytes for the log.
SQL Server 16 changes the stamp to 0xC0’s instead of 0x00s.
Why 0xC0? It was probably intended as a way to thwart storage vendors who tend to optimize out zeroed bytes — unfortunately this disrupts the intent behind zeroing out in the first place. The transaction log needs to be stamped with a known value so that crash recovery can work properly.
This five-year-old news also means a sentence in my chapter of both the SQL Server 2017 and SQL Server 2019 books is ambiguous.
The old sentence reads:
When a transaction log is first created or file growth occurs, the portion of the drive must be zeroed-out (the file system literally writes zeroes in every byte in that file segment).
The corrected sentence should read:
When a transaction log is first created or file growth occurs, the portion of the drive must be stamped with a known starting value (the file system literally writes the binary value
0xC0in every byte in that file segment). This is commonly called “zeroing out” because the binary value was0x00prior to SQL Server 2016.
Share your SQL Server misconceptions in the comments.
Photo by Denny Müller on Unsplash.