Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

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.

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:

Note For SQL Server 2008 R2 SP3 instances, you need to upgrade the server to the latest security update available at:

Download Security Update for SQL Server 2008 R2 SP3

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:

http://support.microsoft.com/contactus/?ws=supportNote 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" value

    Drive type

    4096

    4096

    4K native

    512

    4096

    Advanced Format (also known as 512E)

    512

    512

    512-byte native

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×