Maintenance Plan Best Practice

  • Good Day -

    We are currently trying to determine what the best practice would be for a maintenance plan for our SQL Server 2005 database. We currently have no maintenance plan in place other then weekly backups.

    We are looking to start running a maintenance plan on our 30 GB database. Ideally we would like to keep the database at a manageable size as well as ensure that our data retrieval does not become a bottleneck for our applications.

    We figured that asking the experts would provide better insight then taking a stab at it with our limited knowledge. Any advice that anyone can offer would be very helpful.

    Thanks

    ja_yeager

  • You should be doing log backups as well. If you aren't, then you aren't going to have recovery between full backups. I'd add in a full a day or at least a diff a day and then hourly or every few hours a day log backups.

    DO NOT shrink.

    I'd do weekly reindexes and daily or at least weekly, DBCC checks for integrity.

    Beyond that, depends on your environment.

  • You can even create jobs to update statistics.

  • Thanks for the advice. We will certainly look at creating a plan to back up the log files.

    We will certainly take the advice to not shrink the database, however I am now intrigued as to know why we should not do this? If there is another thread or article someone could point me to instead of fully explaining it that would be great as well.

    Thanks again for the help

    ja_yeager

  • I have a 75GB Database and i have space constraints on the Drives and the SYSADMIN wont listen for the past 3 years.

    So I only keep 1 days worth of Full backup's on local drive.

    I move the Backups to a DIFF Storage (CHEAP NAS) and retain atleast 5 days worth of Backups from all my sERVERS.

    Here is what i do.

    DAILY FULL Backup (Takes 2.5 hours approx)-No compression Software used/allowed.

    15 Mins TRANSACTION LOG BAckup (takes few seconds)-Retain for last 2 days.

    Do not Do transaction Log Backups while the Database is in BACKING UP.

    or the 2 hours will go up to 4-6 Hours, and there will be chain reaction on the pending JOBS etc.

    Make sure no other JOBS etc are running while the Databas is being backed up.

    I some how don't Trust OFFITE Backups, because, if there is a FAILURE, it will take me 8 hours(sending a SOR as per the SLA, then they will search for the tape and bring it into the Data Center and then restore the server IMAGE) to recover from a DB Failure, so it is a must you have something handy and accessible.

  • Why not shrink the database?

  • Mani: Your issues seem to be more political than anything else. If there is a time when you have a drive failure, you'll be thankful for the off-site backups.

    With regard to maintenance, Transaction Log backups are always a good idea, providing the database is in Full or Bulk Logged Recover Model. If not, a Full backup weekly with Differentials daily may be adequate.

    I say "adequate" because I don't know how volatile the data in your Database is. The more changes, the more frequently it should be backed up because with lots of changes usually comes a heavy reliance on that data contained.

    As for me, I'm always thinking rainy day scenario when it comes to recovery. By planning for the worst case, your almost always covered for the little hiccups.

    I take a full backup weekly, Differentials every day, and Transaction Logs every 15 minutes. All of my backups are written to local disk for the sake of expediency, but I copy those files to another Server as soon as the job has completed and hold them for 2 weeks (I have the space to do this so why not). I also write those files to a tape archive system and hold them in a 45 day rotation via an off-site storage company.

    I have recovered from some nasty disasters. The worst is losing all of the Servers in the basement Data Center in a flood. Every Server was gone, but we were able to recover to within 1 hour of failure because of the number of backups and where we copy the files.

    Food for thought.

    Regards, Irish 

  • Loner (6/26/2008)


    Why not shrink the database?

    Generally speaking, if SQL needed the file space once, it is going to need it again. Shrinking the files is usually a waste of I/O.

    When you are shrinking, you're usually during a slow period, so no big deal. However, when SQL needs more space it is almost always (just ask Murphy) when your server is at its busiest. You'll be wasting disk IO when you need it most to fulfill requests.

    If you do some one-off processing that is never going to happen again or truncate out a bunch of data that will never be replaced, go ahead and shrink. If you are tight on space, get more space. Playing the shrink game will buy you some time, but you'll be sorry you did when there is no longer room to "shrink" and your production database can't function until the drives arrive from your favorite vendor tomorrow morning.

    Kyle

  • So,your environment is 7X24?


    [font="Arial"]MCDBA, MCITP (DB Dev, DB Admin), MCSE,MCTS, OCA 10g[/font]

  • Mani Singh (6/26/2008)


    I have a 75GB Database and i have space constraints on the Drives and the SYSADMIN wont listen for the past 3 years.

    So I only keep 1 days worth of Full backup's on local drive.

    I move the Backups to a DIFF Storage (CHEAP NAS) and retain atleast 5 days worth of Backups from all my sERVERS.

    Here is what i do.

    DAILY FULL Backup (Takes 2.5 hours approx)-No compression Software used/allowed.

    15 Mins TRANSACTION LOG BAckup (takes few seconds)-Retain for last 2 days.

    Do not Do transaction Log Backups while the Database is in BACKING UP.

    or the 2 hours will go up to 4-6 Hours, and there will be chain reaction on the pending JOBS etc.

    Make sure no other JOBS etc are running while the Databas is being backed up.

    I some how don't Trust OFFITE Backups, because, if there is a FAILURE, it will take me 8 hours(sending a SOR as per the SLA, then they will search for the tape and bring it into the Data Center and then restore the server IMAGE) to recover from a DB Failure, so it is a must you have something handy and accessible.

    Mani, I would be very interested in why you recommend not performing log backups during the full backup. If you are still on 2000 - I can understand since this will block. But, if you are on 2005 then you are putting yourself at risk.

    What would happen if after 2.25 hours of your backup - your server crashed and you had to recover from your backups? You are going to lose at least 2.25 hours of data depending upon when your last transaction log backup was taken (I am assuming it is done right before the full backup).

    On 2005 - backups do not block each other.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Steve Jones - Editor (6/25/2008)


    DO NOT shrink.

    What about shrinking the logs? I never shrink my databases but once per week, when I run the maintenance on my databases, my logs grow huge, one of them grows to over 30GB in size when rebuilding indexes (I know I need to refine this process...).

    Based largely on previous experience where a 20GB log file substantially hurt performance, I setup a job to run after the maintenance jobs over the weekend that will shrink just the log files.

    Is this still a bad idea? I have a rough idea of the size the logs need to be (for normal production) so I don't shrink smaller than that.

  • Reclaiming disk space by shrinking logs I think is okay. However, if Transaction Log backups are being taken frequently enough, that should stave off any unusual file growth.

    Regards, Irish 

  • I don't recommend shrinking t-logs. Your weekly maintenance will finish more quickly if you leave the t-log file alone. As it stands, it is growing it as it is trying to write the newly re-indexed pages to the t-log.

    That being said, I'm curious to know the details surrounding your previous experience with large t-log files. During what operations was the performance degradation present? Do you recall if the t-log was being backed up regularly and/or if it was physically fragmented on the disk?

    Thanks for the information.

    Kyle

  • It was being backed up regularly (once per hour) and was regularly defragged. I don't recall exactly what I was doing to the database but the application accessing the database, when I was done, was nearly non-responsive (minute+ response times). Once I found the oversized log, I shrank it and performance returned to normal. One other thing to note, as I'm writing this, I was on SQL 2000 at the time. Perhaps this is something that was corrected in 2005?

  • Loner (6/26/2008)


    Why not shrink the database?

    http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 25 total)

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