Deleting Records and Recovering Space in SQL Database

by Joe Havelick 27. February 2008 23:08

On the occasion that you must removed data from a table and recover the space, there is a two step process to do this in SQL.

First, you must delete the data, using TRUNCATE or DELETE.  The difference between the two has to do with how the data is removed.  The delete command allows for a WHERE clause, and the deletions will be processed into the transaction logs.  TRUNCATE, on the other hand, is a method for the bulk deletion of an entire table, and it not processed into the transaction logs.

Second, in order to reclaim the disk space previously consumed by data, you must ask the database files to resize themselves accordingly.  The following script exemplifies how to truncate a database table, then resize the database (leaving a 10% reserve for future growth).

USE DATABASENAME

/* --Remove All Records from TableName */
TRUNCATE TABLE TABLENAME

/* --Shrink the database files with 10% reserve */
DBCC SHRINKDATABASE(DATABASENAME, 10)

Tags:

Tech Tips

Comments are closed

About Me

Joe Havelick is a reasonable facsimile of this photo.


profile for Joe on Stack Exchange, a network of free, community-driven Q&A sites

Recommendations