Time to shrink a database

[hallpa1]

Hi all,
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.

Thanks In Advance

[hallpa1]

(continuation of first post)

One idea that I had, which may not work at all, is as follows.

Copy a table to a temp db.
Do the shrink on the temp db.
Drop the original table.
Move the table from the temp db to the original db.

Repeat for each table in the db.

Shrink the original db.

The thought behind this is that if you shrink a db with only one table,
you can get through it much quicker than shrinking a db with all of the
tables in it. This could be done in the available window.
Then when you shrink the original db, there is less work to do since
each table was already shrunk.
I assume that because of the files behind the DBs, this would not work.
Any thoughts on this would be appreciated. I am planning on setting up
a test of this, but if it is a waste of time, please let me know.

Thanks

[Erland Sommarskog]

ha*****@yahoo.com (ha*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[hallpa1]

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? I thought that the space would not be reused until
you did a shrink to release it.
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.

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.

thanks

[pb648174]

Does anyone have a resource on index defragmentation that could be done
on a schedule for an entire database based on some automated
statistics? I see plenty of info in BOL, but whether I should use fill
factors of 80, 30 or what I have no idea. We have never done any kind
of index defragmentation - is there some way that the database itself
can just handle it?

[Erland Sommarskog]

ha*****@yahoo.com (ha*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

pb648174 (go****@webpaul.net) writes:

Does anyone have a resource on index defragmentation that could be done
on a schedule for an entire database based on some automated
statistics? I see plenty of info in BOL, but whether I should use fill
factors of 80, 30 or what I have no idea. We have never done any kind
of index defragmentation - is there some way that the database itself
can just handle it?

I guess that you can set up a maintenance job, but I think it's better
to run a separate job.

The easy way is just to run a cursor over sysobjects and run DBREINDEX
on all tables. As long as you have enough off-hours to permit this, this
may be good enough. And it's certainly far better than nothing at all.

A colleage of mine has composed a reindexing job for our system. It uses
DBCC SHOWCONTIG to check for fragmentation, and runs DBREINDEX only if
fragmentation is over 30%. For the tables with lower fragmentation, he
instead runs UPDATE STATISTICS WITH FULLSCAN INDEX on my insistence. This
is because some tables have monotonically growing keys, so they don't
fragment, but statistics easily gets out of date, as new rows are always
added outside the current intervals in the histogramme.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[pb648174]

So there isn't some source out there that has the script posted on the
net? If this is something that should be done for every single
database, why should every person write their own and go through a
development bug fix cycle to figure out what they are doing wrong? I'm
worried about throwing something together and it causing more problems
than it solves (right now we have no problems as far as I know related
to indexes or fragmentation for databases in use for years).

[Erland Sommarskog]

pb648174 (go****@webpaul.net) writes:

So there isn't some source out there that has the script posted on the
net? If this is something that should be done for every single
database, why should every person write their own and go through a
development bug fix cycle to figure out what they are doing wrong? I'm
worried about throwing something together and it causing more problems
than it solves (right now we have no problems as far as I know related
to indexes or fragmentation for databases in use for years).

I didn't say that there is not anything publicly available for this, but
I was too lazy to start searching for something. As a matter of fact the
code that we use to get the tables with 30% more fragmentation was some-
thing that SQL Server MVP Andrew Kelly posted to
microsoft.public.sqlserver.tools once. (I believe that it was that news-
group, but I could be wrong.)

SQL Server comes with maintenance plans where you can set this up,
and probably get some default. But the problem is, that this is not a
case of one size fits all. There are several factors involved: how
much data is inserted? updated? deleted? What availability requirements
do you have? Do you need to use GUIDs etc.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[pb648174]

The total database size is less than half a gig. Most of the time the
data is being read, with maybe 20% of the time being inserts and
updates. I would like an expert to give me the link so I know I'm not
getting the wrong thing.. I see plenty of stuff out there but am unsure
what the right thing to do is. I was hoping there would be
functionality in SQL 2005 that would just handle it.

[Erland Sommarskog]

pb648174 (go****@webpaul.net) writes:

The total database size is less than half a gig. Most of the time the
data is being read, with maybe 20% of the time being inserts and
updates. I would like an expert to give me the link so I know I'm not
getting the wrong thing.. I see plenty of stuff out there but am unsure
what the right thing to do is. I was hoping there would be
functionality in SQL 2005 that would just handle it.

If the database is that small, I would set up job that just loops
sysobjects and reindex every table in sight. It's not worth the effort
to anything more sophisticated.

The reason that we exempt some tables is from reindexing is mainly to
reduce execution time for the job.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[pb648174]

Ok, does running the below once per week seem reasonable for a SQL 2005
DB?

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT top 1 table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
Exec('ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR =
80)')
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

DBCC CHECKDB
go

[Erland Sommarskog]

pb648174 (go****@webpaul.net) writes:

Ok, does running the below once per week seem reasonable for a SQL 2005
DB?
Some small modifications:
DECLARE TableCursor CURSOR FOR
SELECT top 1 table_name FROM information_schema.tables

1) The TOP 1 should probably not be there.

2) Change "table_name" to quotename(table_name), so you can defrag
Northwind and its Order Details too. :-)

3) And change to correct case, in case you one day want to run it on
case-sensitive database. It's INFORMATION_SCHEMA etc.

And I will have to admit that I have not really digested the new
ALTER INDEX syntax in SQL 2005, but I guess that part looks find.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx