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 |
|
martinch
Starting Member
35 Posts |
 Posted - 2005-01-27 : 10:31:02
|
Hey all,At the moment, we have a relatively small database (a whole 20MB  ), being used by around 10 users, with most of the operations being read operations. The "maintenance plan" consists of several stored procedures & scheduled jobs, which do the following:* Re-Index (DBCC DBReIndex), Index Defrag (DBCC IndexDefrag), and Update Usage Statistics (DBCC UpdateUsage) every night.* Backup transaction log twice a day - at lunch time, and at home time.* Full backup of the database once a week.(most of these are based around Tara's SP's, which were most helpful - if you read this, thanks!  )Should we be running DBCC CheckDB on a weekly basis too?Is that everything we should be doing, or are we missing anything? I appreciate that it's not doing a backup very often, but the data really isn't updated/inserted that often, so I don't really see any point in doing a TLog backup every hour or something (and there's a paper hard-copy of everything anyway...).Thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-27 : 16:14:34
|
| Hey, no problem!I run DBCC CHECKDB daily as if there's any database corruption, I want to know sooner than later about it. I wouldn't run REINDEX and INDEXDEFRAG on the same night as they sort of do the same thing. I run REINDEX weekly and INDEXDEFRAG about twice a week. Neither are run on the same day though.You might consider also doing update statistics each day. It's different than DBCC UPDATEUSAGE. You can use sp_updatestats for this. This one is very important for performance, although with such a small db size I'm not sure you are going to notice.Tara |
 |
|
|
martinch
Starting Member
35 Posts |
Posted - 2005-01-28 : 05:31:51
|
| Thanks for you reply, Tara! I've taken your advice (naturally!) and changed it so:* Re-indexing and update usage are done every Friday* Index defragmentation is done every Tuesday and Thursday* Update Statistics is run nightly* DBCC CheckDB to be run nightlyBackups are done as before.Thanks for the help - I'm sorry if I seem a bit newbie-ish (is that a real word..?), but I'm relatively new to this!PS I know the database is only small at the moment (it only contains the last 3 years research students for our department), but it will be added to over the next year or so (undergrads, historical records, marks, and staff), so I want to get everything right now (so I don't have to worry then! ) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-28 : 11:33:36
|
Hey even if you are a newbie, you are one step (maybe 10 steps) ahead of other newbies by not using the built-in maintenance plans of SQL Server. So congrats on that!  Tara |
|
|
|
|
|
|
|
|
Maintenance Plan - Comments/Suggestions Please