(SQL) Tip of the Day: Keep an eye on size so you do not hit your maximum

Today’s (SQL) Tip…

In Azure, customers are billed based upon, among other things, the size of their database.  This article shows how to calculate database size using a count of disk "pages." 

https://msdn.microsoft.com/en-us/library/windowsazure/ff394114.aspx

As each page is eight kilobytes in size, we can follow the logic clearly.  Size in megabytes equals the number of pages multiplied by eight kilobytes then divided to show a value in megabytes:

SELECT [Size in MB] = SUM(reserved_page_count)*8.0/1024

FROM sys.dm_db_partition_stats;

This query, however, does not include system tables or metadata objects.  It only includes user generated data.  The system generated data is still taking up space on the disk though.  So how can you calculate that?

Try this query instead as it shows historic data usage.

select time,database_name,sku,storage_in_megabytes,usage_in_seconds

from sys.resource_usage

order by database_name, time DESC

*Keep in mind that space isn't freed just because an item is deleted.  Metadata objects, system data, and even user data are simply marked as invalid or inaccessible when "deleted."  It's not really deleted until a background clean-up thread loops around to reclaim the space.  Thus, customers should keep an eye on their usage so as not to approach their limits because waiting for a clean-up task to pull a database back from those limits can often be challenging, especially in high-pressure situations.