Anything else I should add to my Maint Plan?

I have 2 SQL 2000 Servers running Windows 2003 Server. Both are used heavily on a daily basis and have anywhere from 30-50 databases on them at any given time. Databases are added, deleted, shrink, grow, etc. all the time.

I have 3 jobs running nightly...

Integrity check (Using a maintenance plan)
Run sp_updatestats on all user databases (manually created job)
Shrink all user databases (manually created job)
Is there anything else I should be doing? Should I be running steps 2 and 3 on all DBs rather than just the user DBs?

Also, I would like to run defrag on a regular basis. Is there anyway to schedule a defrag in Windows 2003? If not, is there a free utility out there that will do it for me?

Any reason why I shouldn't do a full scan every night?

If you are concerned about disk fragmentation then you could either take all databases off line periodically and defrag the disk in w2k3, or a better alternative might be to use a tool like diskkeeper which will defrag the disk on a continuous basis.<br /><br />NOTE that dbreindex followed by a shrink will undo all the benefit of the reindex... unless you set the shrink to not move any pages, in which case your database won't shrink. this is by design, so we're told <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />Cheers<br />Twan

All the benefits of running an 'Update Statistics with Full Scan' will be gone after doing a Shrink DB?

Do I need to run the Shrink DB first, then the Update Stats?

Hi ya,

no not update statistics, but if you do a dbcc dbreindex, then SQL will create all the indexes in the database by extending the database size and marking the pages of the old indexes as unused. If you then shrink the database then SQL will move pages from the end of the file back into the gaps free up by the reindex, which will end up fragmenting them again.

Update statistics and shrink db won't interfere with each other

Cheers
Twan

Ok, thanks for clarifying that!

quote:Originally posted by satya

But its better to keep those 2 processes seperately, its suggested to run any other until the SHRINK operation is finished.

What do you mean? Run the shrink first, then do the integrity check and update statistics?

You should not do a shrink after a dbcc dbreindex. The shrink will undo all of the benefit of the dbcc dbreindex

Cheers
Twan

The way a dbreindex works is it will increase the size of the database to create the new indexes in the newly added contiguous free space. Then as it finishes building each index it marks the previous pages that the index was taken up as now being free. This makes the last part of database file nice and contiguous but the first part of the database file is now fragmented with information from the indexes not defragged and tables with no clustered indexes.

If you now do a shrink asking SQL to move pages from the end of the file, then SQL will start with the last pages in the file and move them into the gaps in the early part of the file. This puts the indexes pretty much back where they were...

The only way that it would be ok is if you have a clustered index on every table and you do a dbcc dbreindex on every table before you do the shrink, but even then you are liekly to reintroduce some fragmentation (or at least reversal of pages, since the shrink will just start at the end of the database and move pages into the earliest available slot in the file.

Cheers
Twan