Export (0) Print
Expand All
Expand Minimize

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.

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.

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.

Note 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;

Community Additions

ADD
Show:
© 2014 Microsoft