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.

 

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:

  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" value

    Drive type

    4096

    4096

    4K native

    512

    4096

    Advanced Format (also known as 512E)

    512

    512

    512-byte native

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

What affected your experience?

Thank you for your feedback!

×