Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
 Posted - 2007-08-29 : 21:10:27
|
| Can someone please point me in the direction of what type of general maintenance I should be doing with my sql 2005 db and how often.My database is roughly 100 tables and is used daily by only 7 users.It is normal wear and tear. My largest table has 100,000 rows and grows by about 1500 rows a day. There is a small amount of deletions, but it does happen.The total db size is 117mb.I do daily backups with FULL recovery model.Along with the default settings.should I shrink the db after each backup?also how often should I update statistics, re-index, or whatever else I should be doing.Any help would be appriciated.Thanks! |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-30 : 00:36:33
|
| I'll do integrity check weekly, reindex daily, log backup hourly, plan and file clean up daily in this case. But will not shrink db since it's performance killer. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-30 : 04:08:41
|
| "I do daily backups with FULL recovery model."Make sure you are doing LOG backups too. I recommend every 10-15 minutes (ask yourself: why do them less frequently when they might save your database?)"should I shrink the db after each backup?"Absolutely not."how often should I update statistics, re-index"If you have a quiet period (overnight, say) then I would suggest doing them every night. Why wait until, say, the weekend with your database efficiency dropping day by day? It might be a tiny improvement, but as your database grows it will become more important, and a pain to change and re-test in the future."whatever else I should be doing"Well the Maintenance Plan Wizard Thingie in SQL 2005 pretty much covers all the normal activities. Disaster Recovery Planning would be a good thing to consider - before the disaster!See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=What%20type%20of%20backup%20should%20I%20use,Automating%20Backups,Backup,Database%20Shrinking,Shrink,Shrinking,HousekeepingKristen |
|
|
|
|
|
|
|
|
General Maintenance