Microsoft KB Archive/833044

= BUG: You receive an error message when you copy a large subscription database =

Article ID: 833044

Article Last Modified on 4/6/2004

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



Bug #: 470737 (SQL Server 8.0)



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  is the full path of the subscription database file that is being copied):

The size of the file ' ' 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

</li> 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 &quot;Copying Data Between Servers&quot; topic in SQL Server Books Online.</li></ul>

<div class="moreinformation_section">

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.
 * 1) Right-click the pull subscription, and then click Copy Subscription Database.
 * 2) In the File Name box, type a name for the subscription database file, and then click Save.

<div class="references_section">