--- title: "Monitoring Database Mirroring (SQL Server) | Microsoft Docs" ms.custom: "" ms.date: "03/14/2017" ms.prod: sql ms.prod_service: high-availability ms.component: "database-mirroring" ms.reviewer: "" ms.suite: "sql" ms.technology: high-availability ms.tgt_pltfrm: "" ms.topic: conceptual helpviewer_keywords: - "monitoring [SQL Server], database mirroring" - "database mirroring [SQL Server], monitoring" ms.assetid: a7b1b9b0-7c19-4acc-9de3-3a7c5e70694d caps.latest.revision: 78 author: MikeRayMSFT ms.author: mikeray manager: craigg --- # Monitoring Database Mirroring (SQL Server) [!INCLUDE[appliesto-ss-xxxx-xxxx-xxx-md](../../includes/appliesto-ss-xxxx-xxxx-xxx-md.md)] This section introduces Database Mirroring Monitor and the **sp_dbmmonitor** system stored procedures, explains how database mirroring monitoring works (including the **Database Mirroring Monitor Job)**, and summarizes the information that you can monitor about database mirroring sessions. Additionally, this section introduces how to define warning thresholds for a set of predefined database mirroring events and how to set up alerts on any database mirroring event. You can monitor a mirrored database during a mirroring session to verify whether and how well data is flowing. To set up and manage monitoring for one or more of the mirrored databases on a server instance, you can use either Database Mirroring Monitor or the **sp_dbmmonitor** system stored procedures. A database mirroring monitoring job, **Database Mirroring Monitor Job**, operates in the background, independently of Database Mirroring Monitor. [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Agent calls **Database Mirroring Monitor Job** at regular intervals, the default is once a minute, and the job calls a stored procedure that updates mirroring status. If you use [!INCLUDE[ssManStudioFull](../../includes/ssmanstudiofull-md.md)] to start a mirroring session, **Database Mirroring Monitor Job** is created automatically. However, if you only use ALTER DATABASE ** SET PARTNER to start mirroring, you must create the job by running a stored procedure. **In this Topic:** - [Monitoring Mirroring Status](#MonitoringStatus) - [Additional Sources of Information About a Mirrored Database](#AdditionalSources) - [Related Tasks](#RelatedTasks) ## Monitoring Mirroring Status To set up and manage monitoring for one or more of the mirrored databases on a server instance, you can use either Database Mirroring Monitor or the **dbmmonitor** system stored procedures. You can monitor a mirrored database during a mirroring session to verify whether and how well data is flowing. Specifically, monitoring a mirrored database enables you to: - Verify that mirroring is functioning. Basic status includes knowing if the two server instances are up, that the servers are connected, and that the log is being moved from the principal to the mirror. - Determine whether the mirror database is keeping up with the principal database. During high-performance mode, a principal server can develop a backlog of unsent log records that still need to be sent from the principal server to the mirror server. Furthermore, in any operating mode, the mirror server can develop a backlog of unrestored log records that have been written to the log file but still need to be restored on the mirror database. - Determine how much data was lost when the principal server instance becomes unavailable during high-performance mode. You can determine data loss by looking at the amount of unsent transaction log (if any) and the time interval in which the lost transactions were committed at the principal. - Compare current performance with past performance. When problems are occurring, a database administrator can view a history of the mirroring performance to help in understanding the current state. Looking at the history can allow the user to detect trends in performance, identify patterns of performance problems (such as times of day when the network is slow or the number of commands entering the log is very large). - Troubleshoot the cause of reduced data flow between mirroring partners. - Set warning thresholds on key performance metrics. If a new status row contains a value that exceeds a threshold, an informational event is sent to the Windows event log. A system administrator can then manually configure alerts based on these events. For more information, see [Use Warning Thresholds and Alerts on Mirroring Performance Metrics (SQL Server)](../../database-engine/database-mirroring/use-warning-thresholds-and-alerts-on-mirroring-performance-metrics-sql-server.md). ### Tools for Monitoring Database Mirroring Status Mirroring status can be monitored using either Database Mirroring Monitor or the **sp_dbmmonitorresults** system stored procedure. These tools can be used to monitor database mirroring on any mirrored database on the local server instance by both System administrators, that is, members of the **sysadmin** fixed server role, and user who have been added to the **dbm_monitor** fixed database role in the **msdb** database by a system administrator. When using either tool, a system administrator can also manually refresh mirroring status. > [!NOTE] > System administrators can also configure and view warning thresholds for key performance metrics. For more information, see [Use Warning Thresholds and Alerts on Mirroring Performance Metrics (SQL Server)](../../database-engine/database-mirroring/use-warning-thresholds-and-alerts-on-mirroring-performance-metrics-sql-server.md). - Database Mirroring Monitor Database Mirroring Monitor is a graphical user interface tool that enables system administrators to view and update status and to configure warning thresholds on several key performance metrics. Database Mirroring Monitor can also be used by members of the **dbm_monitor** fixed database role to view the most recent row of the mirroring status table, though they cannot update the status table. The monitor displays the status, including performance metrics, for a selected database on the **Status** tabbed page. The content of this page comes from both the principal and mirror server instances. The page is filled asynchronously as status is gathered through separate connections to the principal and mirror server instances. The monitor tries to update the status table at 30-second intervals. The update succeeds only if the table has not been updated within 15 seconds and the user is a member of the **sysadmin** fixed server role. For a summary of the information reported on the **Status** page, see [Status Displayed by the Database Mirroring Monitor](#perf_metrics_of_dbm_monitor), later in this topic. For an introduction to the Database Mirroring Monitor interface, see [Database Mirroring Monitor Overview](../../database-engine/database-mirroring/database-mirroring-monitor-overview.md). For information on launching Database Mirroring Monitor, see [Start Database Mirroring Monitor (SQL Server Management Studio)](../../database-engine/database-mirroring/start-database-mirroring-monitor-sql-server-management-studio.md). - System stored procedures You can also retrieve or update the current status by running the **sp_dbmmonitorresults** system stored procedure. Other dbmmonitor stored procedures enable you to set up monitoring, change monitoring parameters, view the current update period, and drop monitoring on the server instance. The following table introduces the stored procedures for managing and using database mirroring monitoring independently of the Database Mirroring Monitor. |Procedure|Description| |---------------|-----------------| |[sp_dbmmonitoraddmonitoring](../../relational-databases/system-stored-procedures/sp-dbmmonitoraddmonitoring-transact-sql.md)|Creates a job that periodically updates the status information for every mirrored database on the server instance.| |[sp_dbmmonitorchangemonitoring](../../relational-databases/system-stored-procedures/sp-dbmmonitorchangemonitoring-transact-sql.md)|Changes the value of a database mirroring monitoring parameter.| |[sp_dbmmonitorhelpmonitoring](../../relational-databases/system-stored-procedures/sp-dbmmonitorhelpmonitoring-transact-sql.md)|Returns the current update period.| |[sp_dbmmonitorresults](../../relational-databases/system-stored-procedures/sp-dbmmonitorresults-transact-sql.md)|Returns status rows for a monitored database and allows you to choose whether the procedure obtains the latest status beforehand.| |[sp_dbmmonitordropmonitoring](../../relational-databases/system-stored-procedures/sp-dbmmonitordropmonitoring-transact-sql.md)|Stops and deletes the mirroring monitor job for all the databases on the server instance.| The **dbmmonitor** system stored procedures can be used as an adjunct to the Database Mirroring Monitor. For example, even if monitoring was configured using **sp_dbmmonitoraddmonitoring**, Database Mirroring Monitor can be used to view the status. ### How Monitoring Works This section introduces the database mirroring status table, database mirroring monitor job and the monitor, how users can monitor database mirroring status, and how the monitoring job can be dropped. #### Database Mirroring Status Table Database mirroring status is stored in an internal, undocumented database mirroring status table in the **msdb** database. This status table is automatically created the first time the mirroring status is updated on the server instance. The status table may be updated either automatically or manually by a system administrator, with a minimum update interval of 15 seconds. The 15 second minimum prevents server instances from being overloaded with status requests. The status table is updated automatically by both Database Mirroring Monitor and the database mirroring monitor job, if running. **Database Mirroring Monitor Job** updates the table once a minute by default (a system administrator can specify an update period of 1 to 120 minutes). Database Mirroring Monitor, in contrast, updates the table automatically every 30 seconds. For these updates, **Database Mirroring Monitor Job** and Database Mirroring Monitor call **sp_dbmmonitorupdate**. The first time **sp_dbmmonitorupdate** runs, it creates the **database mirroring status** table and the **dbm_monitor** fixed database role in the **msdb** database. **sp_dbmmonitorupdate** usually updates the mirroring status by inserting a new row into the status table for every mirrored database on the server instance; for more information, see "Database Mirroring Status Table," later in this topic. This procedure also evaluates the performance metrics in the new rows and truncates rows older than the current retention period (the default is 7 days). For more information, see [sp_dbmmonitorupdate (Transact-SQL)](../../relational-databases/system-stored-procedures/sp-dbmmonitorupdate-transact-sql.md). > [!NOTE] > Unless Database Mirroring Monitor is currently being used by a member of the **sysadmin** fixed server role, the status table is automatically updated only if the **Database Mirroring Monitor Job** exists and [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Agent is running. #### Database Mirroring Monitor Job The database mirroring monitoring job, **Database Mirroring Monitor Job**, operates independently of Database Mirroring Monitor. **Database Mirroring Monitor Job** is created automatically only if [!INCLUDE[ssManStudioFull](../../includes/ssmanstudiofull-md.md)] is used to start a mirroring session. If ALTER DATABASE *database_name* SET PARTNER commands are always used to start mirroring, the job exists only if the system administrator runs the **sp_dbmmonitoraddmonitoring** stored procedure. After **Database Mirroring Monitor Job** is created, assuming that [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Agent is running, the job is called once a minute, by default. The job then calls the **sp_dbmmonitorupdate** system stored procedure. [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Agent calls **Database Mirroring Monitor Job** once a minute, by default, and the job calls **sp_dbmmonitorupdate** to update the status table. System administrators can change the update period by using the **sp_dbmmonitorchangemonitoring** system stored procedure, and they can view the current update period by using the **sp_dbmmonitorchangemonitoring** system stored procedure. For more information, see [sp_dbmmonitoraddmonitoring (Transact-SQL)](../../relational-databases/system-stored-procedures/sp-dbmmonitoraddmonitoring-transact-sql.md) and [sp_dbmmonitorchangemonitoring (Transact-SQL)](../../relational-databases/system-stored-procedures/sp-dbmmonitorchangemonitoring-transact-sql.md). #### Monitoring Database Mirroring Status (by System Administrators) Members of the **sysadmin** fixed server role can view and update the status table - Using Database Mirroring Monitor When using Database Mirroring Monitor, a system administrator can manually refresh the **Status** page, navigation tree, or **History** page. This also updates the status table, unless it has already been updated within the previous 15 seconds. To view the history of mirroring status on a given server instance, the system administrator can also click the **History** button for a server instance (on the **Status** page). The history is displayed in the **Database Mirroring History** dialog box. There, the system administrator can view some or all of the rows in the status table of the server instance. For information about the **Status** page metrics, see Performance Metrics Displayed by the "Database Mirroring Monitor," later in this topic. - Using **sp_dbmmonitorresults** System administrators can use the **sp_dbmmonitorresults** system stored procedure to view and, optionally, to update the status table, if it has not been updated within the previous 15 seconds. This procedure calls the **sp_dbmmonitorupdate** procedure and returns one or more history rows, depending on the amount requested in the procedure call. For information about the status in its results set, see [sp_dbmmonitorresults (Transact-SQL)](../../relational-databases/system-stored-procedures/sp-dbmmonitorresults-transact-sql.md). #### Monitoring Database Mirroring Status (by dbm_monitor Members) As mentioned, the first time **sp_dbmmonitorupdate** runs, it creates the **dbm_monitor** fixed database role in the **msdb** database. Members of the **dbm_monitor** fixed database role can view the existing mirroring status by using either Database Mirroring Monitor or the **sp_dbmmonitorresults** stored procedure. But these users cannot update the status table. To learn the age of the displayed status a user can look at the times in the **Principal log (***\