sp_dbmmonitorresults (Transact-SQL)

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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.

|Applies to: SQL Server ( SQL Server 2008 through current version).|

Topic link icon Transact-SQL Syntax Conventions


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


Result Sets

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;  

See Also

Monitoring Database Mirroring (SQL Server)
sp_dbmmonitorchangemonitoring (Transact-SQL)
sp_dbmmonitoraddmonitoring (Transact-SQL)
sp_dbmmonitordropmonitoring (Transact-SQL)
sp_dbmmonitorhelpmonitoring (Transact-SQL)
sp_dbmmonitorupdate (Transact-SQL)