Posted by Erland Sommarskog on 04/12/06 00:32

hallpa1@yahoo.com (hallpa1@yahoo.com) writes:
> I posted messages before about trying to purge many records (about 35%)
> of a 200Gig database. The responses gave me a lot to think about,
> especially regarding the indexes. But due to the short windows that I
> have to run in, manipulating the indexes is not an option.
>
> But this leads to another question. When all of this is done, we will
> need to shrink the db to reclaim the space. We will also need to
> rebuild the indexes, but this can be done one table at a time, so that
> might be ok. What I am looking for is advice on how to get through a
> shink of a 200G db on a fairly slow machine. Are there any 'tricks of
> the trade' that will help me get through it? I believe one of the DBAs
> said that they have not been able to shrink the db in years because it
> takes longer than the longest available window.

I'm not sure you are going shrink at all. Even if you are removing
a lot of rows from the database, I assume that new rows keep coming in
all the time? There is no point in shrinking, if it will grow again.

Reindexing on the other hand is a good idea, but this it not something
you should run when your DELETE job is done, but which should be performed
regularly. Defragmenting can be performed in two ways DBCC DBREINDEX and
DBCC INDEXDEFRAG. The first is an offline operation, that is the table
is not accessible while it's running. INDEXDEFRAG is an online operation.
Again, I'm assuming that data is inserted and updated in the
database on a regular basis.

According to Books Online, shrinking is an online operation in the sense
that uses can keep on working. I would expect it to take some load,
and I would certainly not run a shrink on office hours. A tip is to
specify a target size; that's the variation I've been most successful
with.

If you are going to shrink, which again I don't recommend, the best is
to do this when all tables have been reduced by your deletion job.
You cannot shrink one table at a time as you outlined in you other
post.

Once you have completely any shrinking, you should definitely run
defragmentation, as shrinking causes a lot of fragmentation.

--
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

[Back to original message]