[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4754
  • Last Modified:

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
0
sajm78
Asked:
sajm78
1 Solution
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now