SQL Azure database not freeing space after deletes

Question

_{Wednesday, April 20, 2011 6:58 PM}

I am seeing that my SQL Azure database is not freeing space when I delete records. We are regularly purging records from tables and do not seem to get the appropriate space back. It feels like I need to call DBCC SHRINKDATABASE after these purges but it is not supported. It’s been hard for me to pin down, but the best indicator has been that when I copy a database the new database’s size is significantly smaller than the original. Specifically a day after one of these purges I copied a 40gb database and the new database was 32gb. I can only assume the 8gb difference is deleted data, which I might add that I’m sure I am continuing to be billed for. 

Any help on this issue would be greatly appreciated as this problem is not only costing us money but will also soon force us off of SQL Azure due the 50gb database limit.

Eddie

All replies (9)

_{Monday, April 25, 2011 6:27 PM ✅Answered}

Hi,

I wanted to clarify one point; your initial question was around the use of DBCC SHRINKDATABASE. Note that the DBCC statement does more than reorganize data pages in SQL Server; it also shinks the Log Files.  So the DBCC statement accomplishes more than a reindex operation.

Nevertheless, the maximum space defined in SQL Azure excludes log files (among other things; info here: http://msdn.microsoft.com/en-us/library/ee621788.aspx). So if you have a 1GB Web Edition, this only applies to the Data, Index and Objects; not the log files. So the reindexing your database should do the trick as suggested by Ike, Brian and Brent.

Herve Roggero, Blue Syntax MVP SQL Azure Co-Author: Pro SQL Azure

_{Wednesday, April 20, 2011 9:05 PM}

I agree, it sounds like a shrink is in order. The behavior you're reporting makes sense but I haven't seen this issue raised before. I'll see if I can'tget a few folks that should be "in the know" to check out this thread and hopefully get you a good response.

_{Wednesday, April 20, 2011 10:39 PM | 1 vote}

If you've done deletes then there's probably some empty space in your tables & indexes. If you rebuild your tables and/or indexes you should get that space back. I don't think this is a SQL Azure thing - I've seen the same behavior in on-premise SQL Server installations. They probably are charging you for that space because SQL Server is hanging on to that empty space so that it can fill it in with new data.

_{Thursday, April 21, 2011 12:25 PM}

I've ping'd some folks and there's some good discussion going on but nobody as yet seems sure of a "best practice" around this. One thought I had was to verify the process you're using to measure your database size and reconcile that against the billing charges you're seeing on the MOCP portal (if you can, its admittedly not as transparent as many of us would like).

Also, it seems like you're dealing with a significant amount of temporary data. Is there an opportunity to evaluate moving that data to Azure Storage? This would give you a modest decrease in hosting charges and posibly give you a more consistent projection on your usage. However, those benefits could be easily outweighed by the costs of refactoring any dependent processes.

_{Thursday, April 21, 2011 4:28 PM}

Hi Eddie,

I wanted to see if I could recreate your experience.  I'm checking space using the Windows Azure Management Portal.  I loaded 5GB of data into a table.  I deleted about 500MB of it, and didn't notice the space drop.  I rebuilt the indexes, and then it started to drop.  I waited an hour and saw most of the space used drop.  I cleared out another 2GB of data and then I just waited.  I saw the space gradually drop in the portal.  I cleared the table out entirely, and all the space dropped immediately.

I'm not certain what we can learn from that.  I'll probably have to test it further, but it looks to me like they might be shrinking and reclaiming space on the backend, it's just a low-priority.  Moving the table to a different table and moving it back again might help that process if you're concerned about your bill.

Ike

Ike Ellis EllisTeam.net Blog @ellisteam1

_{Thursday, April 21, 2011 7:03 PM}

The laziness of space reclamation hinted at by Ike Ellis is suggested also by the following documentation (my emphasis):

MAXSIZE provides the ability to limit the size of the database. If the size of the database reaches its MAXSIZE you will receive an error code 40544. When this occurs, you cannot insert or update data, or create new objects (such as tables, stored procedures, views, and functions). However, you can still read and delete data, truncate tables, drop tables and indexes, and rebuild indexes. You can then update MAXSIZE to a value larger than your current database size or delete some data to free storage space. There may be as much as a fifteen-minute delay before you can insert new data.

_{Thursday, April 21, 2011 9:15 PM}

I used the following query to create backups:

CREATE DATABASE Prod_Copy

    AS COPY OF Prod

I backup the production db the day after the purge and use the following query to look at the size of each table, from both my production db and the copied backup:

select   

      sys.objects.name, sum(reserved_page_count) * 8.0 / 1024

from   

      sys.dm_db_partition_stats, sys.objects

where   

      sys.dm_db_partition_stats.object_id = sys.objects.object_id

group by sys.objects.name

Here is the output of this query showing the sizes in mb of all of the tables in what should be 2 identical DBs:

 As you will notice, there are a number of tables where the Production table is considerably larger than the table in the backup.  I believe in each case where the backup table is smaller than the production we have deleted data that is still occupying database space. In some cases these deletes occurred months in the past.

_{Thursday, April 21, 2011 9:21 PM}

I have also noticed that this is not as clear cut as deleted data is not instantly getting freed. I seem to get some space back when I delete, perhaps more a bit later, but some is just not getting freed ever (see my post from earlier today). I have also notice that if I start with a large table (25gb) and make multiple queries to gradually remove all the data, the bulk of the space seems to get freed once I get down to about 2gb. Pretty strange.

_{Thursday, April 21, 2011 9:31 PM | 1 vote}

Yes, this is most likely due to index fragmentation. The production version has some fragmentation, due to the deletes. Since the backup has been all inserts and no deletes, it is not fragmented. Defragment the production tables by rebuilding those indexes & the clustered index, and you'll get that space back.

I had a case where I used this same sproc to check a table's size. It was the exact same before & after deleting data. Then I reorganized the indexes & immediately got back about 15GB.