Getting an error while restoring logs in LOG SHIPPING environment

I'm working with SQL SERVER 2005 SP2.

I have a working and a steady environment of LOG SHIPPING for quite a while.

Yesterday, the Restore job of the LOG SHIPPING in the DR server failed with the locking error (the log starts from the last line and goes up):

05:02:38.34  *** Error: The log backup file 'C:\database\LogShipping\20100927021501.trn' was verified but could not be applied to secondary database ''
05:02:37.42  *** Error: Could not apply log backup file 'C:\database\LogShipping\20100927021501.trn' to secondary database ''.
05:02:37.42  *** Error: Exclusive access could not be obtained because the database is in use.
05:02:17.04  Restored log backup file. Secondary DB: ''<c/> File: 'C:\database\LogShipping\.trn'
05:00:01.01  Disconnecting users. Secondary DB: ''
05:00:00.64  Starting transaction log restore. Secondary ID: 'f89bba95-6fa8-4ee3-8883-3bb3b63f6127'<nl/>
05:00:00.64  Retrieving restore settings. Secondary ID: 'f89bba95-6fa8-4ee3-8883-3bb3b63f6127'<nl/>
05:00:00.65  Retrieved common restore settings. Primary Server: ''<c/> Primary Database: ''<c/> 
Backup Destination Directory: 'C:\database\LogShipping'<c/> File Retention Period: 4320 minute(s)<nl/>
05:00:00.65  Retrieved database restore settings. Secondary Database: ''<c/> Restore Delay: 0<c/> Restore All: True<c/> Restore Mode: Standby<c/> 
Disconnect Users: True<c/> Last Restored File: C:\database\LogShipping\20100927014500.trn<c/> Block Size: Not Specified<c/> Buffer Count: Not Specified<c/> Max Transfer Size: Not Specified
05:00:00.54  ----- START OF TRANSACTION LOG RESTORE  -----

The next restore job, succeeded with no issues, and it restore ALL the logs, even the older ones.

So What did it happen ?

I have the check-box 开发者_如何学运维"Disconnect Users in the Database when restoring backups" checked in the LOG SHIPPING Configuration.

You can see in the log above that there is a DISCONNECTING USERS stage.

In my Google searches, I only find links that tells me to check the above check-box, or to create a procedure that kills the sessions and run it prior the restore job.

But - there WAS a users disconnection prior the restore job. perhaps an applicative user tried to connect the DB in the fraction of a second between the end of the first log restoration and the second log restoration ?

If so, how can I prevent that from happening ?

Thanks, Roni.

To overcome this, set the Retry Job property for the LS Restore SQL Agent job. This would not be entirely fool proof. Still, you can reduce the probability of an user connecting in that fraction of a second. Three retries should be enough.

I have run into this also. The issues is, when the log shipping api kills open connections to the database, it runs ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE. Technically if there is a log in attempt from the application immediately after this, it will get the single user session before the log shipping does.

Ue Logon Trigger Enable and disable before and after executing the restoration job for particulr user or host to prevent esablishing connections to the DB for particular period of time .