Check Mirror Status

A handy SQL contributed by Andrew R.

SELECT left(a.name,50) 'Database'
     , CASE b.status WHEN 0 THEN 'Suspended'
        WHEN 1 THEN 'Disconnected'
        WHEN 2 THEN 'Synchronizing'
        WHEN 3 THEN 'Pending Failover'
        WHEN 4 THEN 'Synchronized'
       ELSE 'Investigate status'
       END 'Mirror_Status'
     , local_time 'Time'
  FROM master.sys.databases a
     , msdb.dbo.dbm_monitor_data b
 WHERE a.database_id = b.database_id
   AND b.local_time = (SELECT max(A_ED.local_time) 
                         FROM msdb.dbo.dbm_monitor_data A_ED
                        WHERE b.database_id = A_ED.database_id
                          AND A_ED.local_time <= GETDATE())
GROUP BY a.name, b.database_id, b.status, b.local_time
ORDER BY 1,3;