Returns status rows for a monitored database from the status table in which database mirroring monitoring history is stored and allows you to choose whether the procedure obtains the latest status beforehand.
Returns the requested number of rows of history status for the specified database. Each row contains the following information:
Name of a mirrored database.
Current mirroring role of the server instance:
1 = Principal
2 = Mirror
State of the database:
0 = Suspended
1 = Disconnected
2 = Synchronizing
3 = Pending Failover
4 = Synchronized
Connection status of the witness in the database mirroring session of the database, can be:
0 = Unknown
1 = Connected
2 = Disconnected
Amount of log generated since preceding update of the mirroring status of this database in kilobytes/sec.
Size of the unsent log in the send queue on the principal in kilobytes.
Send rate of log from the principal to the mirror in kilobytes/sec.
Size of the redo queue on the mirror in kilobytes.
Redo rate on the mirror in kilobytes/sec.
Total delay for all transactions in milliseconds.
Number of transactions that are occurring per second on the principal server instance.
Average delay on the principal server instance for each transaction because of database mirroring. In high-performance mode (that is, when the SAFETY property is set to OFF), this value is generally 0.
Time at which the row was recorded by the database mirroring monitor. This is the system clock time of the principal.
Approximate system-clock time of the principal to which the mirror database is currently caught up. This value is meaningful only on the principal server instance.
System clock time on the local server instance when this row was updated.
Requires membership in the sysadmin fixed server role or in the dbm_monitor fixed database role in the msdb database. The dbm_monitor role enables its members to view database mirroring status, but not update it but not view or configure database mirroring events.
The first time that sp_dbmmonitorupdate executes, it creates the dbm_monitor fixed database role in the msdb database. Members of the sysadmin fixed server role can add any user to the dbm_monitor fixed database role.
The following example returns the rows recorded during the preceding two hours without updating the status of the database.
USE msdb; EXEC sp_dbmmonitorresults AdventureWorks2008R2, 2, 0;