How to reduce size of SQL server table?

Question

Hi experts,

  I have a table with 99.9% of unused size. How do I reduce the size?

I have tried those commands below but they do not work.

ALTER INDEX [VBDATA~0] ON qa2.VBDATA REBUILD

 

DBCC CLEANTABLE (QA2,"qa2.VBDATA", 0)

WITH NO_INFOMSGS;

GO

 

ALTER INDEX ALL ON qa2.VBDATA REBUILD

Answer 1

If you want to realize the space to OS means, then u need to shrink the database "QA2"

Thanks,

---

Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

Answer 2

Hi Gajula,

  But I don't want to shrink the whole database(4.5TB of 9.0TB). It will take a long time and will probably cause performance impact. Could I shrink sinlge table?

 

Answer 3

Hi,

there is a solution but it risky, if you can ensure while the process of the solution there is no insertion in your database it will work fine.

first drop the indexes.

second create the droped indexes.

and if it does not remove the free space you may run DBCC commands (try the previous command you run).

 

I hope this is helpfull.

 

Elmozamil Elamir

MyBlog

---

Please Mark it as Answered if it answered your question
OR mark it as Helpful if it help you to solve your problem
Elmozamil Elamir Hamid
http://elmozamil.blogspot.com

Answer 4

Hi,

Does this table have a clustered index? If so reorg or rebuilding the clustered index should allow the size of the table to be lowered however it will not hand that space back to the OS. You would need to shrink the database or data file (but I am not recommending that!).

If the table is a heap, then creating a clustered index on it and then dropping it would also help but this could take sometime on a large table and will also affect any non-clustered indexes.

You should also check your fill factor for this table.

---

Sean Massey | Consultant, iUNITE

Feel free to contact me through My Blog or Twitter.
Please click the Mark as Answer button if a post solves your problem!

Answer 5

Hi,

In order to release space back to OS the only option is SHRINK FILE or DB there is no other way.

 

 

---

Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

Answer 6

I can see in the screen shot that we have got LOB data there. There is a known issue with LOB data in sql server 2005 that the space doesn't get released.

Is it SQL 2005, if yes then:

If feasible,try exporting the table to the new table

and

then truncate the old table

and

bring the data back from the backup table.

 

Also other option which you could try is :

      ALTER INDEX Index_name on Table_Name REORGANIZE WITH (LOB_COMPACTION=ON)

 

---

Kind regards| Harsh Chawla | Personal Blog:- SQL-blogs
|Team Blog:- Team Blog

Answer 7

HI Dan,

 

The following are my thoughts, which will help you to reduce the space on table, If you really wants to release space back to OS means, the ONE and ONLY option is SHRINK DATABASE

 

·         To Minimize database space can be done by remove leaf level fragmentation on table that which have a clustered index

·         Fill factor: 80% fill factor across your database will add 20% min to your database size, if the size is less than 80%, it occupies more space as per my knowledge

·         Rebuild your indexes within the database to re-optimize the data. run dbcc updateusage to make sure sp_spaceused to be accurate

Thanks,

---

Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

Answer 8

Hi Harsh,

  Thanks. "alter index ... reorganize" did work.

Our SQL server is 2008 R2 but it is upgraded/migrated many times from SQL server 2005.

Answer 9

Works for me ! :-)

Thanks

Answer 10

The ALTER INDEX did not work for me , I had to recreate the whole table (renaming the old one, creating a new one, inserting, dropping old) (and of course shrinking the DB after this).

Dropping / recreating the clustered PK did also not help.

Edit:

Upgrading from SQL 2005 SP2 to SQL 2005 SP4 CU3 (see http://sqlserverupdates.com/ for links to the most recent SQL Versions) helped (the bug seems to be fixed meanwhile). The upgrade process also automatically released the unused space from all affected tables (without the need to manually recreate or reindex them).