none
How to reduce size of SQL server table? RRS feed

  • 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

    Friday, January 20, 2012 9:52 AM

Answers

  • 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

    Friday, January 20, 2012 11:53 AM

All replies

  • 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 :)
    Friday, January 20, 2012 10:17 AM
  • 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?

     

    Friday, January 20, 2012 10:20 AM
  • 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
    Friday, January 20, 2012 10:36 AM
  • 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!

    Friday, January 20, 2012 10:37 AM
  • 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 ++++
    Friday, January 20, 2012 10:52 AM
  • 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

    Friday, January 20, 2012 11:53 AM
  • 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 :)
    Friday, January 20, 2012 12:40 PM
  • 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.

    Friday, January 20, 2012 12:46 PM
  • Works for me ! :-)

    Thanks

    Tuesday, October 22, 2013 5:23 PM
  • 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).

    • Edited by samot-dwarf Wednesday, September 16, 2015 8:21 AM
    Monday, September 14, 2015 9:27 AM