locked
Reclaiming table space after dropping a column RRS feed

  • Question

  • Hi Folks,

    I've done a fair bit of studying on this topic, but in my case I still have an issue.

    I have a 4GB sql 2000 database and one of the tables was taking up about 2.5 GB, Index=1.5GB, so I normalised out most of the nvarchar columns into lookup tables and remapped the data with the usual sql stmts. Then I dropped the original columns. The table size is now much smaller at data = 1.6 GB, indexsize = 0.9 GB

    Then I ran a script which does the following:

    - dbcc dbreindex (because the table has a clustered index)
    - dbcc cleantable (this does nothing on a table with a clustered index, but I ran it anyway)
    - update stats
    - dump the log
    - dbcc shrinkfile on each file
    - dbcc shrinkdatabase on the db

    I repeated all the above several times.

    sp_spaceused returns the following:

    rows=3,200,000
    reserved = 4,100,000 KB
    data = 1,600,000 KB
    index_size = 920,000 KB
    unused = 1,590,000 KB

    I want to get that 1.5 GB in unused released. How can I do this ?

    thanks
    Paul

    Monday, November 30, 2009 11:44 AM

Answers

  • OK the solution was to do

        dbcc updateusage(dbname)

    The space had been reclaimed already, but sp_spaceused wasn't reporting it because that dbcc command hadnt been run.

    • Marked as answer by BobTD Monday, November 30, 2009 1:20 PM
    Monday, November 30, 2009 1:20 PM

All replies

  • OK the solution was to do

        dbcc updateusage(dbname)

    The space had been reclaimed already, but sp_spaceused wasn't reporting it because that dbcc command hadnt been run.

    • Marked as answer by BobTD Monday, November 30, 2009 1:20 PM
    Monday, November 30, 2009 1:20 PM
  • you could have ran sp_spaceused with updateusage for eg.

    EXEC sp_spaceused @updateusage = N'TRUE';


    Madhu
    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    Tuesday, December 1, 2009 12:35 AM