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

Applies to: SQL Server 2012 EnterpriseSQL Server 2008 R2 EnterpriseSQL Server 2012 Developer More

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:

Note After you install this update, you must enable trace flag 3057 in order to enable this fix. To enable trace flag 3057, see the Trace Flags (Transact-SQL) topic on the Microsoft Developer Network (MSDN) website.

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