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

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:

  1. Truncate Table msrepl_commands
  2. DBCC Shrinkfile(distribution,1)
  3. Backup Log Distribution with truncate_only
  4. 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;

References:

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s