1

I want to have a separate instance of Sql Server dedicated to a "reporting" role. I've decided to populate that server via Log Shipping (since mirroring won't work, and Availability Groups is crazy-expensive). I've worked out that I simply need to backup the transaction logs on an interval, copy them to a place accessible by the reporting instance, then execute the restore at the target instance.

The problem that I've not found a widely accepted solution of is that one can't restore the log without exclusive access to the target catalog. I want to refresh the data often, but have the refresh process wait for any active reports/queries being run by end-users; you can't just track open connections because of connection pooling. So the key to my solution is to be able to determine if the database catalog is "busy". I've come up with this query that lists "active" queries---those that my process should wait for. Does anyone know why this wouldn't work, or can someone confirm that it will? (e.g. does the blocking info matter beyond what I'm already checking for?)

SELECT USER_NAME(user_id) AS LoginName
    , DB_NAME(database_id) AS DbCat
    , blocking_session_id AS BlockedBy
    , open_transaction_count AS OpenTrans
    , status AS ExecStatus
    , cpu_time AS CpuTime
    , logical_reads AS LogicalReads
    , *
    FROM sys.dm_exec_requests
    WHERE session_id != @@SPID
        AND database_id = DB_ID()
        AND status != 'background' --always non-client activity; not something that should block log-shipping.
        AND (open_transaction_count > 0 --open transactions always need to be waited on
            OR status != 'sleeping' --sleeping=idle, except when there's an open transaction.
        );

Essentially, if this query returns zero rows, the catalog is idle and I do the restore. If it doesn't, then the restore doesn't happen that time around.

Granger
  • 1,150
  • 2
  • 10
  • 26
  • FYI, note that dm_exec_requests does not have rows for all open connections. For that, you'd need to switch to use sysprocesses instead (which has been deprecated since before 2008, but "everyone" still uses it in 2019). – Granger Aug 22 '19 at 22:20

2 Answers2

0

I've not tried it myself, but I think the functionality you require is built in, if you uncheck the "Disconnect users in the database when restoring backups" checkbox when you configure the log shipping secondary.

enter image description here

Andy Joiner
  • 1,273
  • 1
  • 10
  • 24
  • That's explicitly what I said I do *not* want to happen. That blindly kicks all connections regardless their actual activity. – Granger Feb 20 '14 at 19:55
  • Sorry - the description says "untick" - I have updated the screenshot to show what it looks like after it's unticked. – Andy Joiner Feb 23 '14 at 19:25
  • And what that option does is *never* restore any backups when any of your processes use connection pooling; there is almost always a connection open. – Granger Feb 24 '14 at 15:05
0

The query works great. I'm successfully using it in a restore job. No process has ever been kicked out of the database when they're in the middle of a query.

Granger
  • 1,150
  • 2
  • 10
  • 26