After you apply this hotfix, you must enable the trace flag 1800 as a startup parameter on all the servers or replicas that have 512-byte physical sector size and restart them, to make this hotfix work correctly.
Symptoms
Consider the following scenario:
-
You enable the AlwaysOn Availability Groups or Logshipping feature in Microsoft SQL Server.
-
The disks that store the log files of the primary and secondary replica in an AlwaysOn Availability Group (AG) have different sector sizes. Or in Logshipping environments, the disks that the store the log files for Logshipping primary servers and Logshipping secondary servers have different sector sizes. For example:
-
The primary replica log file is located on a disk that has a sector size of 512 bytes. However, the secondary replica log file is located on a disk that has the sector size of 4 kilobytes (KB).
-
The primary replica log file is located on an on-premises local system that has a sector size of 512 bytes. However, the secondary replica is located on a Windows Azure Storage disk that has the sector size of 4 kilobytes (KB).
-
In this scenario, the following error message is logged in the SQL Server Error log. The error message may continue for a while after restarting if there had been logs that were not applied to secondary prior to restarting the server.
There have been X misaligned log IOs which required falling back to synchronous IO. The current IO is on file ....
Additionally, AG or Logshipping synchronization runs very slowly because of the synchronous I/Os. If the secondary replica is in Windows Azure Storage, it takes much longer than expected to finish the synchronization process.
Note This issue occurs when you use both the new drives that have a 4-KB sector size and the old drives that have a 512-byte sector size. For more information about the new drives, see SQL Server - New drives Use 4K sector size and SQL Server–Storage spaces/VHDx and 4K sector size.
Resolution
The issue was first fixed in the following cumulative update of SQL Server.
Cumulative Update 5 for SQL Server 2014 /en-us/help/3011055
Cumulative Update 3 for SQL Server 2012 SP2 /en-us/help/3002049
Cumulative Update 13 for SQL Server 2012 SP1 /en-us/help/3002044
After you apply the hotfix and enable trace flag 1800 as a startup parameter on all the servers replicas running on a disk that has a sector size of 512 bytes, you notice a small increase in the size of the following files:
-
Transaction log file
-
Log backups
Additionally, you notice that the following messages are logged in the SQL Server Error log of the primary server:
The tail of the log for database '<database name>' is being rewritten to match the new sector size of 4096 bytes
This is an informational message that can be safely ignored.
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. See the latest cumulative updates for SQL Server:
Workaround
To work around this issue, move the transaction log file at the destination to a drive that has Bytes per Physical Sector set as 512 bytes.
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
More Information
As a best practice, try to make sure that all disks on all replicas (at least all disks that host log files) have the same sector size. In mixed environments, where the secondary has a physical sector of 512 bytes and the primary has a sector size of 4 KB, TF 1800 should be used as a startup flag on all servers or replicas that have 512-byte physical sector size and restarted. This makes sure that the ongoing log creation format uses a 4-KB sector size.
For more information about how SQL Server works with larger sector sizes, see the following post on the support blog:
SQL Server–Storage spaces/VHDx and 4K sector size
You can use the Fsutil command prompt utility to determine the Bytes per Physical Sector value. If this parameter isn't visible in the output, you must apply the hotfix that's specified in KB article 982018.
To verify the kind of drive that you have, follow these steps:
-
Run the following command at an elevated command prompt:
Fsutil fsinfo ntfsinfo x: Note The x placeholder represents the drive that you are checking.
-
Use the values for Bytes Per Sector and Bytes per Physical Sector to determine the kind of drive that you have. To do this, use the following table:
"Bytes Per Sector" value
"Bytes per Physical Sector" value
Drive type
4096
4096
4K native
512
4096
Advanced Format (also known as 512E)
512
512
512-byte native
