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.
.gif "Topic link icon")
Syntax
sp_dbmmonitorresultsdatabase_name
,rows_to_return ,update_status
Arguments
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.
Return Code Values
None
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. |
Remarks
sp_dbmmonitorresults can be executed only in the context of the msdb database.
Permissions
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.
Examples
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;