Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2007
    Posts
    22

    Unanswered: Problem Shrinking Data File

    (SQL 2005) I have a few databases that are currently hovering at around 150GB. (The following only concerns the .mdf) Of this total size, only about 90GB is real data. The other 60-70 is data from unecessary audit tables. Recently, we've been having some space issues on a 1.7TB server. We've decided that deleting these audit tables and reclaiming the 60-70GB per database is the route we're going to take.

    What we did: DELETE <table> (per audit table). Now, it shows that the database size is ~90GB and there is the 60-70GB of unallocated space.

    Then, we ran a DBCC SHRINKDATABASE (db_name) overnight. Checked space the next morning and only about 10GB had been reclaimed. We've tried a number of other alternatives of which, none have been successful.

    (DBCC SHRINKFILE(file, space), drop and recreate tables, etc.)

    Can anyone think of any reason why these commands would not be working? If I can provide any additional information, please let me know.

    Thank you in advance for any input!

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    use sp_spaceused with the updateusage option to get a better idea of how much space you have. this stuff is notoriously out of date at times.
    “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.

  3. #3
    Join Date
    Oct 2007
    Posts
    22
    When I started looking at this issue, I actually wrote a small script that would show this for me:

    DECLARE @cmdstr varchar(100)

    CREATE TABLE #TempTbl
    ( Table_Name varchar(50),
    Row_Count int,
    Table_Size varchar(50),
    Data_Space_Used varchar(50),
    Index_Space_Used varchar(50),
    Unused_Space varchar(50)
    )

    SELECT @cmdstr = 'sp_msforeachtable ''sp_spaceused "?"'''

    INSERT INTO #TempTbl EXEC(@cmdstr)

    SELECT * FROM #TempTbl ORDER BY LEN(Table_Size) DESC

    DROP TABLE #TempTbl


    Running this before and after shows that the table size has decreased to 0. However, according to any of our server space monitors, or even physically logging on to the server, none of the space has been reclaimed.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you missed the part about the @updateusage argument for sp_spaceused.
    “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.

  5. #5
    Join Date
    Oct 2007
    Posts
    22
    Ah, sorry!

    The results are as follows:

    Db_Nm
    <name>

    Db_size
    101,684.94 MB

    Unallocated_space
    71,429.32 MB


    reserved
    30,927,992 KB

    data
    22,885,320 KB

    index_size
    7,887,600 KB

    unused
    155,072 KB

    Is this what you would expect to see?

    Thank you very much for your assistance.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by Joe_F
    Ah, sorry!
    Is this what you would expect to see?
    this is a better question for you.
    “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.

Posting Permissions

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