Database cannot be opened. It is in the middle of a restore?

Question

I've set up log shipping using standby mode （The "Disconnect users in the database when restoring backups" is unchecked).

I tested running the following code on secondary server/database.

declare @a int
while 1=1
begin
  select @a = count(*) from ....
end

However, the code stops with the following error when log shipping restore job start to run.

Database cannot be opened. It is in the middle of a restore.

How to avoid the issue?

Answer 1

This is kind of how log shipping has to work. You need exclusive access to a database to apply a log backup, which means your script has to set the database to single_user or otherwise evict all the users before it can apply the log backup(s).

Some workarounds:

1. Schedule your restores for outside of business hours, and let them continue querying stale data in the meantime.
2. Queue up multiple log backups and apply them, say, every hour or every two hours. Users get periodic updates throughout the day, with interruptions less frequent than the log backup schedule on the primary.
3. Don't force your script to kick users out. This means the log restore will wait and wait and wait until there are no active connections to the database. I've never actually tried this, and its feasibility really depends on how active your secondary is.
4. Have multiple secondaries and cycle through them. I've written pretty extensively about this approach.

Answer 2

Well the first thing I would check is if the database is actually restoring, this is the query I use to report on the status.

SELECT session_id AS SPID
          ,command
          ,a.TEXT AS Query
          ,start_time
          ,percent_complete
          ,dateadd(second, estimated_completion_time / 1000, getdate()) AS estimated_completion_time
    FROM sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
    WHERE r.command IN ('BACKUP DATABASE','RESTORE DATABASE')