BUG: You receive an error message when you copy a large subscription database (833044)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
Bug #: 470737 (SQL Server 8.0)
SQL Server 8.0:470737

SYMPTOMS

You try to copy a subscription database that subscribes to a merge replication using pull subscriptions, and then you save the subscription database as a Microsoft Subscription File (.msf). After you do so, you may receive an error message that is similar to the following (where Subscription Database Path is the full path of the subscription database file that is being copied):
The size of the file 'Subscription Database Path' has exceeded the limit of 2146435071 bytes imposed by the compression mechanism, retry the operation again with a smaller data set if possible.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

CAUSE

This problem occurs because of a limitation in Microsoft Windows. The Windows operating system cannot compress or decompress a file that is more than 2 GB. Therefore, if the subscription database is more than 2 GB, you cannot copy it.

WORKAROUND

To work around this problem, use an appropriate subscription database that is less than 2 GB. You can also create a copy of your subscription database on the instance of Microsoft SQL Server that you want, and then set up a new replication topology that includes the new database. To create a copy of your subscription database on the instance of SQL Server, use one of the following methods:
  • Method 1: Back up the database and the transaction logs, and then restore them to a new database in the location that you want. Drop any unwanted objects after you restore the database and the transaction logs. For more information, see the following topics in SQL Server Books Online:
    • Backing up and Restoring Replication Databases
    • Restoring Backups of Replicated Databases to a Different Server or Database
  • Method 2: Create a Data Transformation Services (DTS) package that runs a Create script of the objects that you want, and then use a Transform Data task to move the data between the databases.
  • Method 3: Use the Copy Database Wizard to copy the database in the location that you want. Drop any unwanted objects after the database is copied.
    For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

    274463 INF: Understanding and troubleshooting the Copy Database Wizard in SQL Server 2000

  • Method 4: Use the bcp utility to bulk copy the data from one SQL Server database to the other SQL Server database. For more information, see the "Copying Data Between Servers" topic in SQL Server Books Online.

MORE INFORMATION

Steps to Reproduce the Problem

To reproduce the problem, follow these steps:
  1. Start SQL Server Enterprise Manager.
  2. Identify a database that is more than 2 GB.
  3. Create a merge publication for the database that you identified in step 2.
  4. Set the properties of the merge publication.
  5. Click the Subscription Options tab, and then under Subscription creation, click to select the Allow new subscriptions to be created by attaching a copy of a subscription database check box.
  6. Click OK.
  7. Create a pull subscription for the merge publication that you created in step3, and then synchronize the subscription.

    Note Make sure that you create an initial snapshot for the merge publication by starting the corresponding Snapshot Agent.
  8. Right-click the pull subscription, and then click Copy Subscription Database.
  9. In the File Name box, type a name for the subscription database file, and then click Save.

REFERENCES

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

320499 HOW TO: Manually synchronize replication subscriptions by using backup or restore

Modification Type:MajorLast Reviewed:4/6/2004
Keywords:kbReplication kbfile kbdocerr kberrmsg kbBug KB833044 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.