Posted by Neil on 12/24/07 11:29

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns9A106EF2E30A4Yazorman@127.0.0.1...
> Neil (nospam@nospam.net) writes:
>> 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?
>
> 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!
>
>

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

[Back to original message]