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

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.

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.

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