Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Maintenance Plan Best Practice Expand / Collapse
Author
Message
Posted Wednesday, June 25, 2008 1:31 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 10:06 AM
Points: 208, Visits: 146
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
Post #523667
Posted Wednesday, June 25, 2008 2:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 11:24 AM
Points: 33,088, Visits: 15,197
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
Post #523709
Posted Wednesday, June 25, 2008 10:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 17, 2011 9:15 AM
Points: 44, Visits: 3,626
You can even create jobs to update statistics.


Post #523856
Posted Thursday, June 26, 2008 8:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 10:06 AM
Points: 208, Visits: 146
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
Post #524220
Posted Thursday, June 26, 2008 8:33 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, January 2, 2014 10:30 AM
Points: 646, Visits: 731
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
Post #524230
Posted Thursday, June 26, 2008 8:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 17, 2014 7:55 AM
Points: 2,805, Visits: 3,067
Why not shrink the database?
Post #524263
Posted Friday, June 27, 2008 12:18 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 6:25 AM
Points: 149, Visits: 1,023
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
Post #525261
Posted Friday, June 27, 2008 2:17 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 4:44 AM
Points: 194, Visits: 1,142
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
Post #525340
Posted Friday, June 27, 2008 2:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 3, 2008 2:04 PM
Points: 23, Visits: 92
So,your environment is 7X24?


MCDBA, MCITP (DB Dev, DB Admin), MCSE,MCTS, OCA 10g
Post #525348
Posted Friday, June 27, 2008 2:42 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:28 AM
Points: 4,388, Visits: 9,508
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #525350
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse