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 DBCC TRACESTATUS
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
Best Answer
The documentation you referenced says that:
and
And on the definition of flag 1800 it says:
That means a restart is required depending on the configuration method you used: if by
DBCC TRACEON
, no restart required. If by-T
, a restart is required.Since you don't have a maintenance window to restart the server now by using the -T recommended option, you could use DBCC TRACEON to activate it emediately and also configure the -T on the service properties without rebooting SQL Server. That way when the server reboots next time the flag configured by DBCC TRACEON will be lost, but the option -T will take effect.