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


published."





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


Publication





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


Next





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