sp_dbmmonitorresults (Transact-SQL)
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.
Transact-SQL Syntax Conventions
- 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.
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. |
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.
| 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. |
Reference
sp_dbmmonitorchangemonitoring (Transact-SQL)sp_dbmmonitoraddmonitoring (Transact-SQL)
sp_dbmmonitordropmonitoring (Transact-SQL)
sp_dbmmonitorhelpmonitoring (Transact-SQL)
sp_dbmmonitorupdate (Transact-SQL)
