sp_dbmmonitordropalert (Transact-SQL)

New: 14 April 2006

Drops the warning for a specified performance metric, by setting the threshold to NULL.

Topic link iconTransact-SQL Syntax Conventions

Syntax

sp_dbmmonitordropalert database_name 
    [ , alert_id ] 

Arguments

  • database_name
    Specifies the database for which to drop the specified warning threshold.
  • alert_id
    An integer value that identifies the warning to be dropped. If this argument is omitted, all warnings on the database are dropped. To drop the warning for a specific performance metric, specify one of the following values:

    Value Performance metric Warning threshold

    1

    Oldest unsent transaction

    Specifies the number of minutes worth of transactions that can accumulate in the send queue before a warning is generated on the principal server instance. This warning helps measure the potential for data loss in terms of time, and it is particularly relevant for high-performance mode. However, the warning is also relevant for high-safety mode when mirroring is paused or suspended because the partners become disconnected.

    2

    Unsent log

    Specifies how many kilobytes (KB) of unsent log generate a warning on the principal server instance. This warning helps measure the potential for data loss in terms of KB, and it is particularly relevant for high-performance mode. However, the warning is also relevant for high-safety mode when mirroring is paused or suspended because the partners become disconnected.

    3

    Unrestored log

    Specifies how many KB of unrestored log generate a warning on the mirror server instance. This warning helps measure failover time. Failover time consists mainly of the time that the former mirror server requires to roll forward any log remaining in its redo queue, plus a short additional time.

    4

    Mirror commit overhead

    Specifies the number of milliseconds of average delay per transaction that are tolerated before a warning is generated on the principal server. This delay is the amount of overhead incurred while the principal server instance waits for the mirror server instance to write the transaction's log record into the redo queue. This value is relevant only in high-safety mode.

    5

    Retention period

    Metadata that controls how long rows in the database mirroring status table are preserved.

    Note

    This procedure drops warning thresholds, regardless of whether they were specified using sp_dbmmonitorchangealert or Database Mirroring Monitor.

    For information about the event IDs corresponding to the warnings, see Using Warning Thresholds and Alerts on Mirroring Performance Metrics.

Return Code Values

None

Result Sets

None

Permissions

Requires membership in the sysadmin fixed server role.

Examples

The following example drops the retention period setting of the AdventureWorks database.

EXEC sp_dbmmonitordropalert AdventureWorks, 5;

The following example drops all of the warning thresholds and the retention period of the AdventureWorks database.

EXEC sp_dbmmonitordropalert AdventureWorks ;

See Also

Reference

sp_dbmmonitorchangealert (Transact-SQL)

Other Resources

Monitoring Database Mirroring

Help and Information

Getting SQL Server 2005 Assistance