sp_dbmmonitorresults (Transact-SQL)
TOC
Collapse the table of content
Expand the table of content

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   

database_name
Specifies the database for which to return mirroring status.

rows_to_return
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

update_status
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.

None

Returns the requested number of rows of history status for the specified database. Each row contains the following information:

Column nameData typeDescription
database_namesysnameName of a mirrored database.
roleintCurrent mirroring role of the server instance:

1 = Principal

2 = Mirror
mirroring_stateintState of the database:

0 = Suspended

1 = Disconnected

2 = Synchronizing

3 = Pending Failover

4 = Synchronized
witness_statusintConnection status of the witness in the database mirroring session of the database, can be:

0 = Unknown

1 = Connected

2 = Disconnected
log_generation_rateintAmount of log generated since preceding update of the mirroring status of this database in kilobytes/sec.
unsent_logintSize of the unsent log in the send queue on the principal in kilobytes.
send_rateintSend rate of log from the principal to the mirror in kilobytes/sec.
unrestored_logintSize of the redo queue on the mirror in kilobytes.
recovery_rateintRedo rate on the mirror in kilobytes/sec.
transaction_delayintTotal delay for all transactions in milliseconds.
transactions_per_secintNumber of transactions that are occurring per second on the principal server instance.
average_delayintAverage 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_recordeddatetimeTime at which the row was recorded by the database mirroring monitor. This is the system clock time of the principal.
time_behinddatetimeApproximate 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_timedatetimeSystem 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.

System_CAPS_ICON_note.jpg Note


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)
sp_dbmmonitorchangemonitoring (Transact-SQL)
sp_dbmmonitoraddmonitoring (Transact-SQL)
sp_dbmmonitordropmonitoring (Transact-SQL)
sp_dbmmonitorhelpmonitoring (Transact-SQL)
sp_dbmmonitorupdate (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft