sp_dbmmonitorresults (Transact-SQL)

New: 14 April 2006

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.

Topic link iconTransact-SQL Syntax Conventions

Syntax

sp_dbmmonitorresults database_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 AdventureWorks, 2, 0;

See Also

Reference

sp_dbmmonitorchangemonitoring (Transact-SQL)
sp_dbmmonitoraddmonitoring (Transact-SQL)
sp_dbmmonitordropmonitoring (Transact-SQL)
sp_dbmmonitorhelpmonitoring (Transact-SQL)
sp_dbmmonitorupdate (Transact-SQL)

Other Resources

Monitoring Database Mirroring

Help and Information

Getting SQL Server 2005 Assistance