|
|
Posted by Erland Sommarskog on 12/30/07 23:10
Neil (nospam@nospam.net) writes:
> 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.
OK, so there is obviously a lot of air in that table. No idea where it
came from.
> 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.
That does apparently not include the text column.
> I looked in BOL for CLEANTABLE, but it wasn't there. So I'm thinking it's
> not in SQL 7?
It seems so. I will have to admit that I only looked in Books Online
for SQL 2000 and SQL 2005.
> 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.
I don't know how entwined this table is with other tables, but there
certainly is a case for
o Rename it.
o Create it again.
o Move the data to the new table.
o Recreate triggers, indexes and constraints.
o Move referencing constraints.
o Drop the old table.
o Shrink the database.
I would suggest that you first do this in a test environment to see that
it works well. And, oh, I would also suggest that you download SQL Compare
from Red Gate (www.red-gate.com) to compare the schema of the database
after the change with a reference copy. SQL Compare has a price tag, but
there is a preview period you could benefit from. Caveat: I don't know if
they support SQL 7.
--
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]
|