| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2003-12-01 : 12:08:01
|
| We're moving from a DB strategy with limited lifetime to a longer life strategy.I'll be needing to implment regular maintenance tasks - backup, compacting, reindexing.Does anyone have a stored proc that will generate all the DBCC REINDEX commands for nightly execution or is this a bad approach ?Sam |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-01 : 13:14:19
|
| SELECT 'DBCC DBREINDEX([' + u.name + '.' + o.name + '], '''', 80)'FROM sysobjects oINNER JOIN sysusers u ON o.uid = u.uidWHERE o.name <> 'dtproperties' AND o.type = 'U'ORDER BY o.nameRun the above once to generate the output. Then run some of the statements each day. I typically spread out the larger tables over the week.Tara |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-12-01 : 13:33:17
|
| Thanks Tara. I modified the script to time each REINDEX.Sam |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-01 : 13:41:09
|
| One other thing that needs to be included in your maintenance routines is DBCC CHECKDB. This will check for database corruption.Tara |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-12-01 : 13:46:06
|
| Thanks. Running it locally, my DB gets a clean bill of health on my test system. I'll run these on the production db tonight.Looks like these scripts must be run interactively and viewed for possible errors.Sam |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-01 : 13:49:58
|
| What we do is setup failure notifications on the jobs. On failure, we get paged for important jobs like these. It's better to know about these things at 2am (or whenever they run) so that you have to time to fix it before the customers start really hitting your system.Tara |
|
|
|
denisemc
Starting Member
26 Posts |
Posted - 2003-12-01 : 13:54:05
|
quote:
Does anyone have a stored proc that will generate all the DBCC REINDEX commands for nightly execution or is this a bad approach ?
As always, it depends. If you have a daily maintenance window where users are not connected to the system, then you can run DBREINDEX during that time. But if you have a 24 x 7 uptime requirement, then DBREINDEX can interfere with regular system usage since it holds locks that will block users' db access. I use a combination of DBREINDEX and INDEXDEFRAG. INDEXDEFRAG is an online operation. While it is not as effective as completely rebuilding the index, it will not interfere with user activity.Denise |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-01 : 13:58:00
|
| Yes that is true. I have not needed to run INDEXDEFRAG thus far even for my databases that are several gigabytes in size. I've always been able to split up the tables so that only a few are done each day. I have run INDEXDEFRAG during the day manually as needed though. I guess it just needs to be tested to see what your environment can handle. We are 24x7 here, but have found that we can run maintenance routines in the middle of the night with very little interference.Tara |
|
|
|
saglamtimur
Yak Posting Veteran
91 Posts |
Posted - 2003-12-01 : 16:34:10
|
| One more info; with DBREINDEX you can set fill factor (as %), but can not with INDEXDEFRAG. But INDEXDEFRAG not ignores or resets fillfactor to 100%. What BOL saying about this is;"DBCC INDEXDEFRAG also compacts the pages of an index, taking into account the FILLFACTOR specified when the index was created" |
|
|
|
|
ReIndexing