Maintenance Plan - Should data and index job be scripted?

• Tejwant Kaur-333539

  SSCommitted

  Points: 1647

  More actions

  June 26, 2006 at 6:46 pm

  #191115

  Dear All,

  Recently I created a brand new SQL box and created a miantenance plan on the System databases to do all the four tasks - Integrity checks, Database backup, Optimization and transaction log backup. However, except for the Optimization task, everything else failed. Further investigation into the mainenance plan history showed that some jobs were successful, and some weren't. So, I have some questions:

  1. The Data and Index linkage failed on Master and MSDB because the databases were not in single user mode.

  • If that is the case, then how can I make it to be in single user mode before the job is executed, and then turn it back to normal mode after it is executed? 
  • Is this the best way to do it, or should I write a script for it? What script would that be? 

  2. Backup Transaction Log failed on both Master and MSDB because this task can't be performed on these databases. Why is that?

  Please help.

  Thanks heaps in advance.

  Regards,

  TK

• Aaron Ingold

  SSCrazy Eights

  Points: 9070

  More actions

  June 26, 2006 at 9:45 pm

  #645965

  I wouldn't perform the same maintenance on system databases (master, model, msdb, tempdb) as you would on user databases.

  1. I wouldn't concern yourself with too many optimizations on master / msdb. They're usually best left alone in my experience.

  2. Any database with a recovery model of simple will fail transaction log backups. Don't change the recovery model on your system databases, just leave them out of that plan.

  I find it's best to avoid optimization and log backups on your system databases. I'm sure there's some good articles out there about best practices (with better reasoning than my "you just don't do that"-explanations) if you search around.