March 24, 2006 at 5:45 am
I have a table with 193748 rows. When I extract all the data from that table to a text file it is only 55MB and if I transfer it to another database it comes up to 64MB but sql server shows the table size as 2GB. Queries running against that table are slow but if I transfer all the data to another database the query speed is fine. The two indexes on that table sum up to 600MB which is not part of the 2GB table size. I tried all the shrinking already. Any suggestions as to what is going on?
March 24, 2006 at 10:35 am
The table definitions are the same? (char vs varchar)
March 24, 2006 at 11:31 am
Table Definition is exatly the same.
March 24, 2006 at 12:18 pm
Run sp_spaceused on the database. See the BOL for the syntax. That will show what space is being used for what. If it shows you have 2GB being used, check to see the default size of the database. Easiest way: use Enterprise Manager, right click on the database, select Properties. Go to Data and look at the Space Allocated amount. That's the default size.
-SQLBill
March 24, 2006 at 1:06 pm
Does the table have a clustered index ?
If it doesn't, then the 'shrinking' you might already have tried (like DBCC DbReindex() ) will not have done anything.
March 25, 2006 at 12:11 am
text or image columns? = 8KB minimum per non-null entry, with the only sure way to reclaim the space of deleted entries being to copy the table to a new one...
March 28, 2006 at 8:47 am
The table has a clustered index and it never had any text or image columns but the size just starts to grow even when no new records are entered.
March 29, 2006 at 6:49 am
You may want to verify that there are no differences in the Fill Factor and Pad Index properties of the indices. Fill Factor can be specified at the database level and/or individually when creating the indexes. Pad Index is specified when creating/reindexing.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply