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.