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 ?