GNUZEN (3/18/2009)
Here we have publisher database having tran. log with large size even though we have log backup and full backup in place. What is the best practice to truncate trans. log on publisher database?
The best practice, in any environment is not to ever truncate the transaction log. That means none of the following:
BACKUP LOG MyImportantDB WITH NO_LOG
BACKUP LOG MyImportantDB WITH TRUNCATE_ONLY
DUMP TRAN MyImportantDB WITH NO_LOG
All of those break the log chain. That means no log backups and no point in time restores after that until a full backup is run.
With replication, a full log is often the result of the log reader not running or running slowly. Either way, the solution is to fix the problem with the log reader. The inactive log entries cannot be removed until the log reader has processed them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability