I have successfully configured Microsoft SQL Server log shipping between two servers; keeping the secondary database in Standby mode. Restore job failed on the very first run with the following error:
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.
Reconfigured log shipping but restore job failed again on the first run with the same error.
Time to dig down deep!
After checking all possible options and unable to fix it, I have found that the issue is due to the disks holding log files (.ldf) on both servers. Transaction log file (.ldf) of the primary database was stored on a disk that has “Bytes per Physical Sector” set to 512 bytes. Transaction log file (.ldf) of the secondary database is located on a disk that has “Bytes per Physical Sector” set as 4,096 bytes.
In this scenario if you take, transaction log backup of the primary database and then try to restore it by using the standby option on the secondary database it fails with the above error.
I have executed the following command from an elevated command prompt to get the drive info:
Fsutil fsinfo ntfsinfo G :
To resolve this issue I have moved the transaction log file (.ldf) at the secondary server to a drive that has “Bytes per Physical Sector” set as 512 bytes. I have left the Standby file on the drive that has “Bytes per Physical Sectors” set as 4096 bytes. The other option is to restore the log backup WITH NORECOVERY instead of STANDBY