Transaction Log Backup Job for 'Model and MSDB DB Maintenance Plan'

Question

Post reply

Transaction Log Backup Job for 'Model and MSDB DB Maintenance Plan'

Krasavita

SSC-Insane

Points: 23138
More actions
October 31, 2008 at 8:47 am

#127337

When my Transaction Log Backup Job for 'Model and MSDB DB Maintenance Plan' runs it fails and I get error message sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed. Anyone knows why? Also I checked history for DB Maintance plan and I get error message:Backup can not be performed on this database. This sub task is ignored

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

danschl SSCertifiable Points: 5770 More actions · Answer 1

danschl

SSCertifiable

Points: 5770

October 31, 2008 at 9:15 am

#892407

msdb doesnt allow transaction log backups

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

The model and msdb databases use the simple recovery model by default. For model, a periodic full backup is all the may be necessary. For msdb, you can change the recovery model used to bull-logged or full if you believe there are sufficient updates to this database the it makes sense to run t-log backups.

Cath Trimble SSCarpal Tunnel Points: 4201 More actions · Answer 3

Have a look in the properties for those databases, you'll find that they're both in Simple recovery model, which means you can't backup the transaction log. If you want to do backups using Maintenance Plans you'll need separate plans for Full recovery model databases and Simple recovery model databases. Even better would be to have a separate plan for each database (or use scheduled jobs, which some people prefer) to give you more flexibility regarding appropriate scheduling for individual databases.

Cath

Krasavita SSC-Insane Points: 23138 More actions · Answer 4

My model is sat to ful and msdb is set to simple. The jobs were running fine before and just strat failling.

Cath Trimble SSCarpal Tunnel Points: 4201 More actions · Answer 5

msdb and model are on the same Plan, so the job will report a failure even if it did successfully backup the model transaction log, as it failed with msdb. If the job has run successfully in the past, it sounds like the recovery model for msdb has been changed. To make the job run successfully, either remove msdb from it and create a separate Plan, or put msdb into Full recovery model.

Cath

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

If you change msdb from simple to full recovery model, run a full backup. The transaction log backup of msdb will continue to fail until a full backup is completed.