Assistance with Maintenance Plan configurations SQL 2005
-
For you professional DBA's out there....what do your daily and weekly MP's look like?
Do you check DB integrity each day? etc.
I've been using the Admin Pocket Consultant for MS SQL 2005 and its been a great help for most things and the websites are helpful too but I'd really like to hear from people who do this every day with real world examples of Maintenance Plans.
Thanks again!
- Newb DBA
-
depends on the size of your databases, availability requirements and other tasks
generally though i run a defrag and checkdb every weekend (with the exception of our 600Gb database) and nightly backups with 1 hour t-log backups
i also never automatically claim space back, as this can be counterproductive sometimes - if the log needs 1Gb of space to do a defrag then why shrink it and then have it grow back again - it will only take longer do the defrag
MVDBA
-
Thank you Michael.
So you do a full backup as well as a t-log backup? So the full backup doesnt include the t-logs?
Currently I'm working with a new SQL 2005 server running Server 2003 for OS. THe DB is new and only 170mb but expected to grow rapidly once its live. We have a couple user db's and the standard system db's so at the moment I'm just trying to get a good maintenance plan in place to keep them clean, functional and restorable in case of disaster.
I currently have a maintenance plan in place for the user db's and one for the system db's both of them are basically doing everything from checking integrity to full backup every night. I know this isnt the way it should stay but I didnt want to miss anything.
Any direction would be SUPER appreciated. Thanks
Sorry for the ignorant questions....these new responsibilies have been thrust upon me so i've literally become a DBA over night. I'm up to my chin in books, web links and newsgroup threads trying to get up to speed.
(though I have to admit i'm having fun along the way). -
you've come to the reight forum for information
i'm guessing that you may not know about the recovery model for databases if you've been trown in at the deep end
in short
you can find the recovery model by right clicking on a database and selecting properties it's either simple, full or bulk logged (ignore bulk logged for now)
simple reovery mode allow you to back up the database ightly and that's about it (ok - you can back it up whenever you want)
in simple mode t-log backups will do absolutley zip and will most likely fail
in FULL recovery mode the log will grow until you back it up and then it will truncate and begin growing again - and so the cycle continues - this mode allows you to perform a point in time recovery (assuming you kept the backup files for long enough)
if you don't perform a t-log backup in FULL mode then expect disaster when your database consumes the entire drive
you can't recover from t-log backups alone - you need a datbase backup as well.
database bakups don't include a log backup - and vice versa. you need one database backup and ALL the log files since then for a point in time restore.
i would advise nightly backups and hourly log files if your in FULL mode and Nightly backups without log backups in SIMPLE mode
hope this helps
MIKE V
MVDBA
-
Thank you very very much for the tips Michael!!! I knew I was missing several things....I'm glad to know now that I need a full backup of the database as well as the t-log backup!! Im just glad to find out now rather than weeks from now and end up needing to do a restore.
I have our production databases setup with a recovery mode of FULL and all the other databases...dev, stage, etc set to simple since a point in time recovery is not needed there. (that sound about right?)
All I need to figure out now is how I should manage the other MP options....eg. DB Integrity checks, shrink database, rebuild indexes, update statistics, cleanup history. These all seem important but Im certain that a shrink or integrity check isnt necessary every night...maybe even not once a week since the DB is under 200mb still.
This is a great help!! Thanks again.
-
sounds about right
in that case i recommend 3 maintenance plans 9the easiest way to implement that
one for system databases
one for user databases in simple recovery mode (no t-log backups)
one for user databases in full recovery mode (t-log backups requried)
actually this equates to 3 slightly different plans if you do it right
one for system databases - weekly backups
one for all user databases - daily backups, checkdbds,defrags etc NO T-LOG BACKUPS
one for t-log backups for databses set in full mode (hourly)
the all user databases maint plan is agood idea as if you add a new database it automatically gets added in;
if you then creat a new maint plan pureley for the t-log backups of those databses in full mode you should be coveered (please note - don't do any other task in this plan as it will be also included in the "all user databases" maintenance plan)
as for your shedule - here's by best guess
all user databases backup - nightly
system databases backup - weekly
t-log backups for user databases - hourly between 8am and 6pm (working hours)
checkdb(integrity check) - weekly on sunday
shrink dataase - do it manually if requried(after abnormal operation) don't do automatically
rebuild indexes - weekly on sunday
update statistics - weekly on wednesday night (midweek)
cleanup history - daily
MVDBA
-
Wow
Excellant....exactly what I was looking for. A nice simple layout to get me started with explanations and everything. Even with all the books and web sites it basically came down to figuring it out myself. So this is really helpfull! I can sleep tonight! Thank you Michael!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply