Index size shows larger than entire database

Question

Index size shows larger than entire database

mrogers-674473

Old Hand

Points: 391
More actions
June 10, 2008 at 12:59 pm

#72123

A number of months ago one of our monitoring tools started reporting that a database in a SQL 2000 instance had exceeded it's max size by 200%+. The database is set to autogrow. A quick look at the properties of the datase showed that the data file was slightly over 6gb, and the log file at 100MB or so. The database is in simple recovery mode. We run regular maintenance on the database, but I still manually rebuilt all indexes and shrunk the data and log files. We continued to get the error from the monitoring tool, but chose to ignore it since everything was working fine and there was plenty of space left on the server.
We later migrated the database to SQL 2005. (detach, move, attach). Everything continued to work, but we still got the notifications.
Further analysis of the database shows that MANY of the tables in the database have indexes that are larger than the data. The number of indexes and the number of columns in the index were not numerous, so this alone seemed odd. But two of the indexes were not only large, they were significantly larger than the reported size of the database. One table, with 1.6 million records takes up about 350 MB of data space, but 42 GB of index space. The database, as I said, only shows 6GB total. Obviously something, somewhere is out of sync. I have rebuilt the indexes, reorged the data, defragged the data, and kicked the machine, but it still shows the 42 GB.
Short of dropping all the indexes and recreating them (not a huge deal for this table, but there are many tables), is there anything else i can do to fix this?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

Under SQL Server 2000, space usage is not maintained in real time and you must run "dbcc updateusage" with appropriate parameter values to get the correct information.

SQL = Scarcely Qualifies as a Language

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Try DBCC UpdateUsage

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mrogers-674473 Old Hand Points: 391 More actions · Answer 3

I'd kiss the two of you if I could. It would be awkward, but I'd do it none-the-less.

I'm never surprised to find out how little I know.

Thanks for your help. That fixed it.