Monday, October 8, 2012

Changing SQL Azure database size limit / quota

The starting size limit quota for my SQL Azure database was 1 gigabyte (1 GB).  This particular database has a table with millions of records.  I am semi-manually importing records into that table, so I had been noticing that it was creeping up on that limit with each import batch.

Finally, I ran a batch of INSERT statements and it pushed my database size over the 1 GB quota.  I fully expected that it would give me some quota message and not allow any more inserts.  What I didn't expect is that hitting the quota brought down my whole Windows Azure web site!  Even my Azure-hosted web pages that were not hitting the database started failing to load (I couldn't see the exact error message because remote error messages were turned off, and I never was able to successfully turn them on).

I logged into the billing area of Windows Azure so that I could upgrade my account and increase my database size limit.  I looked all over the billing control panel, but could find no way to upgrade my account to a larger database size.  So I had to email support.

Support was very good.  They emailed me promptly to say that they would call me the next day during business hours.  They called me the next day at 1pm my time.  The support person couldn't tell me why filling up my database caused my web site to crash, but he was able to give me a solution.  He said that I needed to increase the maximum size of my database (no surprise there).  He said that currently there was no way to do that in the Windows Azure control panel (that did surprise me) so I needed to run a script.  He sent me the script to run, here it is (This should be run on the Master database):

ALTER DATABASE My_Database_Name MODIFY (EDITION='WEB', MAXSIZE=5GB)

I asked if I could set it to something between 1 GB and 5GB so that I didn't have to pay for a lot of space that I wasn't using.  But he said that you only have to pay for as much space as you're using, so it didn't hurt to go all the way up to 5 GB.

As a side note, by the time I ran this script, I noticed that my database size was back below 1GB and my site was working again!!  It appears that every day or two some kind of compression routine runs to decrease the amount of space needed by the table.  I'd seen this happen once before where after completing a batch of inserts, the database size was over 900 MB, but when I came back to add some more several days later it was closer to 800 MB.  I asked the support person about that, but he said he was more of a billing support person and I would need to ask tech support about it.  Maybe next time....