December 30, 2010 at 10:46 am
Hello All,
I wanted to know what you think should be the order of items in the Maintenance Plans for a Nightly Backup.
This is what i am doing so far:
1. Rebuild Index Task( All User Databases/ Advanced Option - Sort Results in Tempdb)
2. Check Database Integrity Task( All User Databases)
3. Full Database Backup ( All User Databases)
4. Transaction Database Backup
5. Shrink Database Task
6. History Cleanup Task
7. Maintenance Cleanup Task
I need suggestions if i am missing something or if i need to reorder any step.
I am also attaching an attachment of the steps.
December 30, 2010 at 10:56 am
First, don't shrink. Not needed, reduces performance.
Second, the t-log backup schedule should be driven but your data loss/downtime SLA, and not necessarily be a once a day item in your plan. Most people back up their transaction logs multiple times in a day.
History is usually last, but probably doesn't matter.
In terms of index rebuilds do you need to do them every night? You ought to investigate something like Michelle Ufford's intelligent index rebuild script instead. http://sqlfool.com/2009/06/index-defrag-script-v30/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 30, 2010 at 10:56 am
chaudharyabhijit (12/30/2010)
I wanted to know what you think should be the order of items in the Maintenance Plans for a Nightly Backup.This is what i am doing so far:
1. Rebuild Index Task( All User Databases/ Advanced Option - Sort Results in Tempdb)
2. Check Database Integrity Task( All User Databases)
3. Full Database Backup ( All User Databases)
4. Transaction Database Backup
5. Shrink Database Task
6. History Cleanup Task
7. Maintenance Cleanup Task
I need suggestions if i am missing something or if i need to reorder any step.
Best DBA practices do not change with RDBMS version - they are always the same.
Having said that, list of tasks suggest you are dealing with really small databases - nothing wrong about that. If that's the case I'll say that as far as you ensure a backup/recovery policy that fits business requirements you will be fine.
Last but not least, please, pretty please, do not shrink you database.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 30, 2010 at 11:05 am
You got that right Paul, the databases are not too big, except one.
However i did not understand why we should not shrink the databases. I thought that would be a good option so the files do not grow too large.
We follow this policy:
1. Take a Nightly backup using the Maintenance Plan.
2. The .bak files are backed up using an external solution nightly, hence smaller the file size - lesser we get charged.
December 30, 2010 at 12:21 pm
You want to set a specific size for your data files that allows for growth. You DO NOT want to depend on autogrowth as it creates physical fragmentation. Shrink created internal, logical fragmentation in your tables, so performance goes down.
Data files ARE NOT like Excel or Word files. They should have pre-allocated space to handle your needs for 3-4 months, not growing as needed.
There are better technical explanations, but the gist of it is, you do not need to, and should not be regularly shrinking databases.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 30, 2010 at 12:39 pm
Steve,
What is a proper way to shrink Transaction Log files for a database that's set to a Simple Recovery mode?
Thanks.
Abhijit Chaudhary
December 30, 2010 at 1:13 pm
chaudharyabhijit (12/30/2010)
What is a proper way to shrink Transaction Log files for a database that's set to a Simple Recovery mode?
This is not needed.
Under Simple Recovery Model t-logs are automatically truncated at checkpoint time - in simple terms, at the end of each transaction.
If you see t-log growing while under Simple Recovery Model, check for long standing transactions.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 30, 2010 at 1:31 pm
You should not need to shrink, as noted above. Set the log to the size that is needed between checkpoints, or for your largest transaction.
If it grows, check the replication is also working, or open transactions are not stuck. Other than that, it should remain at a steady state. If you are shrinking it regularly, and it keep growing, you are wasting resources and time.
It needs to be the size it needs to be.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply