Posted by Erland Sommarskog on 12/26/07 13:14

Neil (nospam@nospam.net) writes:
> BTW, why do they even have this shrinking thing if it's not needed? I
> mean, I don't doubt you when you say it does more harm than good; but
> why is it there in the first place if it just causes fragmentation?

Good question. Well, for the shrinking facility as such there are of
course situations where it's useful. You have just erased five years of
data, and you expect the business to be a lot calmer for the next year,
so your database is now oversized. Or you took a copy of the production
database to get a development database to play with, but you only want
a fraction of the data, so you delete most and then shrink. This
becomes even more critical if you create one database per developer.

Now, as for why they put it as an option for maintenance jobs that's a
really good question. Unfortunately, there are plenty of things in
the tools that violate best practices for the server.

> I meant either that: a) perhaps SQL 2005 might have some superior tools
> for dealing with this; and/or b) when the database is converted to SQL
> 2005 the objects might be rewritten in such a way that it would get rid
> of the bloating. But I guess no to (a) or (b).

The tools are about the same, but the behaviour may be a little different.

> The data is 2.4 GB, which is 1.4 GB more than the DB was previously (and
> when added to the index_size and unused, accounts for the additional 2
> GB). Where is this 1.4 GB coming from? I mean, there's only at most 1 GB
> of actual data in the database. So what is the other 1.4 GB? Again,
> forgive me for being dense here if you've already explained it.

The figure of 2.4 GB comes from used pages. If only 100 bytes is actually
used on a page, that counts as 8192 bytes. That is, this is another token
of fragmentation.

You could run DBCC SHOWCONTIG on some big tables to have a look at
fragmentation. But if you reindex every night, and don't shrink anything,
the situation should improve.

By the way, does this query return any rows:

SELECT * FROM sysindexes WHERE indid = 0

Or put in another way: do you have tables without a clustred index?

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