Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

«
»

Between 4096 and 512 sector size issue.

Posted by Simon Cho on 11/16/2015

There are 2 issues in case of primary server has 512 sector size, and secondary server has 4096 sector size

  • You may see below message in the SQL server error log

There have been 170613760 misaligned log IOs which required falling back to synchronous IO.  The current IO is on file

  • If you are using Log Shipping with standby mode on secondary server, standby mode may fail frequently.

Msg 9004, Level 16, State 6, Line 7
An error occurred while processing the log for database ‘MessageExchange_Sanpedro’. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

 

Please check 4k and 512 basic concept in below url.

http://blogs.msdn.com/b/saponsqlserver/archive/2014/10/02/message-misaligned-log-ios-which-required-falling-back-to-synchronous-io-in-sql-server-error-log.aspx

http://blogs.msdn.com/b/psssql/archive/2011/01/13/sql-server-new-drives-use-4k-sector-size.aspx

http://blogs.msdn.com/b/psssql/archive/2013/05/15/sql-server-storage-spaces-vhdx-and-4k-sector-size.aspx

 

Solution

https://support.microsoft.com/en-us/kb/3009974 : I don’t know how it work internally. It might force the primary to 4K alignment. Please fully test it out and apply it.

Note After you apply this hotfix, you have to turn on the trace flag 1800 to make this hotfix work correctly.

https://support.microsoft.com/en-us/kb/2987585 : This one is great. Need to turn on trace flag 3057

 

Thanks Bob ward and Robert Dorr for investigation.

This entry was posted on 11/16/2015 at 6:12 PM and is filed under Common. Tagged: , , , , . You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “Between 4096 and 512 sector size issue.”

  1. Kevin Ojunta said

    01/28/2019 at 11:19 AM

    we are on sql 2017 and even after applying T1800, misaligned I/O messages still show up on the error log. performance did improve however. is this common?

    Reply

  2. Simon Cho said

    03/07/2019 at 1:45 PM

    I just checked your message. Could you provide more detail?
    What is the exact version of your SQL. Could you send @@Version result?

    Did you enable it on Secondary or Primary?

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s

«
»