Search code examples
sql-serverdatabasedatabase-administration

Unused Space allocated to table in SQL server

I have a table in my Database which has unused space of about 55GB. How do I reduce this unused space?

Steps that I have tried:

  1. I have rebuilt the Indexes, this helped me a lot this reduced the unused space from 110GB to 55GB.
  2. By running DBCC SHOWCONTIG I found that my table's average page density is 99% (which is in a way good,but it did not help me to reduce the unused space)
  3. I have changed the fill factor and rebuild the index again (this time rebuilding the index after changing fill factor to 100 did not reflect any desired outcome)
  4. I tried DBCC CLEANTABLE (DBNAME,"Tablename"), I could not reduce the unused space
  5. I have not tried shrinking of DB, I want to reduce the unused space without shrinking the DB.

Current output:

CurrentOutput

Desired Output: I want to reduce the Total space so that the Unused space is reduced.

Solution

  • Solution:

    ALTER INDEX ALL ON MyTable REBUILD WITH (FILLFACTOR = 100);