December 2, 2010 at 9:24 am
Mirroring was suspended last night because log drive ran out of space on the principal and mirror servers. A few minutes later our hourly job that drops and recreates a snapshot from the mirror failed every hr since with this message:
The database must be online to have a database snapshot. [SQLSTATE 42000] (Error 1822).
The following was recorded in the SQL ERRORLOG when mirroring was suspended:
Database mirroring will be suspended. Server instance 'xxxx\xxxx' encountered error 9003, state 15, severity 20 when it was acting as a mirroring partner for database 'DBNAME'.
Is there a way I can check whether a snapshot can be created before dropping the existing snapshot in my hourly refresh job.
We have a lot of angry users this morning who would rather be able to access an old snapshot than nothing at all.
In the existing snapshot-refresh job I simply drop and recreate the snapshot; by the time the snapshot is dropped it's already too late if it cannot be recreated because mirroring is suspended.
How can I check from DMVs whether mirroring is suspended?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
December 2, 2010 at 4:51 pm
select d.name ,mirroring_state, mirroring_state_desc
from sys.database_mirroring dm
inner join sys.databases d
on dm.database_id = d.database_id
where d.name = '<your db name>'
December 2, 2010 at 4:53 pm
Thank you!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
December 2, 2010 at 4:58 pm
Happy to help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply