Database Maintenance Plan

Posted on 2012-09-11
Last Modified: 2012-09-26
I'm just getting back into SQL Server after a hiatus since 2002, and need some guidance on constructing a maintenance plan for a clients SQL Server.

They currently have a Maintenance Plan that looks like the attached.maintenance plan  But it looks to me like they are shrinking the database before backing it up.  Lets just assume, for the time being that their backup frequency (2 days) is about right.  Should they be doing the backup before or after the Shrink Database task

As I recall from my days using 2000, there was an issue regarding the growth of transaction logs.  I believe we had to manually shrink those logs after we backed up the database.  Do you still have do to that?  I don't see a tasks for doing that in the Maintenance Plan Tasks list. Can you add that task to the script?

I also understand that Shrinking the database can damage index files.  Do I need to add a Rebuild or Reorganize Index Task to the plan.

Any other steps I'm missing for a database maintenance plan?
Question by:Dale Fye (Access MVP)
    LVL 16

    Assisted Solution

    Personally, I'd do the backup first, in case anything is damaged and does more harm during the shrink operation.

    Yes, you can add things to the maintenance plan - it's just an SSIS task.

    In addition, when you've made a backup, you should restore it to make sure that it really is a backup, and not a pile of unrelated bits and bytes! You don't need to do that every single time, but once a month (IMHO), or more frequently, depending on the value of your data.

    Once you've recovered to another machine you can do some of the DBCC checks to see if there is corruption - if it's there on the recovery it'll be there on the original (or else it's a bad backup!)


    LVL 3

    Accepted Solution

    I would set the database to backup first, then perform the shrink.  Then, add a reorganize operation at the end due to the fact that the shrink will fragment your indexes.  Also, I would only do the shrink if it is absolutely necessary.  Keep in mind that this only applies to database files.  It is perfectly fine to shink log files as they are sequential.  Database files are random disk I/O.
    LVL 47

    Author Closing Comment

    by:Dale Fye (Access MVP)
    Thanks,<br /><br />Appreciate the input.  I've done some more reading and agree that there is no real reason for the database shrink, as I am not deleting records from this db, so there should be no requirement to shrink.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now