FIX: Slow synchronization when disks have different sector sizes for primary and secondary replica log files in SQL Server AG and Logshipping environments

Notice
Note After you apply this hotfix, you must enable the trace flag 1800 on all the servers to make this hotfix work correctly.
Symptoms
Consider the following scenario:
  • You enable the AlwaysOn Availability Groups or Logshipping feature in Microsoft SQL Server 2012 or SQL Server 2014.
  • 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:

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

Cumulative Update 3 for SQL Server 2012 SP2

Cumulative Update 13 for SQL Server 2012 SP1

After you apply the hotfix and enable trace flag 1800 on the primary servers, 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.

About cumulative updates for SQL Server

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 (especially the servers that have a 512-byte physical sector) that can transition into the primary role. 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:
  1. Run the following command at an elevated command prompt:
    Fsutil fsinfo ntfsinfo x:
    Note The x placeholder represents the drive that you are checking.
  2. 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" valueDrive type
    409640964K native
    5124096Advanced Format (also known as 512E)
    512512512-byte native
Properties

Article ID: 3009974 - Last Review: 01/19/2016 19:35:00 - Revision: 7.0

Microsoft SQL Server 2014 Developer, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Standard, Microsoft SQL Server 2012 Enterprise, Microsoft SQL Server 2012 Developer, Microsoft SQL Server 2012 Standard

  • kbqfe kbhotfixserver kbfix kbsurveynew kbexpertiseadvanced KB3009974
Feedback