After Large Delete from Table?

Question

After I perform a large delete on a table what steps should I take to ensure that space that was taken up by those rows is reallocated back to SQL Server? Run Statistics on that table, etc?

Maybe I don't need to do anything, was just wondering what if there were any follow up steps.

Thanks,

S

Answer 1

The space will automatically go back to SQL server. You don't have to do anything.

Now, the database size will be the same plus the growth of your log file due to logging the transactions. However, internally, sql server knows that it still has that space to play with.

Beyond that you'll probably want to update the statistics and rebuild any indexes as necessary. However, that should be a part of your normal nightly maintenance plan.

UPDATE:
I just wanted to add one more thing. Some people like to Shrink their databases in order to reclaim space after a big delete. Don't do this unless you have no other choice. Besides the time it takes and server overhead, the problem is that if the database has to grow again then SQL server has to reallocate the space. This action is very time consuming to SQL server. If you know the size the database actually needs, just allocate that up front and leave it alone.