Applies to: SQL Server (all supported versions)
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.
sp_dbmmonitorresults database_name , rows_to_return , update_status
Specifies the database for which to return mirroring status.
Specifies the quantity of rows returned:
0 = Last row
1 = Rows last two hours
2 = Rows last four hours
3 = Rows last eight hours
4 = Rows last day
5 = Rows last two days
6 = Last 100 rows
7 = Last 500 rows
8 = Last 1,000 rows
9 = Last 1,000,000 rows
Specifies that before returning results the procedure:
0 = Does not update the status for the database. The results are computed using just the last two rows, the age of which depends on when the status table was refreshed.
1 = Updates the status for the database by calling sp_dbmmonitorupdate before computing the results. However, if the status table has been updated within the previous 15 seconds, or the user is not a member of the sysadmin fixed server role, sp_dbmmonitorresults runs without updating the status.
Return Code Values
Returns the requested number of rows of history status for the specified database. Each row contains the following information:
|Column name||Data type||Description|
|database_name||sysname||Name of a mirrored database.|
|role||int||Current mirroring role of the server instance:
1 = Principal
2 = Mirror
|mirroring_state||int||State of the database:
0 = Suspended
1 = Disconnected
2 = Synchronizing
3 = Pending Failover
4 = Synchronized
|witness_status||int||Connection status of the witness in the database mirroring session of the database, can be:
0 = Unknown
1 = Connected
2 = Disconnected
|log_generation_rate||int||Amount of log generated since preceding update of the mirroring status of this database in kilobytes/sec.|
|unsent_log||int||Size of the unsent log in the send queue on the principal in kilobytes.|
|send_rate||int||Send rate of log from the principal to the mirror in kilobytes/sec.|
|unrestored_log||int||Size of the redo queue on the mirror in kilobytes.|
|recovery_rate||int||Redo rate on the mirror in kilobytes/sec.|
|transaction_delay||int||Total delay for all transactions in milliseconds.|
|transactions_per_sec||int||Number of transactions that are occurring per second on the principal server instance.|
|average_delay||int||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_recorded||datetime||Time at which the row was recorded by the database mirroring monitor. This is the system clock time of the principal.|
|time_behind||datetime||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.|
|local_time||datetime||System clock time on the local server instance when this row was updated.|
sp_dbmmonitorresults can be executed only in the context of the msdb database.
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 AdventureWorks2012, 2, 0;
Monitoring Database Mirroring (SQL Server)