Wednesday, March 14, 2007

Unable to truncate trans. log after removal of replication.

ACTION: Problems with truncating the transaction log even though the

database is not configured for replication.

RESULT: When truncating the transaction log by executing DUMP TRANSACTION db

WITH NO_LOG the following error was generated:

"The log was not truncated because records at the beginning of the log are

pending replication. Ensure the Log Reader Agent is running or use

sp_repldone to mark transactions as distributed."

Also executing sp_repldone then fails stating, "The database is not


CAUSE: The database has been left in an intermediate state where replication log records is still available in the transaction log even though SQL Server does not have publishing enabled, the database is not published and no objects are marked for replication.

RESOLUTION: Enable publishing again and then run sp_repldone and after that remove replication again.

First, enable publishing:

1. In SEM, choose Tools\Create and Manage Publications

2. Select the database whose log we want to shrink and click Create


3. Select 'Next' on the Create Publication Wizard

4. Choose Yes, use local machine as the distributor, and then click Next

5. Click OK on any messages that might appear warning that SQL Agent is running under the local system account

6. Select Transaction Replication, the click Next

7. Accept No, do not allow immediate-updating subscriptions, and then click Next

8. Accept All subscribers will be servers running SQL Server, then click


9. Click 'Publish All" then click Next

10. Accept the default publication Name and Description, then click Next

11. Accept No, create a publication without data filters, then click Next

12. Click Finish. Enter the 'sa' password if prompted. Click OK, Close,

Close to exit remaining dialog boxes.

Second, clear log of transactions marked for replication:

13. Login to Query Analyzer as 'sa' and open the database who log we want to shrink.

14. Execute "sp_repldone NULL, NULL, 0, 0, 1"

15. Check that this worked using "sp_repltrans". It may take a little time before all the transactions have been marked as distributed.

16. Execute "dump tran with no_log"

17. Confirm that the log file shrank by running "DBCC sqlperf(logspace)". It may take a little time before the % used shows as decreased.

18. Disable publishing through SEM by choosing Tools= />
Publishing= />

powered by performancing firefox