Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2006
    Posts
    115

    Unanswered: release table space after column is dropped

    I hv dropped the useless columns for a table but the table size is remain unchanged by executing sp_spaceused. pls advise how to release table spaces.

    thx

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Don't worry about the space... The space is available to SQL Server, and will be reclaimed/used in due time.

    SQL Server is a full blown database server. It does so much for you, much of which is automated/scheduled/triggered by things that are not readily apparent. Reclaiming unused space is something that happens when SQL Server isn't busy serving users.

    -PatP

  3. #3
    Join Date
    Jul 2006
    Posts
    115
    May i know when the due time is? is it configured in SQL server?

    thx

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There's no formal definition for when the space will be reclaimed, it will be somewhere between the time the space is released and the time that the space is needed. There are so many factors that contribute to the timing that it is practically impossible to predict unless you artificially use all of the free space and force the issue.

    -PatP

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Isn't this the "10 percent rule"?

    When the servers CPU usage is below 10 percent for 10 minutes...
    George
    Home | Blog

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The space may not be released until you either drop and recreate a clustered index, or create and drop a clustered index if one didn't exist before.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This is a slightly more formalised review of what you have observed:
    http://www.nigelrivett.net/SQLAdmin/...eProblems.html

    What you are observing is in effect an optimisation (or at least the absence of a potential inefficiency ). You may not necessarily want the table rebuilt after dropping a column. As such, reclaiming the space is a seperate process as described by Robert.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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