HOW TO REMOVE UNUSED SPACE IN SQL SERVER 2000 DATABASE TABLE

BELOW IS THE RESULT I GOT WHEN I CHECKED SPACE USED BY A TABLE IN SQL SERVER 2000. IS THERE ANY WAY I COULD REDUCE THE SIZE OF THIS TABLE AND DELETE UNUSED SPACE? I HAVE ALREADY TRIED DBCC SHRINKDATABASE BUT IT DOESNT WORK.

NAME           NUMROWS    RESERVEDK  DATA K       INDEX_SIZE   UNUSED
TABLE      77698      1332544 KB      154192 KB      24040 KB      1154312 KB
sajm78Asked:
Who is Participating?
 
CragCommented:
Corrected:
There are three steps to resove this issue:
1. Make sure the table has a clustered index - even if only during this process.
2. Reorganise the clustered index to remove the space.
alter index <indexname> on <schema>.<tablename> rebuild;
3. Update the statistics on the table to ensure that the values are correct
update statistics <schema>.<tablename>;

This should do the trick.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
hi,

 to start with, please do NOT post with CAPS LOCK on. it is difficult to read, and considered shouting on internet...

 now, in regards to the problem:
 try to drop and recreate the clustered index on that table.
 if you don't have a clustered index on that table, change one of the indexes of that table into a clustered index
 if you don't have any index at all on that table, create one (clustered, obviously)
0
 
sajm78Author Commented:
Angellll,

I am sorry for writing in CAPS, it was done unintentionaly. The solution u have given doesnt help. Is there some procedure I can run to remove unused space from table?

Thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
CragCommented:
There are three steps to resove this issue:
1. Make sure the table has a clustered inded - even if only during this process.
2. Reorganise the clustered index to remove the space.
alter index <indexname> on <schema>.<tablename> rebuild;
3. Update the statistics on the database to ensure that the values are correct
update statistics <schema>.<tablename>;

This should do the trick.
0
 
danicolaiCommented:
This looks like a solution for a SQL 2005 database.  SQL 2000 does not support the ALTER INDEX syntax.  Will a DROP INDEX and CREATE INDEX do the same here?
0
 
JEFFWCommented:
We had a similar situation with sql 2000. We had one table with a clustered index that had a sparsely populated text field that had tons reserved and unused space. The only way that I was able to fix was to use dbcc shrinkfile with emptyfile option (another way is to open the offending table in design mode and temporarly change a varchar value. The example we had was needing to change a varchar 50 to varchar 51, and then change it back after it finshed changing the table structure.) Both ways were successful in releasing the space. After this, I was forced to do a shrink db with truncate option which left the ddatabase fragmented. I then used a dbcc dbreindex job to derebuild all indexes on the database. I then used the dbcc dbreindex on any important tables the still needed to be defraged.

I hope this makes sense to anyone who needs it.
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.