Friday, February 24, 2012

Cannot shrink transaction log

Hi,

I have a t-log on one database which is 400mb when the database size is only 30mb. dbcc shrinkfile does not work and dbcc opentran shows no open transactions. When I tried to do a Backup log with truncate_only the following message displays:

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.

The database in question is not a publisher and only receives 5 subscribed articles, none of which are changed very often. The database operates 24 x 7.

Does anyone have a solution to this dilemma?

Thank YouHave you enabled the transaction checkpoint ? so that it will reduce automatically ?

dave|||Run this script (courtesy of SQL Server Professional magazine)

Sometimes a piece of data blocks the rest of the transaction log from being truncated. This script will solve your problem by created and removing garbage data until the active portion of the log loops to the beginning of the t'log.

No comments:

Post a Comment