By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,841 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

6GB 2 row table????

P: n/a
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.

Jul 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a

"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
Jul 23 '05 #2

P: n/a
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
Jul 23 '05 #3

P: n/a
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.

Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.