Database Tripled In Size!!

[Neil]

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

[Tom van Stiphout]

On Mon, 24 Dec 2007 01:37:57 GMT, "Neil" <no****@nospam. netwrote:

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.

>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

[Neil]

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

"Tom van Stiphout" <no************ *@cox.netwrote in message
news:su******** *************** *********@4ax.c om...

On Mon, 24 Dec 2007 01:37:57 GMT, "Neil" <no****@nospam. netwrote:

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]

"Neil" <no****@nospam. netwrote in message
news:V3******** *****@newssvr11 .news.prodigy.n et...

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

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.

[Erland Sommarskog]

Neil (no****@nospam. net) writes:

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.

In that case sp_spaceused for the database should report a lot of free
space.

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?

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, es****@sommarsk og.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

[Neil]

"Erland Sommarskog" <es****@sommars kog.sewrote in message
news:Xn******** **************@ 127.0.0.1...

Neil (no****@nospam. net) writes:

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

In that case sp_spaceused for the database should report a lot of free
space.

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

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

Do you run a regular maintenance job on the database that defragments
indexes?

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

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, es****@sommarsk og.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]

Neil (no****@nospam. net) writes:

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

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

That's OK.

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)

But remove this one. Shrinking the database is not a good thing to do
on regular terms.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.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

[Neil]

"Erland Sommarskog" <es****@sommars kog.sewrote in message
news:Xn******** *************@1 27.0.0.1...

Neil (no****@nospam. net) writes:

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

database_siz e unallocated space
--------------------------------------
3355.75 MB -2571.25 MB

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

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.

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

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?

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

>

>The maintenance job that is run nightly performs the following:

Optimization s tab:

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

That's OK.

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

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

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

Thanks!

Neil

>

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.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

[Neil]

OK, Erland, here's another strange one.

I took the last backup from before the database ballooned, and I downloaded
it to my development machine, so as to be able to look at the old vs. new
data, to see if I missed anything.

After restoring the backup, the database size was 1.5 GB, though it was
previously 1.0 GB. Going into Shrink Database, it showed that only 1.0 GB
was used. So I performed a Shrink Database on the old database.

The Shrink Database took a very long time. When it was done, I was told,
"The database has been shrunk to a size of 2 GB"!!! So it grew instead of
shrinking!

Looking at the files, the MDF is 1.26 GB, and the LDF is 0.75 GB, for a
total of 2 GB.

So the database that had been 1 GB grew to 1.5 after backup, and then 2.0
after shrinking.

This is the backup that was performed right after I made the table changes.
Remember that I said that I made the changes *after* the optimizations that
night, but before the backup. The backup file itself is 1.1 GB. It was the
following night's backup that grew to 3 GB.

So, apparently, making the changes, in and of themselves, didn't balloon the
database. But sometime after that -- probably the following night's
optimizations, which included a Shrink Database, caused it to balloon to 3
GB.

A significant difference between my test and the actual database, though, is
that when the old database grew to 2 GB, 0.75 GB of that was from the log
file. With the current 3 GB database, though, the log file is less than a
MB. The 3 GB is all in the data file.

Weird.

Neil

[Erland Sommarskog]

Neil (no****@nospam. net) writes:

I took the last backup from before the database ballooned, and I
downloaded it to my development machine, so as to be able to look at the
old vs. new data, to see if I missed anything.

Wait, didn't I tell you celebrate Christmas and relax?!?!?

After restoring the backup, the database size was 1.5 GB, though it was
previously 1.0 GB. Going into Shrink Database, it showed that only 1.0 GB
was used. So I performed a Shrink Database on the old database.

The Shrink Database took a very long time. When it was done, I was told,
"The database has been shrunk to a size of 2 GB"!!! So it grew instead of
shrinking!

Haven't I told you to stop shrinking databases! There are very few
situations where shrinking a database is a good idea. But there are many
where it is an outright bad idea.

So, apparently, making the changes, in and of themselves, didn't balloon
the database. But sometime after that -- probably the following night's
optimizations, which included a Shrink Database, caused it to balloon to
3 GB.

Probably it was not until the reindexing that SQL Server allocated full
space for the new columns.

Judging from the new numbers from sp_spaceused you posted, there is
plenty of free space in the database. Let it stay that way, it's
not going to do you any harm. Although the amount of unused space
is possibly a little worrying, since that indicates quite an amount
of fragmentation. But if you stop shrinking your database, the next
reindexing job should take care of that. (Shrinking introduces
fragmentation, another reason it's bad.)

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

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

The database is not going to shrink if you upgrade to SQL 2005 if
that is what you think. Let me put it this way: you have recently
become the proud owner of a 3GB database, congratulations !

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.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