2

In SQL Azure each database has a size limitation (adjustable). In order for my service to not suddenly come to a halt I'd like to be able to programmatically find the current maximum size and current actually used space (and generate and alert once some threshold is reached).

Looks like sp_mstablespace can be used to solve this problem, but this stored procedure is not available in SQL Azure.

How do I find the current maximum allowed size and current actually used space in a SQL Azure database?

Community
  • 1
  • 1
sharptooth
  • 167,383
  • 100
  • 513
  • 979

2 Answers2

4

David has already pointed out how to find the current size of your database, if you want to know the maximum size of your database in MB, you need to run the following query in the database you're interested in:

SELECT CONVERT(BIGINT, DATABASEPROPERTYEX('DatabaseOfInterestName', 'MaxSizeInBytes')) / 1024

Reference

knightpfhor
  • 9,299
  • 3
  • 29
  • 42
3

You can calculate used space via dynamic management views (reference page here):

SELECT SUM(reserved_page_count)*8.0/1024
FROM sys.dm_db_partition_stats;

While I don't think you can retrieve max size, it doesn't hurt to simply set your database to the largest size, since you're only billed for space you use. Having said that: If you're only in the sub-5GB range, it's best to go with Web edition. If you're already at 10+ GB, there shouldn't be any harm setting max size to 150GB and then monitoring consumed size.

David Makogon
  • 69,407
  • 21
  • 141
  • 189