pause-or-resume-a-database-mirroring-session-sql-server.md

title	description	author	ms.author	ms.date	ms.service	ms.subservice	ms.topic	helpviewer_keywords
Pause & resume a database mirroring session	Learn how to pause and resume a SQL Server database mirroring session using SQL Server Management Studio or Transact-SQL (T-SQL).	MikeRayMSFT	mikeray	03/14/2017	sql	database-mirroring	how-to	resuming database mirroring database mirroring [SQL Server], sessions database mirroring [SQL Server], pausing database mirroring [SQL Server], resuming pausing database mirroring

Pause or Resume a Database Mirroring Session (SQL Server)

[!INCLUDE SQL Server] This topic describes how to pause or resume database mirroring in [!INCLUDEssnoversion] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql].

In This Topic

• Before you begin:

  Security

• To ReplaceThisText, using:

  SQL Server Management Studio

  Transact-SQL

• Follow Up: After Pausing or Resuming Database Mirroring

Before You Begin

At any time, you can suspend a database mirroring session, which might improve performance during bottlenecks, and you can resume a suspended session at any time.

Caution

After a forced service, when the original principal server reconnects, mirroring is suspended. Resuming mirroring in this situation could possibly cause data loss on the original principal server. For information about managing the potential data loss, see Role Switching During a Database Mirroring Session (SQL Server).

Security

Permissions

Requires ALTER permission on the database.

Using SQL Server Management Studio

To pause or resume a database mirroring session use the Database Properties Mirroring page.

To pause or resume database mirroring

1. During a database mirroring session, connect to the principal server instance, in Object Explorer, click the server name to expand the server tree.

2. Expand Databases, and select the database.

3. Right-click the database, select Tasks, and then click Mirror. This opens the Mirroring page of the Database Properties dialog box.

4. To pause the session, click Pause.

   A prompt asks for confirmation; if you click Yes, the session is paused, and the button changes to Resume.

   For more information about the impact of pausing a session, see Pausing and Resuming Database Mirroring (SQL Server).

5. To resume the session, click Resume.

Using Transact-SQL

To pause database mirroring

1. Connect to the [!INCLUDEssDE] for either partner.

2. From the Standard bar, click New Query.

3. Issue the following [!INCLUDEtsql] statement:

   ALTER DATABASE database_name SET PARTNER SUSPEND

   where database_name is the mirrored database whose session you want to you want to suspend.

   The following example pauses the [!INCLUDEssSampleDBobject] sample database.

   ALTER DATABASE AdventureWorks2022 SET PARTNER SUSPEND;  
   

To resume database mirroring

1. Connect to the [!INCLUDEssDE] for either partner.

2. From the Standard bar, click New Query.

3. Issue the following Transact-SQL statement:

   ALTER DATABASE database_name SET PARTNER RESUME

   where database_name is the mirrored database whose session you want to resume.

   The following example pauses the [!INCLUDEssSampleDBobject] sample database.

   ALTER DATABASE AdventureWorks2022 SET PARTNER RESUME;  
   

Follow Up: After Pausing or Resuming Database Mirroring

• After pausing database mirroring

  On the primary database, take precautions to avoid a full transaction log. For more information, see The Transaction Log (SQL Server).

• After resuming database mirroring

  Resuming database mirroring places the mirror database in the SYNCHRONIZING state. If the safety level is FULL, the mirror catches up with the principal and the mirror database enters the SYNCHRONIZED state. At this point, failover becomes possible. If the witness is present and ON, automatic failover is possible. In the absence of a witness, manual failover is possible.

Related Tasks

• Remove Database Mirroring (SQL Server)

See Also

Database Mirroring (SQL Server)