Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Maintenance Plan Best Practice


Maintenance Plan Best Practice

Author
Message
ja_yeager
ja_yeager
SSC Veteran
SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)

Group: General Forum Members
Points: 208 Visits: 148
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
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36471 Visits: 18764
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
sachinsamuel1
sachinsamuel1
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 3626
You can even create jobs to update statistics.



ja_yeager
ja_yeager
SSC Veteran
SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)

Group: General Forum Members
Points: 208 Visits: 148
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
MannySingh
MannySingh
SSChasing Mays
SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)

Group: General Forum Members
Points: 649 Visits: 781
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.

Maninder
www.dbanation.com
Loner
Loner
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2920 Visits: 3337
Why not shrink the database?
Jeffrey Irish
Jeffrey Irish
SSC-Enthusiastic
SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)

Group: General Forum Members
Points: 163 Visits: 1122
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 w00t
Kyle Neier ,
Kyle Neier ,
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 1188
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
sqlservercenter-793614
sqlservercenter-793614
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 92
So,your environment is 7X24?


MCDBA, MCITP (DB Dev, DB Admin), MCSE,MCTS, OCA 10g
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4468 Visits: 9836
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search