As the popularity of Microsoft's Azure platform continues to rise, I find myself constantly adding new scripts to my library. As technology continues to evolve we need to find different solutions to meet technology needs. SQL Azure has introduced a number of changes to processes and protocols as it relates to development & support. One of the most viewed topics on this blog is my Determining SQL Server Table Size, a quick post from 2007 after helping a co-worker solve a problem. Fast forward 8 years and this post still sees more than 1,000 views in a month. However, try to run that on SQL Azure and you will have an issue. SP_SpaceUsed is not available to us on that platform! In this post, I will provide a few helpful queries to get around this limitation.
For those that are familiar with the sp_spaceused solution, you would simply call this procedure and the results would show you the size of the current database. The SQL Azure ready solution for this is to query sys.dm_db_partition_stats and make a proper conversion for the sum of reserved pages and convert to MB. The following is a helpful Stored Procedure that I often add to databases to help.
All Object Sizes
For those familiar with the sp_spaceused process, you could expand the call to sp_spaceused by simply calling "sp_spaceused objectname." to see the same metrics but for an individual database object, and with my older query you could enumerate the tables calling this procedure to see the results for all tables. Given that we don't have sp_spaceused available in SQL Azure we need to look towards a new process to complete this task as well. The following stored procedure expands on the example for the entire database but divided out by object.
Single Object Sizes
Using the information from the prior procedure, we can easily then create a procedure to help us go after a specific object. The final helpful query in my toolbox is as follows
You can call this using "EXEC Support_SingleObjectSize 'EventLog'" for example to get the size of the EventLog table. It should also be noted that the parameter to this is sysname NOT a varchar column for proper matching.
Although a bit different SQL Azure supported features will work on non-SQL azure installations. SO the above queries will be helpful for those of us working in dual environments as well. I hope this has been helpful!