Results 1 to 13 of 13

Thread: Unused Space

  1. #1
    Join Date
    Feb 2012
    Posts
    9

    Unanswered: Unused Space

    Hi,

    Can anyone please let me know how to claim unused free space at table level. The database size is of 4.6TB, recently I deleted some data which is of almost 1.5TB but my unused space has grown upto 2.5TB leaving me short of space on my drives. How do I claim this usused on to OS Disk space?

    Any quick help is hghly appreciated.

    Thanks,
    Sri.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2012
    Posts
    9
    Sorry, this is of no help. I already tried

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How big is your transaction log?
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2012
    Posts
    9
    It is only 4GB as our ETL package shrink log files after every load.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What does this return?
    Code:
    EXEC sp_spaceused @objname = 'your_table', @updateusage='TRUE'
    GO
    
    EXEC sp_spaceused @objname = 'your_table'
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2012
    Posts
    9
    name rows reserved data index_size unused
    XXX 1130865309 1991316120 KB 886805344 KB 63371784 KB 1041138992 KB

    This is one of the tables, load is going on the other 2 major tables

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    What do you mean it did not work? Did it just run forever and you killed it? or did it complete and not do anything?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Feb 2012
    Posts
    9
    It ran but did not release the unused space.

  10. #10
    Join Date
    Feb 2012
    Posts
    9

    Unused Space

    Hi Guys,

    Thank you for your replies. I am sorry this reply is bit late but it is always better be late than never.

    Unused space can be claimed by Rebuilding table, this way you get all unused space to Unallocated space which can be shrunk later.

    Thanks,
    srimami.

  11. #11
    Join Date
    Aug 2008
    Posts
    147
    One other option is to commit a DBCC CHECKDB and see if there is any corruption
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd be surprised if there was database corruption causing this problem. It sounds much more like page fragmentation to me. Rebuilding the table would certainly fix either problem (as long as the database wasn't so badly corrupt that it could no longer support the table).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #13
    Join Date
    Aug 2008
    Posts
    147
    That sounds most likely. I've seen databases where records are deleted , but the pages are not freed. For example, if you deleted one record over a number of different pages , therefore the deallocate didn't occur. If there are indexes involved you could think about a reindex.
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •