|
|
Posted by Erland Sommarskog on 04/13/06 01:00
hallpa1@yahoo.com (hallpa1@yahoo.com) writes:
> Ok, I think I understand your point about not shrinking the db. Does
> this mean that new records will be written to the space that was freed
> up by the deletes?
Not really. If you don't defragment, the likelyhood that any space will
be reused is small. But if you defragment, the tables are compressed,
and the free space moved to free extents where it indeed can be reused.
> I thought that the space would not be reused until
> you did a shrink to release it.
When I think of it, you will probably have to defragment before you
can shrink. Shrinking works with free extents I guess. So if there
are pages in a table that are 10% full, that space will not be
reclaimed by a shrink.
> However, my purge will be removing about 200 million records and the db
> only grows at about 10 million per month. So it would take a long time
> to fill up the space freed by the purge.
Less than two years.
> As for the DBREINDEX and the INDEXDEFRAG, do they produce similar
> results? The DBAs that I am doing this for seem to believe that a
> reindex will give a bigger performance boost than the defrag.
I will have to admit that I am not too well acqauinted with INDEXDEFRAG
to answer. I personally prefer DBREINDEX, but it has the drawback of
closing out users, which appears to be a concern for you.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|