Microsoft – SQLServer – Error Message :-
BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log.
This one error has being perplexing me for a while.
Checked the Maintenance job to determine if Database Transaction Logs are being backed up. No.
Started wondering whether it is an OS Scheduled Job. Nope!
Is it coming from another machine; unlikely.
Finally reviewed SQL Server Error Log and determined that it is coming at specific times – 12 AM, 6AM, 12 PM, and 6 PM.
Proceeded to review SQL Server Agent Job History and found a correlating job. The name of the Job is <DB Name> Distribution Cleanup.
Next reviewed the Job Steps:
- Truncate Table msrepl_commands
- DBCC Shrinkfile(distribution,1)
- Backup Log Distribution with truncate_only
- DBCC Shrinkfile(distribution, 1)
So it looks like I found the problem:
Backup Log Distribution with truncate_only
It is not quite valid as the Distribution Database’s recovery mode is set for SIMPLE.
Next step determine if the Database Job is supplied by Microsoft or Vendor.
Had time to go in sooner than anticipated and confirmed that Job is in fact Vendor’s.
Now I feel a bit better about progressing towards slight modification – The modification will be to comment out the step that backups up the distribution database.
--Backup Log [distribution] with Truncate_Only;
- Replication TroubleShooting