Posted by Neil on 12/29/07 20:57

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns9A15CA3BA3963Yazorman@127.0.0.1...
> Neil (nospam@nospam.net) writes:
>> OK, running sp__spaceall has yielded some interesting information. It's
>> showing that a single table, Descriptions_Docs, has a data size of 2 GB.
>> So that would certainly explain why the db was showing 2.5 GB of data.
>> However, when I run the
>>
>> select object_name(id), reserved, used, dpages
>> from sysindexes
>> where indid in (0,1)
>> order by reserved desc
>>
>> query, Descriptions_Docs doesn't even show up as one of the largest
>> tables.
>
> But change to
>
> where indid in (0,1, 255)
>
> and I bet it does. Here is the scoop on indid

Yes. With the current db:

Table Reserved
Used dpages
Descriptions_Docs 300113
253585 0

With the old (1 GB) db:

Table Reserved
Used dpages
Descriptions_Docs 55265
55181 0

So an increase of about 1.6 GB, which is what the other showed.

>> So it went from half a GB to 2 GB practically overnight, with no
>> significant increase in rows!
>
> Someone added some very large documents?

Seems unlikely that adding a few large documents would cause a half GB table
with 66,000 rows to quadruple in size. I just can't fathom that. As noted,
most the docs are about 1-2 KB. The largest are about 4 KB.

Also, there are 81 new rows between the two databases, and 90 additional
rows that were modified between the time of the old and new databases. So
that's not a lot of data change.

>
> SELECT TOP 50 keycol, datalength(textcol1), datalength(textcol2), ...
> FROM Description_Docs
> ORDER BY 2 DESC
>
> And then change to 3, 4 etc in the ORDER BY clause may reveal something.

At first glance, I don't see anything. But I'm going to look at it a little
more closely, as well as examine those 171 rows noted above, and get back to
you.

Thanks!

Neil

[Back to original message]