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:
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.
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 SP2A 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.
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.
To verify the kind of drive that you have, follow these steps:
- 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.
- 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