Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2003
    Posts
    27

    Question Unanswered: Recipe for a good, solid maintenance plan

    Hello

    I'm in the proces of a major revision of the maintenance plans for the SQL servers in our company, and in connection with that I would like to hear how other people are doing this.

    Here's a quick rundown of the plan:

    Critical DBs will be backed up on tape (daily incremental, weekly full, w. Veritas Backup Exec 9.0). Also, there will be full daily disk backups for easy quick recovery. These will be done locally, as I have bad experiences trying to backup across a network share.

    DBs of medium importance will be backed up fully every day on disk. These BAK-files will then be backed up on tape, if I find it necessary.

    Although I rarely restore from the tapes, I think they're nice to have in case the office burns down or who knows what.

    The maintenance plans will be split into 3:
    1. System DBs maint. plan
    2. Critical importance DBs maint. plan
    3. Medium importance DBs maint. plan

    The more I think about it, the more I think I might just classify all production DBs as critical and all test DBs as medium. Maybe that would make more sense.

    For all disk backups, optimization and integrity checks (and backup) will be done daily. For DBs of critical importance (eg. production DBs) Transaction log back will be done as well. What's a good schedule for this? Once every 3 hours or so? Every hour? How much burden does this operation put on the server?

    I guess that's about it so far. If anyone has any suggestions or comments, I would be very pleased to hear them.

    MNJ

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Frequency of trx. log backups depends on the level of activity of action queries and recoverability requirements. In one of our databases here we're doing 15-minute trx. log dumps and the resulting file varies from 800MB to 2.5GB in size. Another database barely creates a 100K logs but we're doing dumps every 30 minutes for its point-in-time recoverability requirements. It all depends.

    As per your breakdown, it looks good. But as our disaster recovery excersises shown, - it's beneficial to have your system databases backed up last. Here we're using SQLMAINT utility to run our maintenance plans (SQLMAINT -PlanName <app_db_maint_plan>). This way it's easier to sequence the steps to your likes. Also, do make sure you log all outputs, in case something goes south

  3. #3
    Join Date
    Aug 2003
    Posts
    27
    [i]As per your breakdown, it looks good. But as our disaster recovery excersises shown, - it's beneficial to have your system databases backed up last. Here we're using SQLMAINT utility to run our maintenance plans (SQLMAINT -PlanName <app_db_maint_plan>). This way it's easier to sequence the steps to your likes. Also, do make sure you log all outputs, in case something goes south
    Why is it beneficial to backup the system DBs last? Since I keep system and user DBs separated into different maint. plans, I guess I can just schedule the system maint. plan to occur 15 min. after the user main. plan?

    MNJ

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    For one, if MSDB is backed up last, - it will contain the latest backup information of all other databases, as well as itself. This information is available when looking at the General tab of database Properties window.

    As per scheduling, - as I said earlier, I have execution of all maintenance plans in one batch with SQLMAINT. If you're using Scheduled Tasks, then you can add a step with SQLMAINT -PlanName <sys_db_maint_plan> after your application databases.

  5. #5
    Join Date
    Aug 2003
    Posts
    27
    Good point with system DBs, I will take that into consideration. I suppose if I just make sure to schedule them a bit apart, it should work out ok.

    MNJ

  6. #6
    Join Date
    Oct 2004
    Location
    Vega Baja, PR
    Posts
    3

    Order of maintenance operations

    What about the order of operations within the maintenance plans - optimization, integrity check and backups? Should they be performed in a particular order? Thanks.

    Chris

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Perform the backups first. Then any other maintenance you need to do. That way, if something in your maintenance screws up your db you have recoverability.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Though Bob beet me to it...but it's a 4.5 year-old topic...Kinda odd that people choose this site over hundreds of others that "should" be covering this to the point of obscenity
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, it enticed you to visit again at least.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    yeah, post #2672, after almost as much time of silence ... So, how 'bout them CLR's (naturally speaking with Texan acsent)? In the mean time, I'm building my library here
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Oh yeah, we're ALL creating CLRs left and right here....not!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •