What is your Database Mirror status?
If your databases are Mirrored, this one is for you. As you know, SQL Server generates events if the status changes in your mirroring session. But what if you want to get in and just check on things yourself? How do we check our database mirroring state? We have the Database Mirroring Monitor, which can be launched to return mirroring status details. Personally, I am not a big fan of GUI tools. They really are memory pigs!
To avoid the GUI, I like to check my mirror status with the script below. You can see I am using sys.database_mirroring, which contains details regarding the state of the mirrored databases. You can run this by hand to check mirror status, or you could even wrap it into a job that would notify you if the mirrored state changes, or becomes suspect. I will try to put that together, and post a sample job soon.
/*What is the state of your mirrored databases? */
-- declarations
DECLARE
@DatabaseID INT,
@State VARCHAR(55),
@IsMirrored INT,
@String VARCHAR(155)
DECLARE @databases TABLE (
DatabaseID INT, MirroringState VARCHAR(55)
)
-- query status for mirrored databases
INSERT @Databases (DatabaseID,MirroringState)
SELECT database_id, mirroring_state_desc
FROM sys.database_mirroring
WHERE mirroring_role_desc IN ('PRINCIPAL','MIRROR')
AND mirroring_state_desc NOT IN ('SYNCHRONIZED','SYNCHRONIZING')
-- walk through all mirrored databases
WHILE EXISTS (
SELECT TOP 1 DatabaseID
FROM @Databases
WHERE MirroringState IS NOT NULL
)
BEGIN
SELECT TOP 1 @DatabaseID = DatabaseID, @State = MirroringState
FROM @Databases
SET @String = 'Host: '+@@SERVERNAME+'.'+CAST(DB_NAME(@DatabaseID) AS VARCHAR)+ ' - DB Mirroring is '+@State +'. Please review.'
EXEC msdb.dbo.sp_send_dbmail 'YOUR MAIL PROFILE', 'yourEmailAddress', @body = @string, @subject = @string
DELETE FROM @Databases
WHERE DatabaseID = @DatabaseID
END
-- You can also check to see if there is no mirroring, when there should be
SELECT @IsMirrored = COUNT(*)
FROM sys.database_mirroring
WHERE mirroring_state IS NOT NULL
IF @IsMirrored = 0
BEGIN
SET @string = 'Host: '+@@SERVERNAME+' - No databases are mirrored. Please review.'
EXEC msdb.dbo.sp_send_dbmail 'YOUR MAIL PROFILE', 'YourEmailAddress', @body =
@string, @subject = @string
END
Take a look, let me know what you think. You should also take a look at both of these for additional information regarding the catalog view and the Database Mirroring Monitor tool:
Database Mirroring Monitor -
http://msdn.microsoft.com/en-us/library/ms365786.aspx
sys.database_mirroring catalog -
http://technet.microsoft.com/en-us/library/ms178655(v=sql.110).aspx