In SQL Server 2008 R2 SP1 we made updates to dynamically accommodate disk drives that present physical sector sizes greater than 512 bytes. In practice, these are generally 4K, physical sector size drives and the SQL 2008 R2 transaction log code will dynamically adjust to the presented physical sector size to accommodate various sector configurations.
To understand this issue completely you have to go back to SQL Server 2000 where we shipped SQL Server's master, model and msdb as if it had been formatted on a 4K, physical sector size drive. This allowed SQL Server to execute on a 4K drive without having to rebuild the master, model or msdb databases.
The formatted sector size (physical sector size of disk where the database was created) is stored in metadata for the database and when the database is restarted (recovered/online/…) we check to see if the data written in the log file is aligned on this formatted sector size boundary. When we detect a situation where the data is not aligned on this boundary we fix up the tail of the log and write a message in the SQL Server error log.
ErrorFormat: The tail of the log for database %ls is being rewritten to match the new sector size of %d bytes. %d bytes at offset %I64d in file %ls will be written.
Customers that have been applying SQL Server 2008 R2 SP1 based builds may encounter the error on restart of the SQL Server service for the master, msdb and model databases.
The work to handle varying sector sizes as done in SQL Server Denali code line and then ported back to the SQL 2008 R2 code line. During the port a specific check was overlooked that results in the error message condition. The database will start writing log records at the physical, disk sector size. This is the proper behavior but for database like master, msdb, and model the logical sector size is not accommodated properly.
Allow me to try to visualize this. In the diagram I show an example of the masterlog.ldf. SQL Server has a logical sector size of 4K but the disk uses 512 byte sectors.
In SQL Server 2008 R2 SP1 we detect the physical sector size of 512 bytes and use that as our write boundary. Prior versions would have used the formatted (logical) sector size in this example - max(physical, logical). We can now write the 1st and 2nd sectors. When we restart SQL the SQL 2008 R2 SP1 log file is checked against the metadata (4K) and it does not align so we log the warning and we write empty data in the next 6 sectors, re-aligning the log write activity on a 4K boundary. But the next write to the log encounters the issue and again writes at a 512 byte boundary so the next restart can trigger the repeat behavior to fix-up the tail of the log.
Note: This does NOT present a risk to the database. SQL Server is writing on sector aligned boundaries, it is just a logical and physical mismatch of the sector size that results in the noisy startup messages.
Bob Dorr - Principal SQL Server Escalation Engineer