|
|
Posted by Neil on 12/26/07 10:54
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns9A116ABC6CF5CYazorman@127.0.0.1...
> Neil (nospam@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?!?!?
What makes you think I'm not relaxing?.... :-)
>> 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.)
I went to run another sp_spaceused, to see what it looked like, since I
turned shrinking off a couple of days ago. But then I saw that shrinking ran
last night. Couldn't figure that out, since I had turned it off. Turns out I
had _two_ shrink jobs running each night! -- one with the regular
optimizations, and one as a standalone job! Oy! So I turned the second one
off, and we'll see what happens.
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?
>
>>> 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.
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).
> Let me put it this way: you have recently
> become the proud owner of a 3GB database, congratulations!
Yay me! :-) OK, that's fine, I can live with that, as long as it's not
problematic re. performance. But there's something I'm still not
understanding. Forgive me for being dense, but here goes.
The second sp_spaceused showed:
database_size unallocated space
----------------------- ------------------ ------------------
3320.06 MB 297.91 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
3094688 KB 2483672 KB 169712 KB 441304 KB
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.
Thanks!
Neil
Navigation:
[Reply to this message]
|