|
|
Posted by Neil on 12/30/07 21:51
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns9A1664706DC2Yazorman@127.0.0.1...
> Neil (nospam@nospam.net) writes:
>> OK, I looked at the data a bit that has been changed. The database backup
>> the night of 12/20 was 1.2 GB. The database backup the night of 12/21 was
>> 3.3 GB. So whatever happened had to have happened on 12/21.
>>
>> Nevertheless, looking at ALL changes from 12/21 through the current
>> database, roughly a week's worth of changes -- all records that have
>> been added or modified in that table since 12/21, the entire amount data
>> in all of those records is about 1.5 MB -- about 1,000 times short of
>> what it needs to be.
>>
>> So there's no way that data added or modified caused this change. For
>> some
>> reason this table went from 1/2 GB to 2 GB overnight, with hardly any
>> changes to the data. This is just very strange.
>
> And
>
> SELECT SUM(datalength(textcol1)) + SUM(datalength(textcol2)) + ...
> FROM Description_Docs
>
> returns what?
>
> I don't know if you can get fragmentation in text columns, but I guess
> you can. But it would hardly happen out of the blue. Unless, hum, you
> passed some magic threshold? Nah...
>
> Anyway, you could try running DBCC CHECKTABLE on the table, or DBCC
> CHECKDB
> on the database to see if there is any corruption.
>
> You could also test the effects of DBCC CLEANTABLE.
>
> Yet an idea is to copy the table to another database, and see how
> much size it takes up there.
OK, getting the sum of all text column lengths resulted in: 374,457,113 --
which approximates the half GB size before it ballooned.
I created a new database and transferred the 2 GB Descriptions_Docs table to
that db. In the new db, according to Roy's sp__spaceall, the size of the
table is 420 MB. That seems about what it should be, given the above count,
as well as its size on 12/21, before the ballooning.
I ran DBCC CHECKTABLE, and it returned:
DBCC results for 'Descriptions_Docs'.
There are 66570 rows in 1171 pages for object 'Descriptions_Docs'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
So that seems fine.
I ran DBCC CHECKDB on the database. Everything seemed fine. It returned the
same information for descriptions_docs as did checktable, and it found no
errors.
I also tried to run DBCC CLEANTABLE on the table, but I got:
"Incorrect DBCC statement. Check the documentation for the correct DBCC
syntax and options."
I looked in BOL for CLEANTABLE, but it wasn't there. So I'm thinking it's
not in SQL 7?
In any case, given the above, there clearly seems to be a problem with the
table, where it's showing 2 GB worth of data, but there's only in actuality
half a GB. Very strange.
Thanks!
Neil
Navigation:
[Reply to this message]
|