We have recently migrated our database mirroring secondary to new hardware. The log drive (and other SQL Server drives) on the secondary has been formatted to 64KB block size, whereas the primary server is on the default 4KB.
As a result of this we are getting the following entries in the log of the secondary almost constantly -
There have been N misaligned log IOs which required falling back to synchronous IO. The current IO is on file L:\SQLSERVER\Logs\MyDatabase_log.ldf.
this article suggests a fix is to turn on trace flag 1800 on the server
with 512-byte sector size as per Microsoft's recommendation
I have run the following command on both servers:
fsutil fsinfo ntfsinfo L:\
and can see for the primary:
Bytes Per Sector : 512 Bytes Per Physical Sector : 512 Bytes Per Cluster : 4096 Bytes Per FileRecord Segment : 1024
and the secondary:
Bytes Per Sector : 512 Bytes Per Physical Sector : 4096 Bytes Per Cluster : 65536 Bytes Per FileRecord Segment : 1024
Therefore it seems traceflag 1800 should be enabled on the primary.
With this in mind, I ran
DBCC TRACEON (1800, -1); on the primary and verfied it was on with
I then checked the error log on the secondary and can see the messages regarding Async IO still persist.
Does this traceflag need setting as a startup parameter (with subsequent reboot) to take effect? Obviously I would do this anyway so it persists reboots but just ran the
DBCC TRACEON first so it is active
The documentation doesn't reference that a reboot is required.
Obviously the way to find out is to test but due to this being a production server, I would need a maintenance window to that which isn't available to me at the moment
Eventually, the primary server will be migrated to new hardware and I will ensure the disks are formatted in the same manner but for the tie being I was hoping this traceflag would provide a quick fix.
If I cannot get this to work, I suppose the intermediate option would be to reformat the log drive on the existing primary to 64KB block size