with purge dematerialization

The dematerialize with purge method is invoked by using the with purge option of the drop subscription command. The subscription status is set to Dematerializing at the replicate Replication Server and a drop request is forwarded to the primary Replication Server.

When the primary Replication Server gets the drop request, it stops sending updates for the subscription to the replicate Replication Server. The subscription status is changed to Dematerializing at the primary Replication Server and a drop request is returned to the replicate Replication Server.

When the replicate Replication Server gets the drop request, it creates a dematerialization queue and starts a dematerialization thread to fill the queue. The dematerialization thread selects subscription data from the replicate database and puts a row delete operation into the dematerialization queue for each row that does not belong to any other active subscription.

While the dematerialization thread is writing to the dematerialization queue, the DSI thread switches to the dematerialization queue and begins applying the deletes to the replicate database.

If the dematerialization thread encounters an error, the entire dematerialization process restarts and the same error may recur. To avoid this problem, suspend the connection to the replicate database. Then resume it using the skip transaction option. This resumes the connection with the second transaction in the queue. The first transaction is written to the exceptions log.

When dematerialization has completed, the subscription status at the replicate Replication Server is changed to Removing. The replicate Replication Server logs in to the primary Replication Server and asks it to delete the subscription from its system tables. When that request succeeds, the replicate Replication Server removes the subscription from its own system tables and the dematerialization process is complete.

If the replicate Replication Server runs out of stable queue segments while dematerializing using the with purge option:

Add a new partition to the replicate Replication Server. If no partition is available, consider using bulk dematerialization.
If you have difficulty deleting subscription data, drop the subscription using the without purge option.
If you have difficulties because of permissions, a user with the appropriate permission should issue the drop subscription command.

Table 5-8 describes solutions for dematerialization problems based on the status returned by check subscription. The Replicate Status column shows the subscription status returned by check subscription at the replicate Replication Server. The Primary Status column shows the subscription status returned by check subscription at the primary Replication Server. The Subscription State column provides a detailed description of the subscription status. The Suggested Actions column provides the solutions.

Regardless of the problem you are having, you should first check the error logs for all of the servers involved for specific information. Also check that the user who is dropping the subscription has the same login name and password at the replicate and primary Replication Servers, and that the maintenance user for the replicate database has select, delete, and update permissions on the replicate table.

**Table 5-8: Dematerialization problems—with purge option**
Replicate Status	Primary Status	Subscription State	Suggested Actions
Dematerializing/ Pending		Waiting for other subscriptions for the same replication definition and replicate database.	Look for other subscriptions being created or dropped for the same replication definition and database. If no other subscription operations are in process, wait for five minutes.
Dematerializing/ Recovering		Cannot connect to the primary Replication Server to drop the subscription.	Check replicate Replication Server error log for messages. If the user dropping the subscription does not have the same login and password at the primary and replicate Replication Servers, then: Provide the user with a Replication Server account that has the same login and password at the primary and replicate, or Have a different user with the appropriate permissions drop the subscription. Also, the user must have at least primary subscribe privileges at the primary Replication Server.
Dematerializing		The primary Replication Server is waiting for the drop request.	Investigate whether the primary Replication Server is out of queue segments. Verify that the primary Replication Server is running and that the SQM, SQT and DIST threads for the primary database are running.
Dematerializing	Dematerializing	The primary Replication Server processed the drop request and sent it to the replicate Replication Server. The replicate Replication Server is waiting for the drop request.	Check the route between the primary Replication Server and the replicate Replication Server. Check the DSI thread for the replicate database. Investigate whether the replicate Replication Server has run out of queue segments.
Dematerializing/ Recovering	Dematerializing	The primary Replication Server processed the drop request and sent it to the replicate Replication Server. The replicate Replication Server failed to create and process the dematerialization queue.	Investigate whether the replicate Replication Server has run out of queue segments. Verify that the maintenance user has select privileges on the replicate table. Check the DSI thread for the replicate database.
Removing/ Recovering	Dematerializing	The subscription has dematerialized at the replicate Replication Server, but the replicate Replication Server could not log in to primary Replication Server to remove the subscription from the system tables.	Verify that the primary Replication Server is running. If the user dropping the subscription does not have the same login and password at the primary and replicate Replication Servers, then: Provide the user with a Replication Server account that has the same login and password at the primary and replicate, or Have a different user with the appropriate permissions drop the subscription.
Removing	Dematerializing	The primary Replication Server is deleting the subscription. The replicate Replication Server is waiting for it to finish.	Wait.
Removing	Invalid	The subscription has been removed from primary Replication Server. The replicate Replication Server will remove the subscription next.	Wait.
Invalid	Invalid	The subscription has been dropped.	None.