SQL Azure: Database Size History

You may end up in a situation where you need to know how your databases have evolved over time from a storage standpoint, and how much they were used. For example, you may want to know how your databases were used last month if your customers complained about performance. Or you may need to see how fast your database is growing to help you plan for future capacity.

Two system views are now available (see disclaimer further down): sys.resource_usage and sys.resource_stats. Both views contain a time stamp, a database name, the database SKU (web, enterprise), usage_in_seconds and storage_in_megabytes. The resource_storage view also has an end_time column, which does not show in the resource_stats view.

Although both views contain similar information, they appear to work on a different time scale. The resource_usage stores hourly information and can go back a few months (three months in my case), while the resource_stats contains information as granular as every five minutes but only goes back a few weeks.

For example, the following statement returns the total usage (in seconds) of a database, including the minimum and maximum storage required by my database on a monthly basis.

SELECT
    CAST(year(time) as nvarchar(4)) + '-' + CAST(month(time) as nvarchar(2)) as YearMonth,
    database_name, 
    sum(usage_in_seconds) as total_usage_sec,
    MIN(storage_in_megabytes) as Min_MB,
    MAX(storage_in_megabytes) as Max_MB
FROM sys.resource_usage
WHERE database_name = 'EnzoLog'
GROUP BY database_name, CAST(year(time) as nvarchar(4)) + '-' + CAST(month(time) as nvarchar(2))

The above statement returns the following rows for my EnzoLog database:

YearMonth database_name total_usage_sec Min_MB Max_MB
2012-1 EnzoLog 170 4.50 5.37
2012-2 EnzoLog 417 5.10 11.18
2012-3 EnzoLog 855 5.10 11.15
2012-4 EnzoLog 497 4.45 5.45

 

Because the resource_stats table returns information up to 5 minutes increments, you can have a more granular view of your database usage patterns during the day.

NOTE: The statements above are running against database objects found in the master database of a SQL Azure server. They are not currently supported nor are they documented. This means that these objects could change at any time, or even be dropped in the future. Do not use these objects in production systems until they are documented by Microsoft®.

Print | posted @ Monday, April 23, 2012 1:08 AM

Comments on this entry:

Comments are closed.

Comments have been closed on this topic.