5

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?

1
  • I Don't see any way that you can avoid from this situation. Is the query have a special reason?
    – itzik Paz
    Jan 23, 2015 at 11:11

2 Answers 2

5

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.
9
  • It seems it may not be a great idea to use log shipping for a secondary reporting server in a production environment.
    – u23432534
    Jan 23, 2015 at 15:57
  • @u23432534 Out of the box, yep, I tend to agree. But it can work great for people willing to do a bit of work (or allow users to be interrupted) instead of paying for more robust solutions like Mirroring + Snapshots (pre-2012) and Availability Groups (2012+). If you have Enterprise Edition then, by all means, log shipping is not the optimal choice. Jan 23, 2015 at 15:59
  • Is it possible to set up mirroring to transfer the changes over FTP (there is no direct TCP connection between the servers). I know log shipping can be configured to send the backup files that way.
    – u23432534
    Jan 23, 2015 at 16:40
  • No, mirroring won't do that, but there are probably a dozen or more ways to solve that problem outside of SQL Server. Jan 23, 2015 at 16:41
  • Curiously, what's other options (inside or outside of SQL server)? Can merge replication do this?
    – u23432534
    Jan 23, 2015 at 16:48
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')
1
  • 3
    Thanks for the query, but what do I do if it's not actually restoring? The SSMS UI says my database restoring in the Object Explorer. How do I get back access to it? Apr 5, 2018 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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