You are currently offline, waiting for your internet to reconnect

FIX: Temporary Tables Are Not Dropped When the Merge Agent Runs in Continuous Mode

This article was previously published under Q278964
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 57700(SQLBUG_70)
When the SQL Server Merge Agent runs in continuous mode, merge replication temporary tables are not dropped. This causes the tempdb database to grow larger, which may eventually fill up the database.
The SQL Server Merge Agent creates some global temporary tables in the download phase of the merge process when the merge publication contains filters.

When the SQL Server Merge Agent schedule is set to "Start whenever SQL Agent is running" (continuous mode), it creates two new tables in every download phase. Because the SQL Server Merge Agent does not stop running, the merge process SQL connections remain open; therefore, the temporary tables are not dropped.
To work around this problem, select either the "one time" or "recurring" schedule for the SQL Server Merge Agent. This ensures that the temporary tables are dropped after each merge session.

You can also drop the temporary tables in tempdb. To do this, use one of the following methods:
  • Stop and restart the SQL Server Merge Agent. This closes the merge process SQL connections, and temporary tables are dropped.
  • If several SQL Server Merge Agents are running, stop and restart the SQL Agent Service on the Distribution Server.
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
For more information, contact your primary support provider.

Article ID: 278964 - Last Review: 10/21/2013 03:32:42 - Revision: 2.1

Microsoft SQL Server 7.0 Standard Edition

  • kbnosurvey kbarchive kbbug kbfix KB278964