Why did the restore fail on the log shipped secondary database


I was doing some random testing for a different log shipping related scenario, when I ran into a restore failure reported by the log shipping restore job. A snippet of the error message is shown below:

*** Error: Could not apply log backup file ‘<backup file name>’ to secondary database ‘<database name>’.(Microsoft.SqlServer.Management.LogShipping) ***
*** Error: The operation cannot be performed on a database with database snapshots or active DBCC replicas.

The above error is reported as Error #5094. The error message is shown in bold above. Now this error occurred because I had created a database snapshot on the log shopped secondary database which was operating in standby (warm standby) mode. In such an event, you can only perform a log shipping restore on the secondary server after the database snapshot is dropped.

This also applies to the active DBCC replicas as well. Which means a DBCC CHECK being executed on the standby database could also cause a log shipping restore to fail. This issue would never occur if the secondary database was in no recovery mode since a database snapshot cannot be created for this log shipping operating mode.

The Ring_Buffer_Exception reports the error as:

<Error>5094</Error>
<Severity>16</Severity>
<State>2</State>

The idea of putting this blog post together was to ensure that you were aware of this behavior before you created database snapshots on your standby log shipped secondary databases for reporting purposes.

Advertisements

3 thoughts on “Why did the restore fail on the log shipped secondary database

  1. Pingback: Troubleshooting Log Shipping Issues « TroubleshootingSQL

  2. Pingback: blogs | manishkumar1980

  3. Pingback: Trouble shooting log shipping best links | satish machineni

It is always good to hear from you! :)

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s