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
