SQL Server Performance

Freeing up allocated space

Discussion in 'SQL Server 2005 General DBA Questions' started by rogilvie, Jul 13, 2007.

  1. rogilvie New Member


    I'm looking to find a command that will reduce the memory space taken up by tables once you have deleted records from them.

    i.e. suppose you run

    select top 10000 * into table_name

    delete from table_name

    you will have a non-zero space-usage. I am aware that the above example can be remedied using truncate but, where the requirement is only to delete some of the records from the table, how can I reduce the space used by the table.

    I've looked around the hints / tips stuff and tried running UPDATE STATISTICS, DBCC CLEANTABLE and sp_shrinkfile (which i've discovered i don't have permissions to use) but none of these seem to work.

    Any ideas?

  2. ndinakar Member

    You can use DBCC INDEXDEFRAG if you are on 2000 or ALTER INDEX in 2005. check out books online for more info.

    Dinakar Nethi
    SQL Server MVP
  3. MohammedU New Member

    I don't think DBCC INDEXDEFRAG will free up any space...where as DBCC DBREINDEX does...
    But when deleted all the rows from the table, I don't think table is going occupy any space...to update the system tables run DBCC UPDATEUSAGE to get the correct space used by the table...

    Microsoft SQL Server MVP

    All postings are provided “AS IS” with no warranties for accuracy.
  4. satya Moderator

    Run DBCC UPDATEUSAGE to claim or correct the mismatched information to get optimum value in this case. To reduce the database space either you have to use DBCC SHRINKDATABASE or DBCC SHRINFILE, but on the contrary why do you need to execute if the database has already been using that space and may need in future too.

    Unless until there is a major cause do not attempt to SHRINK and SQL will look into increase at a later stage as per the quries running, nothing but wasting the resources.

    BTW on the real terms what is the current size of database and volume of deleted rows here?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Share This Page