1

I have a database which is 20 GB and it has history information since year 2001. Now I started deleting data prior to year 2008. after completion of deletion how can i reuse the space that is cleared by my delete statement. I am using SQL Server 2005.

Will Sql server automatically reuse the space if I don't shrink the datafiles? Should we rebuild the indexes?

2
  • Space previously used by deleted items will automatically be reused as needed. Are you trying to reclaim the space by shrinking the file the database is stored in?
    – James
    Dec 15, 2014 at 13:46
  • wanted to know if the space used by deleted items be reused automatically or any other process is there that should be followed after deleting the rows. Can you give me details of how this space will be reused.
    – Uma
    Dec 16, 2014 at 6:46

1 Answer 1

1

SQL Server will reuse available space in the datafile as necessary. If there is no space left it will attempt to auto-grow the datafile(s) for that File Group based on the auto-growth setting. If auto-grow is disabled and there is no room left, then you will get an error trying to do something that would require more space.

With regards to deleting large amounts of records and potentially not seeing that space get reclaimed as "available", you probably need to rebuild the Clustered Index on that table. I have run into situations where I had a rather large (many hundreds of millions of rows) table that would get a new 30 million or so new rows per day and delete the oldest 30 million or so records. We set up a weekly job to do a full rebuild on the clustered index and that is the only time the space was reclaimed. I am not sure of the exact reason that the rebuild was required (maybe the background process that removes the deleted rows couldn't do that task due to high activity on the table?).

Not the answer you're looking for? Browse other questions tagged or ask your own question.