6GB 2 row table????

[hobbzilla]

After running:

SELECT [total size KB], B.rows, O.name
FROM (select sum(convert(dec imal(10,0),dpag es)*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]

Re: 6GB 2 row table????


"hobbzilla" <hobbzilla@hotm ail.com> wrote in message
news:1106669221 .041183.314550@ z14g2000cwz.goo glegroups.com.. .[color=blue]
> After running:
>
> SELECT [total size KB], B.rows, O.name
> FROM (select sum(convert(dec imal(10,0),dpag es)*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.
>[/color]

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]

Re: 6GB 2 row table????

hobbzilla (hobbzilla@hotm ail.com) writes:[color=blue]
> SELECT [total size KB], B.rows, O.name
> FROM (select sum(convert(dec imal(10,0),dpag es)*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?[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Gang He [MSFT]]

Re: 6GB 2 row table????

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" <hobbzilla@hotm ail.com> wrote in message
news:1106669221 .041183.314550@ z14g2000cwz.goo glegroups.com.. .[color=blue]
> After running:
>
> SELECT [total size KB], B.rows, O.name
> FROM (select sum(convert(dec imal(10,0),dpag es)*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.
>[/color]