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 |
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
 Posted - 2007-01-24 : 15:54:31
|
| hi there i was wondering if someone check im do all this correctly.Backup first 12:00Each night i do a Index Rebuilds 1:00 AMAT_FauAT_SecAT_S2 Update Statistics used by Query OptimizerAll system tables 2:30 amAll users tables 2:45 am Database Integrity check databasesAll system tables 3:30 amAll user tables 4:00 amConfused about system tables... Is doing update statistics on system tables and database integrity the correct thing to be doing. Should i be doing dbcc reindex on system tables (i read this should not be done) I put the integrity check databases last as i want to make sure its correct after applying indexes and statisticsThanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-25 : 04:18:08
|
| If your database is in FULL Recovery Model, AND you are doing TLog Backups frequently during the day, I would consider doing the FULL Backup after the Rebuilds and Statistics Update.The Rebuilds etc. will take up considerable TLog space, and if you have to restore it will take longer to restore all that Reindex stuff before you even get to start restoring the actual user data changes!For SIMPLE Recovery Model it probably doesn't matter so much, although given the choice I would prefer my backup to be "all nicely rebuilt", so that any restore gets the database back in optimal state (given that there will have been downtime doing the restore and work lost). However, if you are doing the rebuilds every day I suspect a backup that is 23-ish hours after the previous Rebuild will still be in good shape, so I reckon this is a pedantic point.Only other thing I would worry about is if any of the steps could overlap. (We use a single Stored Procedure to run ours to ensure that they are done sequentially). You might like to put a check in place to be sure that, as your databases grow, or you add more, that none of the steps overruns into the next schedule.Kristen |
 |
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2007-01-25 : 09:02:45
|
| Single procedure seem like a good idea....as im trying to schedule it around with the database maintenance wizard.....Do you have a copy of it so i can see that be nice :)We use Simple mode....Thanks very much |
|
|
|
Kristen
Test
22859 Posts |
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2007-01-25 : 09:55:14
|
| Thanks i take a look at it and see if i can put one together.... |
|
|
|
|
|
|
|
|
Daily tasks of maintenance backups jobs