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

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

WORKAROUND

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.

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.