0

I have a SQLSERVER 2008 instance with a standby configuration using log shipping.

The production server runs a LSBACKUP job every 30 minutes, ships it to the standby server and the standby will then LSRESTORE the shipped log.

The issue I'm having is the LSRESTORE job will fail if any user sessions are accessing the database, even though they don't have active transactions containing any write operations.

The databases are in (Standby / Read-Only) mode so there's no write operations that could be pending on it, simply developing the database tables in SSMS will block the LSRESTORE job with the following error log :

2017-02-14 09:29:22.87  *** Error: Exclusive access could not be obtained because the database is in use.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***

The only solution I have found is when this happens, I can set the database to single_user and back to multi_user to clear the error. I could also put the SQL commands in the SQLAGENT job in a step before the restore but it looks more like a hack than a fix !

ALTER DATABASE [db_name_goes_here] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [db_name_goes_here] SET MULTI_USER

Can my standby be protected from this issue in any way ?

  • 2
    Did you missed the option to disconnect user when restore of transaction log is about to happen ? You have this option when configuring LS – Shanky Feb 14 '17 at 15:21
  • OMG you're right that checkbox was left empty !! i.imgur.com/UXdotts.png – A_V Feb 14 '17 at 15:23
2

When you use secondary database for query processing, you have to check

database users disconnected when transaction log backups are being restored.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.