Yikes! My database, which had been consistently 1 gig for a long time, went
from being 1 gig to 3 gigs overnight! Looking at the nightly backups, the
database increased on average about 5-15 MB per day, and was 1.06 GB on the
Thursday night backup. Then, with the Friday night backup, it was 2.95 GB,
and has stayed that way since!

I did a Shrink on the database, but that didn't help the situation.

The only thing I could think it might relate to is the following. I
previously (about a week ago) changed a couple of tables' DateModified field
from smalldatetime to datetime. (I posted about this under a separate thread
here.) For some reason I was getting occasional errors which I believe might
have been related to the new data type. So I decided to change the data
types back to smalldatetime.

I made the table changes Thursday night, right before the backup, but after
the database optimizations. The backup Thursday night still shows the small
database size. But the backup Friday night has the large size.

So this might not be related to the table changes at all. But I know for a
fact that there isn't 3x the data in the database. Somehow the database is
bloated to 3x its size, and it's not from actual data.

Any ideas about what I can do?

Thanks!

Neil

On Mon, 24 Dec 2007 01:37:57 GMT, "Neil" <nospam (AT) nospam (DOT) net> wrote:

Check Database Properties > Files Page. It will show you how much the
db should grow once it fills up. 1MB by default, but you might have
changed it..

3GB is still very small, and hard disk space is cheap.

-Tom.

In File Properties (which I've never touched), it's set to Automatically
grow file by 10%, with unrestricted filegrowth.

And I realized that 3 GB isn't that large. Still, the fact remains that it
couldn't have tripled in size overnight though data entry. So there has to
be something else going on there. If there's bloating, then it would be good
to get rid of it. But, as noted, Shrink Database doesn't bring it back down.
So I don't know what's going on.

"Neil" <nospam (AT) nospam (DOT) net> wrote


Here's one thought I had: is it possible to look at a breakdown of the
objects in the database, and how much space each one is taking up? Perhaps
that would help to determine what's going on here.

Neil (nospam (AT) nospam (DOT) net) writes:
A very simple advice is: nothing. 3GB is not a very big database, and
I would not lose sleep over a database growing from 1GB to 3GB. Well,
maybe if I were on Express where there is a size limit, but I know you
aren't.

If you really want to find out what happened, first examine whether
it's the log file or the data that have expanded. sp_helpdb gives you
that information.

If the log file has expanded, that may be related to your change of
smalldatetime to datetime - but changing back is going to increase the
log again. In this case you should consider shrinking the log. Normally
shrinking the log is not a good idea, because it will grow again, but
if the log has grown because of some exceptional event, it's reasonable
to shrink it.

If the data file has expanded, this query shows usage per table:

select object_name(id), reserved, used, dpages
from sysindexes
where indid in (0,1)
order by reserved desc

The number are in pages of 8192 bytes. "reserved" is the total that is
reserved for the table, including indexes. "used" is how many pages of
these that actually are in use and "dpages" is the number of data pages.

If there is any table that is suspiciously big, check if any indexes has
been added recently. Also run DBCC SHOWCONTIG to see whether there is
any fragmentation.


But most of all: I wish you a really Merry Christmas, and only look
at your expanded database if you get really bored by Christmas festivities -
which I hope you don't!

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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" <esquel (AT) sommarskog (DOT) se> wrote

Thanks, Erland. Yeah, the log file's only 768 KB, whereas the MDF file is
3.3 GB.

Also, I ran the query you gave, and all the tables appear to be the sizes
they should be. At least none seemed very large, large enough to account for
2 GB.

I appreciate you saying not to worry about it. But, still, how could a
database that has been steady at 1 GB just all of sudden go from 1 GB to 3
GB in one fell swoop, for no apparent reason. And, if it did do that (and
never did anything like that before), wouldn't that mean that performance
would suffer, if there's 2 GB worth of garbage in there somehow?

Thanks, and I wish you a very Merry Christmas as well. Thanks for you
continual help!

Neil

Neil (nospam (AT) nospam (DOT) net) writes:
In that case sp_spaceused for the database should report a lot of free
space.

Do you run a regular maintenance job on the database that defragments
indexes? It might be that when you changed those columns to datetime from
smalldatetime, the tables had to be build entirely on new ground. That
is, all tables were moved and to get space to move them, the database
exploded.

It is not likely that this will cause any performance problems. Trying
to shrink the database may on the other hand, as shrinking leads to
fragmentation. To truly shrink it, you would have to rebuild from
scripts and reload. Definitely not worth it.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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" <esquel (AT) sommarskog (DOT) se> wrote

It shows about half a gig of unused space. Here's the printout:

database_size unallocated space
--------------------------------------
3355.75 MB -2571.25 MB

reserved data index_size unused
------------------ ------------------ ------------------ ------------------
6069248 KB 2477728 KB 3066760 KB 524760 KB


The maintenance job that is run nightly performs the following:

Optimizations tab:

Reorganize data and index pages
(change free space per page percentage to 10%)

Remove unused space from database files
(shrink database when it grows beyond 50 MB)
(amount of free space to remain after shrink: 10% of the data space)

Integrity tab:

Check database integrity
(include indexes)
(attempt to repair any minor problems)

Thanks!

Neil

Neil (nospam (AT) nospam (DOT) net) writes:
The negative number for unallocated space is spooky. Run it again,
but now like this:

sp_spaceused NULL, true

That will make sure the values in sysindexes are updated.

By the way, are you still on SQL 7? I seem to recall that you talked
up moving on to SQL 2005, but did that materialise?

That's OK.

But remove this one. Shrinking the database is not a good thing to do
on regular terms.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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" <esquel (AT) sommarskog (DOT) se> wrote

OK, here it is:

database_size unallocated space
------------------ ------------------
3355.75 MB 338.75 MB

reserved data index_size unused
------------------ ------------------ ------------------ ------------------
3089408 KB 2480768 KB 166896 KB 441744 KB



It's still upcoming. Hopefully in the next month or two. You think that
might make a difference with this situation?

OK, removed it. Is that something I should do periodically?

Thanks!

Neil