|
|
Posted by Erland Sommarskog on 12/29/07 18:44
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
0 - The table itself when it's a heap.
1 - The clustered index.
2-249 - Non-clustered indexes
255 - Text/image data.
The numbers for reserved for indexes 0 and 1 includes the non-clustered
indexes, but not text/image data. (Or so it seems. I don't work much with
LOB data myself.)
> In and of itself, I wouldn't be surprised that Descriptions_Docs is
> using that much space. But it's the overnight jump from a db size of 1
> GB to a db size of 3 GB that perplexes me. Looking at the data for
> Descriptions_Docs from right before the database ballooned in size,
> sp__spaceall shows that it was only half a GB in size:
>...
> So it went from half a GB to 2 GB practically overnight, with no
> significant increase in rows!
Someone added some very large documents?
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.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|