Microsoft KB Archive/278964

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

Article ID: 278964

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q278964



BUG #: 57700(SQLBUG_70)



SYMPTOMS
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.



CAUSE
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 &quot;Start whenever SQL Agent is running&quot; (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.



WORKAROUND
To work around this problem, select either the &quot;one time&quot; or &quot;recurring&quot; 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.



STATUS
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.

Keywords: kbbug kbfix KB278964

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.