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?
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?).
