How to Tell If You Are Out of Room

[This article was contributed by the SQL Azure team.]

SQL Azure databases are capped in size; one reason for this is that we don’t want to send you a surprise bill if your data grows beyond your expectations. You can always increase or decrease the cap of your database (up to 50 Gigabytes); however this is not done automatically. This article will discuss the various tricks and tips to handling the size of your SQL Azure databases, including: making them bigger, querying your current cap, figuring out the current size, and detecting size errors.

One thing to note before we get started you are not charged based on the database cap, you are charged for the data that the database is actually holding based on ranges in each edition of the database. For the web edition those ranges are 0-1 Gigabytes, and 1-5 Gigabytes. For business edition those ranges are 0-10 Gigabytes, 10-20 Gigabytes, 20-30 Gigabytes, 30-40 Gigabytes, and 40-50 Gigabytes.

Current Cap

You can query the current cap of your SQL Azure database using this Transact-SQL query:

 SELECT DATABASEPROPERTYEX ('AdventureWorksLTAZ2008R2' , 'MaxSizeInBytes' )

Obviously, since the property is named MaxSizeInBytes the result are in bytes, rightly so SQL Azure believes that a Gigabyte is 1,073,741,824 bytes not 1,000,000,000 bytes. One thing to note, the database argument should be the same as the current database; otherwise the result will be NULL.

Current Size

You can query the current size of your database using the query below:

 SELECT SUM(reserved_page_count) * 8192
FROM    sys.dm_db_partition_stats

The results of this query are returned in bytes also, you can use it to compare to your current cap gotten from the query above. Pop quiz: When will the current size be bigger than the current limit? Answer: Never. If you try to update or insert data that exceeds the limit you will get SQL error.

Detecting the Size Error

If you are out of room in your SQL Azure database you will get error 40544, the error message is: “The database has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions. “

In SQL Server Management Studio it looks like this: 

Msg 40544, Level 20, State 5, Line 1
The database has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions. Code: 524289

If you wanted to trap this error in your C# code you could do this:

 try
{
    // ...
}
catch (SqlException sqlException)
{
    switch (sqlException.Number)
    {
        // The database has reached its size quota. Partition or delete data,
        // drop indexes, or consult the documentation for possible resolutions. 
        case 40544:
            break;
    }
}

Automatically Increasing the Database Cap

Wouldn’t it be nice to increase the cap when the database grew beyond the current cap? SQL Azure almost performs that auto growth for you, because we only charge you for the data you are using, not based on the database cap. For example, if you have .8 Gigabytes of data and a cap of 5 Gigabytes you are only charge the 0-1 Gigabyte range. When the database gets larger than 1 Gigabyte, there are no errors, however from that day onward you are charged for the 1-5 Gigabyte range. In essence this is auto growth, you tell us the maximum that the database is allowed to auto grow to, and we charge you only for the range that you are using. If you grow beyond your current range, we automatically charge you for the next range, without errors.

The caveat to this auto growth is that SQL Azure currently will not let you “jump” between web edition and business edition. You can have 1, 5, 10, 20, 30, 40 and 50 Gigabyte database caps. The one and five gigabyte database is part of the WEB edition, in order to get larger databases caps you need to change to the BUSINESS edition. This means that as your database grows, you need to keep track of that growth, and execute Transact-SQL (see the code in the next section) to change editions.

Coding Edition Changes

I was thinking about writing a little C# code data layer to automatically increase the database cap or the edition whenever an exception was caught with a SQL Server error number of 40544. This would be something to add to your Windows Azure web role, so that your database could continue growing.

However, there is a concurrency problem with this concept. Let’s say that your web site was getting thousands of connections per second, and just when the database was full, three or four threads on the Windows Azure server got error 40544. The threads would all attempt to increase the database size, make the database size grow larger than it needs to be. The solution might be to transactionally wrap the concept, so that only one thread at a time could increase the database size, and the proceeding threads would do nothing. However, the ALTER DATABASE statement in SQL Azure (the statement to increase the database size) needs to run in a batch by itself and cannot be transactionally wrapped.

I am still contemplating an elegant solution for handling a 40544 error, and automatically increasing the database edition; if I do find one I will blog about it. If you have an idea, post it to the comments below.

Changing the Database Size

While we can’t automatically change the cap, you can with some Transact-SQL. I blogged about changing the database cap in detail here. To change the database edition you can use the ALTER DATABASE Syntax like this:

 ALTER DATABASE AdventureWorksLTAZ2008R2 MODIFY (EDITION='BUSINESS', MAXSIZE=10GB)

Databases capped can be sized up and sized down. Interestingly enough if you try to downsize your database and you have too much data for that option, you will get this error:

Msg 60003, Level 16, State 1, Line 1
Operation failed because the resulting cumulative database size would exceed your database sku limit

Summary

Do you have questions, concerns, comments? Post them below and we will try to address them.