Restore Log with Standby Mode on an Advanced Format disk may cause a 9004 error in SQL Server 2008 R2 or SQL Server 2012

Notice
Note After you apply the hotfix or service pack, you have to enable trace flag 3057 to make the hotfix work.
Symptoms
Consider the following scenario:
  • You have Microsoft SQL Server log shipping or Backup/Restore set up between two servers.
  • The primary database has its transaction log file (.ldf) stored on a disk that has "Bytes per Physical Sector" set as 512 bytes.
  • You take the transaction log backups of this database, and then you try to restore it by using the standby option on the secondary database.
  • The secondary database transaction log file (.ldf) is located on a disk that has "Bytes per Physical Sector" set as 4,096 bytes.
In this scenario, the restore operation fails and returns the following error message:

Error: 9004, Severity: 16, State: 6.
An error occurred while processing the log for database '<databasename>'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

After this error occurs, the secondary database goes into a suspect state.
Resolution

Cumulative Update

This issue was first fixed in the following cumulative update of SQL Server:

Cumulative Update 2 for SQL Server 2014 SP1

Cumulative Update 7 for SQL Server 2012 SP2

About cumulative updates for SQL Server

Each new cumulative update for SQL Server contains all the hotfixes and security fixes that were included with the previous cumulative update. View the latest cumulative updates for SQL Server:

Hotfix for SQL Server 2008 R2 SP2

A supported hotfix is available from Microsoft. However, this hotfix is intended to correct only the problem that is described in this article. Apply this hotfix only to systems that are experiencing this specific problem.

If the hotfix is available for download, there is a "Hotfix Download Available" section at the top of this Knowledge Base article. If this section does not appear, submit a request to Microsoft Customer Service and Support to obtain the hotfix.

Note If additional issues occur or if any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix. For a complete list of Microsoft Customer Service and Support telephone numbers or to create a separate service request, visit the following Microsoft website: Note The "Hotfix Download Available" form displays the languages for which the hotfix is available. If you do not see your language, it is because a hotfix is not available for that language.

Workaround
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

To resolve this issue, use one of the following workarounds:
  • Move the transaction log file at the destination to a drive that has "Bytes per Physical Sector" set as 512 bytes.

    Note The Standby file can still be located on the drive that has "Bytes per Physical Sector" set as 4096 bytes.
  • Restore the log backups without using the standby option. Instead of the STANDBY option, use the WITH NORECOVERY option during the restore operation.
More information
You can use the Fsutil command-line utility to determine the "Bytes per Physical Sector" value. If this parameter is not visible in the output, you must apply the hotfix that is specified in the KB982018.

To verify the kind of drive that you have, follow these steps:
  1. Run the following command at an elevated command prompt:
    Fsutil fsinfo ntfsinfo x:
    Note In this command, <x> represents the drive that you are checking.
  2. Use the values for "Bytes Per Sector" and "Bytes per Physical Sector" to determine the kind of drive that you have. To do this, use the following table.
    "Bytes Per Sector" value"Bytes per Physical Sector" valueDrive type
    409640964K native
    5124096Advanced Format (also known as 512E)
    512512512-byte native
Properties

Article ID: 2987585 - Last Review: 08/17/2015 18:58:00 - Revision: 8.0

Microsoft SQL Server 2012 Enterprise, Microsoft SQL Server 2008 R2 Enterprise, Microsoft SQL Server 2012 Service Pack 2, Microsoft SQL Server 2014 Service Pack 1

  • kbsurveynew kbexpertiseinter kbqfe kbfix kbhotfixserver kbautohotfix kbexpertiseadvanced KB2987585
Feedback