如何临时禁用SQL Server 2008的快照及事务复制功能？

Got it, let's figure out how to temporarily pause replication on your SQL Server 2008 instance (which doubles as publisher and distributor) instead of permanently tearing it down like the official docs cover. Since you're dealing with a planned network outage later this week, here's a safe, reversible approach to halt replication now and fire it back up once the network's back online:

Step 1: Safely Stop Replication Agents #

You don't need to delete any publications or distribution settings—just shut down the agents that handle replication traffic. This keeps your configuration intact for later.

• Stop the Log Reader Agent (critical for transactional replication):
  First, find your Log Reader job name with this query:

  SELECT name 
  FROM msdb.dbo.sysjobs 
  WHERE category_id = (SELECT category_id FROM msdb.dbo.syscategories WHERE name = 'REPL-LogReader')
  

  Then stop it using:

  EXEC sp_stop_job @job_name = 'YourLogReaderAgentJobName'
  

• Stop the Snapshot Agent (if you regularly refresh snapshots):
  Find the job name first:

  SELECT name 
  FROM msdb.dbo.sysjobs 
  WHERE category_id = (SELECT category_id FROM msdb.dbo.syscategories WHERE name = 'REPL-Snapshot')
  

  Stop it with:

  EXEC sp_stop_job @job_name = 'YourSnapshotAgentJobName'
  

• Stop all Distribution Agents (one per subscription):
  List all Distribution Agent jobs with:

  SELECT name 
  FROM msdb.dbo.sysjobs 
  WHERE category_id = (SELECT category_id FROM msdb.dbo.syscategories WHERE name = 'REPL-Distribution')
  

  Stop each one individually:

  EXEC sp_stop_job @job_name = 'YourDistributionAgentJobName'
  

• Disable auto-start for agents (optional but recommended):
  Prevent agents from trying to start automatically during the outage by setting their enabled status to 0:

  EXEC sp_update_job @job_name = 'YourAgentJobName', @enabled = 0
  

Step 2: Clear Pending Transactions (Pre-Outage Check) #

Before the network goes down, make sure all pending transactions are replicated to subscribers to avoid gaps:

-- Run this on your distributor to check for undistributed transactions
SELECT * 
FROM distribution.dbo.MSrepl_commands mc
JOIN distribution.dbo.MSrepl_transactions mt ON mc.xact_seqno = mt.xact_seqno
WHERE mt.publisher_database_id = (SELECT database_id FROM sys.databases WHERE name = 'YourPublishedDatabaseName')

If there are rows here, let the Distribution Agent finish processing them before stopping it, or trigger a manual sync if you're in a hurry.

Step 3: Resume Replication After the Outage #

Once your network is back up, reverse the steps to get replication running smoothly again:

• Re-enable all agent jobs:
  Set each agent's enabled status back to 1:

  EXEC sp_update_job @job_name = 'YourAgentJobName', @enabled = 1
  

• Start agents in the right order:

  1. Start the Log Reader Agent first to capture new transactions:

     EXEC sp_start_job @job_name = 'YourLogReaderAgentJobName'
     

  2. Start the Snapshot Agent only if you need to refresh snapshots post-outage.
  3. Start all Distribution Agents to push pending (if any) and new transactions to subscribers:

     EXEC sp_start_job @job_name = 'YourDistributionAgentJobName'
     

• Verify Replication Health:
  Confirm everything is syncing correctly with these checks:

  -- Check agent statuses
  SELECT name, current_execution_status, last_run_date, last_run_time 
  FROM msdb.dbo.sysjobs 
  WHERE category_id IN (SELECT category_id FROM msdb.dbo.syscategories WHERE name LIKE 'REPL-%')
  
  -- Check for recent replication errors
  SELECT * 
  FROM distribution.dbo.MSrepl_errors 
  ORDER BY time DESC
  

Quick Notes: #

• This method preserves your entire replication configuration—no need to rebuild publications or subscriptions from scratch.
• If you use pull subscriptions, you'll need to repeat the agent stop/start steps on each subscriber (same stored procedures apply).
• All these steps are fully supported on SQL Server 2008.

内容的提问来源于stack exchange，提问作者StevenJe