How to Monitor Database Mirroring, SQL Server?

1. SQL Server Management Studio or via
2. TSQL Queries

Monitor LOG Shipping status Using SSMS
Database Mirroring Monitor is a graphical user interface tool that enables system administrators to view and update status and to configure warning thresholds on several key performance metrics.
How to Access Database Mirroring Monitor ?
Database Mirroring Monitor can be accessed via SSMS
Open SSMS >>> SQL Server Instance >>> Databases >>> User Database, which is configured for mirroring >>> Right Click >>> Tasks >>> Select Launch Database Mirroring Monitor, as shown in following Screen Shot.
What all information we get via Database Mirroring Monitor ?
Specifically, monitoring a mirrored database allow us to know:

• Verify that mirroring is functioning.
• Basic status includes knowing if the two server instances are up, that the servers are connected, and that the log is being moved from the principal to the mirror.
• Determine whether the mirror database is keeping up with the principal database.
• During high-performance mode, a principal server can develop a backlog of unsent log records that still need to be sent from the principal server to the mirror server. Furthermore, in any operating mode, the mirror server can develop a backlog of unrestored log records that have been written to the log file but still need to be restored on the mirror database.
• Determine how much data was lost when the principal server instance becomes unavailable during high-performance mode.
• You can determine data loss by looking at the amount of unsent transaction log (if any) and the time interval in which the lost transactions were committed at the principal.
• Compare current performance with past performance.
• When problems are occurring, a database administrator can view a history of the mirroring performance to help in understanding the current state. Looking at the history can allow the user to detect trends in performance, identify patterns of performance problems (such as times of day when the network is slow or the number of commands entering the log is very large).
• Troubleshoot the cause of reduced data flow between mirroring partners.
• Set warning thresholds on key performance metrics.
• If a new status row contains a value that exceeds a threshold, an informational event is sent to the Windows event log. A system administrator can then manually configure alerts based on these events. For more information, see Using Warning Thresholds and Alerts on Mirroring Performance Metrics.

Monitor Database Mirroring status Using TSQL Statements / Queries
We can also verify / check Database mirroring status using a system stored procedure,"sp_dbmmonitorresults".

We can execute this procedure either on Principal Server or on Mirrored Server on MSDB database.

msdb..sp_dbmmonitorresults @database_name = 'Test_SQLServer_Mirroring'

OUTPUT

This procedure will return the following information.

database_name	Test_SQLServer_Mirroring
role	1 – Principal and 2 – Mirror
mirroring_state	Unknown Synchronizing Synchronized = 4 Suspended Disconnected
witness_status	Unknown 1 = Connected Disconnected
log_generation_rate	0
unsent_log	0
send_rate	0
unrestored_log	0
recovery_rate	0
transaction_delay	0
transactions_per_sec	0
average_delay	0
time_recorded	12/27/2010 4:19:49 PM
time_behind	12/27/2010 4:19:49 PM
local_time	12/27/2010 4:19:49 PM