On Sat, 29 Dec 2007 17:04:33 GMT, "Neil" <nospam (AT) nospam (DOT) net> wrote:

Sorry about that, I pulled it from by SQL 7.0 collection of tricks,
which apparently got mixed up with something else. I don't have 7.0
(or 6.5) running to fix it. 8-(

Roy Harvey
Beacon Falls, CT

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.

Neil

Neil (nospam (AT) nospam (DOT) net) writes:
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.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

"Roy Harvey (SQL Server MVP)" <roy_harvey (AT) snet (DOT) net> wrote

That's cool. I think the other one was the one I needed anyway. Per my other
posts here, it seems to have pinpointed the problem. So, thanks! As to why
it happened, that's another mystery. But at least it's clear now where the
problem is. So thanks again.

Neil

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

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

Neil (nospam (AT) nospam (DOT) net) writes:
OK, so there is obviously a lot of air in that table. No idea where it
came from.

That does apparently not include the text column.

It seems so. I will have to admit that I only looked in Books Online
for SQL 2000 and SQL 2005.

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Yeah, it seems that we'll never get to the bottom of that.

I think that's a good idea. What about the idea of just exporting it to a
blank db, like I did today, with the triggers, indexes, and constraints in
place, and then reimporting it with those? Then only the referencing
constraints would have to be recreated.

I'll look into that. Thanks!

Neil

Neil (nospam (AT) nospam (DOT) net) writes:
Whichever way you fancy, as long as you don't wreck anything.

But if you do it that way, you don't even have to drop the referencing
constraints, you only need to disable them:

ALTER TABLE tbl NOCHECK CONSTRAINT fk_this_that

to enable them, this is the command:

ALTER TABLE tbl WITH CHECK CHECK CONSTRAINT fk_this_that

A somewhat bizarre syntax, but without the WITH CHECK part, SQL Server
will just enable the constraints without checking them.

And whichever path you go, use SQL Compare as I suggested to verify that
you have all in place!

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

Well, all's well that ends well. I transferred the data to a new table,
etc., etc., and did a shrink on the database. Database went down to 1.2 GB.
Been running for a couple of days, and everything seems fine.

So, thanks so much for your help! Strange that this happened in the first
place; but at least it was able to be dealt with. And thanks for the
exchange. Was beneficial on many levels.

Thanks again, and have a great new year!

Neil