SQL Server 2012合并复制：代理未记录关闭故障求助

Hey there, since you're new to SQL Server replication and dealing with these frustrating merge replication errors on 2012, let's break this down step by step to get you sorted out. First, let's tackle each error type and the common fixes that work for most cases.

First: Address "The process could not connect to Subscriber" #

This is often the root cause of the nonlogged shutdown error—if the merge agent can't reach the subscriber, it'll crash unexpectedly. Here's what to check:

• Verify services are running: On the subscriber, make sure the SQL Server service and SQL Server Agent service are up and running. If either stopped, start them and set them to automatic.
• Test network connectivity: From your publisher/distributor server, ping the subscriber's server name and try telnetting to its SQL port (default is 1433) to confirm there's no firewall, router, or network rule blocking traffic.
• Check agent login permissions: The account running the merge agent job needs:
  • db_owner or replmonitor rights on the publisher and distributor databases
  • db_owner rights on the subscriber database
  • Permission to connect to the subscriber's SQL instance directly. Test this by logging into the publisher server with the agent account, opening SSMS, and connecting to the subscriber instance—if it fails, fix the login first.
• Validate subscription connection strings: Double-check the subscriber's server name, instance name, and authentication method (Windows vs SQL auth) in the subscription properties. A typo here is way more common than you'd think!

Next: Fix "The merge process failed because it detected a mismatch between the re..." #

This truncated error usually points to metadata or data inconsistencies between publisher and subscriber. Here's how to resolve it:

• Regenerate the publication snapshot: Outdated snapshots can cause schema mismatches. In SSMS, expand Replication > Local Publications, right-click your publication, go to Properties > Snapshot, and click Generate Snapshot Now. Wait for it to complete before moving on.
• Reinitialize problematic subscriptions: For the 13 failing subscriptions, right-click them in Local Subscriptions and select Reinitialize Subscription. Choose:
  • "Generate a new snapshot and apply it" if you can overwrite subscriber data (safe if subscriber is read-only or you don't mind refreshing data)
  • "Use existing data and schema" if you need to keep subscriber data (only do this if you're sure data is consistent with the publisher—otherwise you'll get more conflicts)
• Check merge agent verbose logs: To get the full error message (the part after "mismatch between the re..."), edit your merge agent job:
  1. Open SQL Server Agent > Jobs, find the failing merge agent job
  2. Go to Steps > Edit the "Run agent" step
  3. Add -Verbose 2 to the end of the command line
  4. Run the job again, then check the agent log directory (default path: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log) for the detailed log. This will tell you exactly which object (table, index, replication system table) is causing the mismatch.

Finally: Resolve "detect nonlogged agent shutdown" #

This error is usually a side effect of the connection or mismatch issues above, but here are a few extra checks:

• Check system/SQL error logs: Look in the Windows Event Viewer (System logs) and SQL Server error logs on both publisher/distributor and subscriber for clues—like out-of-memory errors, CPU spikes, or system crashes that might have killed the agent process.
• Apply SQL Server 2012 patches: SQL Server 2012 has several service packs and cumulative updates that fix replication bugs. Make sure both publisher/distributor and subscriber are running the latest SP/CU (SP4 is the final service pack for 2012, plus the latest CU for SP4).
• Check agent resource limits: If your server is under heavy load, the merge agent might be getting killed by the OS. Monitor CPU, memory, and disk I/O during agent runs to see if resources are maxed out.

Quick Recap for Newbies #

Since you're new to replication, start with the basics: confirm connections and permissions first, then use verbose logs to get specific error details, and don't be afraid to regenerate snapshots/reinitialize subscriptions—these are standard fixes for merge replication issues.

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