Database Maintenance Plan

Posted on 2012-09-11
Medium Priority
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
LVL 16

Assisted Solution

DcpKing earned 400 total points
ID: 38389502
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!)



Accepted Solution

dave_tiller earned 1600 total points
ID: 38414303
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 49

Author Closing Comment

by:Dale Fye
ID: 38438246
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

839 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