Share via

There have been 256 misaligned log IOs which required falling back to synchronous IO.

khushboo chauhan 6 Reputation points
2022-07-11T14:24:04.243+00:00

I checked the SQL configuration manager the sql server services are stopped but i restarted but it did not worked.
i found the root cause is "There have been 256 misaligned log IOs which required falling back to synchronous IO. The current IO is on file C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\Template Data\master.mdf."

I have no idea how to go ahead with this.
Please guide

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,877 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2022-07-11T14:31:21.977+00:00
  2. Erland Sommarskog 102.2K Reputation points
    2022-07-11T21:36:24.987+00:00

    Try enabling trace flag 1800. Make sure that you have Service Pack 3 for SQL 2016 installed, because this trace flag was not there in the original release of SQL 2016.

    0 comments
  3. Seeya Xi-MSFT 16,451 Reputation points
    2022-07-12T05:45:34.52+00:00

    Hi @khushboo chauhan ,

    Welcome to Microsoft Q&A!
    Please refer to this Microsoft article: Message misaligned log IOs which required falling back to synchronous IO in SQL Server Error Log
    SQL Server storage engine logic detects the disk Sector Size and will align the Transaction Log Files metadata and internal boundaries to match the Sector Size (either 512 or 4096 bytes). The error message is generated when SQL Server has detected that Log Entries have been written assuming a Sector Size different to the Sector Size found on the current SQL Server instance. This can happen in scenarios such as the below:

    1. Log Shipping from a Production server with one disk sector size to a DR server with a larger sector size
    2. AlwaysOn or Database Mirroring from a server with one disk sector size to a DR server with a larger sector size
    3. When adding a 4K Native SSD card to hold the Transaction Log on a server where the Transaction Log was created conventional SAN disk with 512 sector size
      To ensure consistency SQL Server may switch from performing asynchronous IO to synchronous IO. This can have an adverse performance impact if technologies like synchronous AlwaysOn or Mirroring are used.
      As a best practice, Microsoft recommends having the same sector size for all disks on all replicas (at least all disks that host log files). In mixed environments, where the secondary has a physical sector of 512 bytes and the primary has a sector size of 4KB, we can use Trace Flag 1800 as a start-up flag on all servers or replicas that have the 512 byte physical sector size. This makes sure that the ongoing log creation format uses a 4KB sector size. Microsoft recommends enabling trace flag 1800 to overcome this issue.
      For more information, please refer to this blog: SQL Server misaligned log IOs which required falling back to synchronous IO

    Best regards,
    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.