1

Our SQL Server (version 2012) is housed on a cloud server, for which we pay whenever we need additional space. We determined that we could delete some of the data to prevent the need for continually buying more storage.

The delete queries did not appear to open space - the size of the drive and available space doesn't change.

It is my understanding that even though available space has not changed, available space is created by the delete query. So new records imported can populate places where data was deleted. Is this correct?

I understand the Shrink Database is not a good choice.

Should I run - sp_clean_db_file_free_space based on the following advice?

http://www.experts-exchange.com/questions/27905050/Freeing-up-disk-space-by-deleting-records-in-a-SQL-Server-2008-table.html

Improve this question
5
  • Is the table in question a heap? Does it have a clustered index? Rows deleted from Tables without clustered indexes are not automatically freed.
    – Hannah Vernon ♦
    Nov 12, 2015 at 17:07
  • Are you measuring available space on the drive, or available space within the database? Where exactly are you getting this figure?
    – Aaron Bertrand
    Nov 12, 2015 at 18:02
  • I am deleting rows form one of the tables within the database which is indexed.
    – CGrand
    Nov 12, 2015 at 18:36
  • To Aaron - I'm looking at the space available on the drive space we've rented in the cloud. I expected to see space available on the drive increase when I deleted records, but did no. Thank you
    – CGrand
    Nov 12, 2015 at 18:39
  • 1
    @CGrand yeah, SQL Server doesn't shrink the data file when you delete rows. It's just a dumb container (much like your drive, but with auto-grow capabilities).
    – Aaron Bertrand
    Nov 12, 2015 at 20:19

1 Answer 1

Reset to default
4
  1. Deleting rows from a table does not automatically free up space, unless you happen to delete a large chunk of rows that are stored on the same pages, and every row on each such page was marked for deletion. You would need to rebuild the indexes in order to reclaim the space.

  2. Even if you rebuild the indexes, SQL Server doesn't reduce the size of the data file(s) just because you've removed some data inside the file. The assumption is typically that if the database got that big once, it will get that big again, so why shrink the file only to grow it again later? Both shrink and growth operations are extremely disruptive to normal activity. Please read this thread in full to understand why you want to be very careful about freeing up space on your drive (even on a cloud-hosted server):

Also see:

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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