6GB 2 row table????

[hobbzilla]

After running:

SELECT [total size KB], B.rows, O.name
FROM (select sum(convert(decimal(10,0),dpages)*8129/1024)
[total size KB], id
FROM sysindexes group by id)
A INNER JOIN sysindexes B ON A.id = B.id and B.indid in (0,1)
INNER JOIN sysobjects O ON B.id = O.id WHERE B.rows <> 0
ORDER BY [total size KB] desc

I receive a list of tables in the database and their size. However, at
the top of the list is a table that reports a size of 6295974.007811
KB... this table contains exactly 2 rows of information.. (whereas the
next largest table ~3GB contains 1.5million). This 2 row table total
size can't be right... can it?

I have created a backup of the entire table in order to try and delete
the data in that table.. and/or delete the entire table to see if my
database decreases by a substaintial amount.. I really am fairly new to
SQL and think even if I delete the entire table and recreate it.. I am
going to lose something (dependencies, SP, etc.. etc.)
Any suggestions are VERY appreciated.

[Simon Hayes]

"hobbzilla" <ho*******@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

After running:

SELECT [total size KB], B.rows, O.name
FROM (select sum(convert(decimal(10,0),dpages)*8129/1024)
[total size KB], id
FROM sysindexes group by id)
A INNER JOIN sysindexes B ON A.id = B.id and B.indid in (0,1)
INNER JOIN sysobjects O ON B.id = O.id WHERE B.rows <> 0
ORDER BY [total size KB] desc

I receive a list of tables in the database and their size. However, at
the top of the list is a table that reports a size of 6295974.007811
KB... this table contains exactly 2 rows of information.. (whereas the
next largest table ~3GB contains 1.5million). This 2 row table total
size can't be right... can it?

I have created a backup of the entire table in order to try and delete
the data in that table.. and/or delete the entire table to see if my
database decreases by a substaintial amount.. I really am fairly new to
SQL and think even if I delete the entire table and recreate it.. I am
going to lose something (dependencies, SP, etc.. etc.)
Any suggestions are VERY appreciated.

The information in sysindexes may be wrong - check out DBCC UPDATEUSAGE in
Books Online. If that doesn't change anything, then you can use DBCC CHECKDB
to see if there's any database corruption which might explain it.

Simon

[Erland Sommarskog]

hobbzilla (ho*******@hotmail.com) writes:

SELECT [total size KB], B.rows, O.name
FROM (select sum(convert(decimal(10,0),dpages)*8129/1024)
[total size KB], id
FROM sysindexes group by id)
A INNER JOIN sysindexes B ON A.id = B.id and B.indid in (0,1)
INNER JOIN sysobjects O ON B.id = O.id WHERE B.rows <> 0
ORDER BY [total size KB] desc

I receive a list of tables in the database and their size. However, at
the top of the list is a table that reports a size of 6295974.007811
KB... this table contains exactly 2 rows of information.. (whereas the
next largest table ~3GB contains 1.5million). This 2 row table total
size can't be right... can it?

Unfortunately it can. If the table does not have a clustered index and
there are frequent inserts and deletes, then this result in huge
fragmentation.

But it could well be as Simon says, that DBCC UPDATEUSAGE will correct
the numbers.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Gang He [MSFT]]

For heap, delete can leave empty pages behind. So it is possible to have a
table with 6GB but only 2 rows.

Although the row/page count in sysindexes are not 100% accurately
maintained, it shouldn't go off by that much.

If DBCC UPDATEUSAGE doesn't help:

If this is a heap, you can reclaim the space back by create a clustered
index on the heap and drop it to get back the heap.
If this is an clustered index, rebuild the index will do.

--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
"hobbzilla" <ho*******@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

After running:

SELECT [total size KB], B.rows, O.name
FROM (select sum(convert(decimal(10,0),dpages)*8129/1024)
[total size KB], id
FROM sysindexes group by id)
A INNER JOIN sysindexes B ON A.id = B.id and B.indid in (0,1)
INNER JOIN sysobjects O ON B.id = O.id WHERE B.rows <> 0
ORDER BY [total size KB] desc

I receive a list of tables in the database and their size. However, at
the top of the list is a table that reports a size of 6295974.007811
KB... this table contains exactly 2 rows of information.. (whereas the
next largest table ~3GB contains 1.5million). This 2 row table total
size can't be right... can it?

I have created a backup of the entire table in order to try and delete
the data in that table.. and/or delete the entire table to see if my
database decreases by a substaintial amount.. I really am fairly new to
SQL and think even if I delete the entire table and recreate it.. I am
going to lose something (dependencies, SP, etc.. etc.)
Any suggestions are VERY appreciated.