Skip to content

Check Mirroring status

February 12, 2013

msdb..sp_dbmmonitorresults ‘database_name’,1,0
go

 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.

Output:

role:
1 = Principal
2 = Mirror

mirroring_state
0 = Suspended
1 = Disconnected
2 = Synchronizing
3 = Pending Failover
4 = Synchronized

witness_status
0 = Unknown
1 = Connected
2 = Disconnected

log_generation_rate
Amount of log generated since preceding update of the mirroring status of this database in kilobytes/sec.

unsent_log
Size of the unsent log in the send queue on the principal in kilobytes.

send_rate
Send rate of log from the principal to the mirror in kilobytes/sec.

unrestored_log
Size of the redo queue on the mirror in kilobytes.

recovery_rate
Redo rate on the mirror in kilobytes/sec.

transaction_delay
Total delay for all transactions in milliseconds.

transactions_per_sec
Number of transactions that are occurring per second on the principal server instance.

average_delay
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
Time at which the row was recorded by the database mirroring monitor. This is the system clock time of the principal.

time_behind
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
System clock time on the local server instance when this row was updated.

Advertisements

From → Uncategorized

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: