January 15, 2009 at 1:49 am
Hi guys,
I have been asked to help out with a database problem on a server that I don’t usually look after. The server is running SQL 2000.
The database in question contains two data files in the primary group, one is 192GB (180GB used/ 12GB unallocated) and the other is 40GB (30.5GB used/ 9.5GB unallocated). However, one of the tables in the database, “tblStoredQuotes”, is currently taking up 216GB of this space although 158GB of this is unused. This table stores about 2.3 million rows which are constantly being inserted and deleted after 30 days. The table also includes nine “text” columns although I don’t know if this is adding to the problem.
I have read that this is likely to be down to internal fragmentation of the tblStoredQuotes table. The table does contain a clustered index and two other indexes on other columns. I have re-indexed all of these indexes using DBCC DBREINDEX and DBCC INDEXDEFRAG and I have also re-created the clustered index with the “drop_existing” option but nothing seems to have any impact on the amount of unused space present in this table.
Is there something else I can try? As a last resort I can possibly “export” the table to another table in the same database which should hopefully avoid all the unused space so I can then replace the original with the copy. Obviously this will mean down-time though and I’d like to avoid this if at all possible.
Any suggestions are very welcome!
January 15, 2009 at 11:03 pm
Do you need all the text columns.
"Keep Trying"
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply