Restore Log with Standby Mode on an Advanced Format disk may cause a 9004 error in SQL Server 2008 R2 or SQL Server 2012
- 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.
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.
Cumulative UpdateThis 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
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
Article ID: 2987585 - Last Review: 08/17/2015 18:58:00 - Revision: 8.0
- kbsurveynew kbexpertiseinter kbqfe kbfix kbhotfixserver kbautohotfix kbexpertiseadvanced KB2987585