Heap Table in SQL 2000 taking Excessive space

i have sql 2000 server database taking 75gb of space.  55gb of in 1 table heap 3 non-clustered indexes on it.  can't change because a vendor maintained database, , makes unsupported.  have shown vendor creating clustered index reduces table down 500mb roughly.  there 1 other table without clustered index taking majority of remaining space.  in each case, creating clustered index, reduces table size, hands pretty tied on this.

 

my question is, else can fix problem?  vendor isn't moving on issue, again, person pays cost of backups , san disk space database.  @ $100+ per gb in our san this waste of resources aggrivates me.

 

anyone have ideas?  have rebuilt of non-clustered indexes no avail.  tempted try drop create on of them tonight after hours find out if helps any.  can done?

hi jonathan,

 

it sounds heap has grown 55 gb @ 1 point, , lot of rows have been deleted, taking down 500 mb.  sql server delete unallocated pages heap when delete given tablock hint (at least 2005 - i'm not sure 2000).  you've got 50-odd gb of data pages empty.  when new row inserted, pages reused, might take while 55 gb.  see it, have 3 options:

 

#1, add tablock delete commands.  not likely, given vendor.

#2, recreate table, eg using same method em uses when add new column

#3, create clustered index , drop clustered index.  vendor never know!

 

the other question is, table grow 55 gb on regular basis?  if so, change make now, you'll have again regularly.

SQL Server  >  SQL Server Database Engine