CODEWITHSUNDEEP
sql-server-2000
gdawgrancid
gdawgrancid

Reputation: 660

SQL Server 2000, table allocates too much space

So a pretty big problem just came up with a few databases we're using. For some reason, people's databases have grown to a ridiculous file size without any change in the table's data. While I have no idea what is causing this all of a sudden, I'm more concerned right now with clearing the disk space its using. I've run sp_spaceused and have tracked the main culprit down to 1 of 2 tables (depending on the database). For each database, one of these tables is allocating over half a GB to reserved spacewhile the data is only something like 50 MB. It shows that the index_size is at ~113 MB. The table has no clustered index and has about 15 columns, all of relatively small length except for 2 columns that are of type nvarchar with length 255 (these are usually null or empty in the table).

I've tried running DBCC shrinkdatabase and truncate table but it didn't do anything. I've researched this a bit and some others have had this problem, but if shrinkdatabase didn't fix it, then no solution was found for them either.

Let me know if there's anything else you guys need to know about the table or database set up. I don't know what else to try and this is a significant issue for us as people's databases are all of a sudden taking up 10 times the space they were before.

EDIT: After trying to run DBCC DBREINDEX and trying to change to a clustered index through Enterprise Manager, I get an error message saying:

Could not allocate new page for database 'DB'. There are no more pages available in filegroup PRIMARY. Space can be created by dropping objects, adding additional files, or allowing file growth.

I've tried deleting rows from this table too and it has no effect on the table's size. The log file increases, as expected, but that's the only change to the table's size.

Upvotes: 1

Views: 408

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

These are heaps (no clustered indexes)? Why?

If the users have done a lot of updates or deletes, I would try rebuilding the tables. Shrinkdatabase is not the way to do this. It will not fix fragmentation, unrecovered space from deletes or altering the width of a column, or rows wasted as forwarding pointers. I bet the tables would fare much better with a rebuild and/or a clustered index. In SQL Server 2000 you would do this by either:

(a) adding a clustered index. (I can't give you exact syntax for adding a clustered index (or changing one of your existing indexes, or the non-clustered primary key to be clustered), because I don't have enough information about your table.)

(b) DBCC DBREINDEX('dbo.tablename');

This will rebuild the non-clustered indexes on your heaps, but that may do you no good depending on where the wasted space is happening. I still suggest you should create a clustered index. If you can share some details about your table (e.g. the indexes that exist, what kind of queries are typically run, how you currently uniquely identify a row and the nature of data changes/additions to this table) we might be able to give more detailed advice.

Upvotes: 2

Related Questions