SQL Server Performance Forum – Threads Archive

Freeing up allocated space

Hi, 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? Thanks

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
***********************
http://weblogs.sqlteam.com/dinakar/

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…
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

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
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ 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.

]]>