November 23, 2015 at 6:40 pm
I have an 2 node, AlwaysOn deployment on Windows 2012 R2 and SQL Server 2012 SP2. Both replicas are on 512b sector size drives.
We are migrating to a new VPC in AWS. In doing this we have added a replica to the availability group that is in AWS. This replica was created using SSD volumes in AWS, which are reporting themselves as 512e (512b sector size / 4096 physical sector size).
Now that this 3rd, AWS replica is in the availability group, I'm getting errors in my SQL log like:
There have been X misaligned log IOs which required falling back to synchronous IO.
I gather this is because the primary replica's log file is on a 512 byte sector size drive and the new replica's log file is on the 512e (512/4096) drive.
I have two questions about this I can't seem to find an answer to:
(1) Once we move completely over to the new VPC, and all drives are 512e, will this error message stop?
(2) Is there a performance issue having SQL server on 512e (Advanced Format) drives?
Any insight would be appreciated.
Thanks.
As a side note:
If I create the partitions on the AWS replica using Disk Manager the resulting volumes report 512b sector / 512b physical sector. If I create the partitions using Storage Pools the resulting partitions report 512b sector / 4096 physical sector.
I read that *all* SSDs have a physical 4k sector, so I'm assuming Disk Manager is simply creating the partitions unaware of the 512/4096 translation and the Storage Pools are aware of this; either way I think the translation is there.
November 23, 2015 at 10:04 pm
There is a bug fix - see https://support.microsoft.com/en-us/kb/3009974. What does SELECT @@version return? SP2 CU3 is 2011.110.5556.0. Workaround would be to ensure sector sizes match
November 23, 2015 at 11:07 pm
My version is 11.0.5623 (SQL2012 SP2 CU7), so it looks like I have that hotfix already, which might be is why I'm not seeing any performance issues at this time, just lots of errors in the SQL Error Log.
I noticed the hot fix talks specifically about sector size mismatches from 512b to 4k. I wonder if this includes the Advanced Format where the drive is emulating 512b but actually using 4k, which is what I have.
November 24, 2015 at 12:19 am
Did you turn on trace flag 1800?
November 24, 2015 at 11:38 am
Good call, I hadn't enabled the trace flag. I've done this on the secondary (with no affect). I'll enable the trace flag on the primary during the next maintenance window and report back on the results.
Thanks you.
November 24, 2015 at 10:49 pm
I've enabled trace flag 1800 on all replicas now and I'm still receiving this error. I did not restart the SQL service though. I place the trace flag in the start up parameters and issues a DBCC TRACEON (1800, -1) command. I believe this should do it. Would you agree that restarting the service should not be necessary?
November 30, 2015 at 7:57 am
dave-L (11/24/2015)
I've enabled trace flag 1800 on all replicas now and I'm still receiving this error. I did not restart the SQL service though. I place the trace flag in the start up parameters and issues a DBCC TRACEON (1800, -1) command. I believe this should do it. Would you agree that restarting the service should not be necessary?
You have to restart sql server when you put the TF 1800 as startup parameter.
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
June 16, 2020 at 11:51 am
H Dave-L
how did you get on when you restarted the service? I know this is an old thread but would be useful to know if it fixed the issue and if you experienced any negative impacts of enabling T1800.
Thanks
Angeline
June 16, 2020 at 7:26 pm
Angeline,
I sure do wish I had updated this thread after. 🙁
I honestly can not recall if the restart solved the problem. I did manage to find some notes I still have on the migration and I make no more mention of the issue after my last post, and in fact I found notes detailing that I had started using the secondary as a read-only replica. This would seem to indicate the issue was solved.
I wish I could say more 🙁
June 17, 2020 at 7:59 am
No worries, I knew it was a long shot to ask if you remember restarting a server 5 years ago ??
Thanks for the reply anyway.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply