Anyone know how i can identify how much data I'm storing in each table within each of my storage accounts?
I know i can get the overall data used for all my storage accounts but I'm trying to figure how much each table is using.
I don;t think Azure offers anything out of the box but how would I go about creating something to figure this out?
There are two ways by which you can fetch the size of all tables in your storage account.
Option 1: Time Consuming Way
Please refer to this excellent blog post by Avkash Chauhan on how the size of an entity is calculated: http://blogs.msdn.com/b/avkashchauhan/archive/2011/11/30/how-the-size-of-an-entity-is-caclulated-in-windows-azure-table-storage.aspx. Essentially the formula is:
4 bytes + Len (PartitionKey + RowKey) * 2 bytes + For-Each Property(8 bytes + Len(Property Name) * 2 bytes + Sizeof(.Net Property Type))
What you could possibly do is fetch entities from all the tables. As you fetch entities, you compute the size of each entity and the sum of size of all entities is the size of table storage (plus some size for storing table names).
Please note that depending on the number of tables in your storage account and the data in there, it could take a while. Moreover, you would incur storage transaction costs and outbound bandwidth costs if you run this application from outside of Azure.
Option 2: Easy Way
This is something we're using in our application where we display the total size of a storage account (blobs, files, queues, and tables). Essentially, we make use of Azure Billing/Usage API. Using this API, you can fetch the storage account size. When you fetch the data from this API, the data is returned in JSON format. For storage account size, please note that this data is cumulative i.e. the value returned by the API represents the size of a storage account on the date/time value indicated in the record.
Each record contains a number of things. What's interesting for you in this case is Meter Category (which would be Storage), Meter Subcategory (which would depend on the type of your storage account) and Meter Name. The meter name that you're interested in is Standard IO - Table/ Queue (GB). As far as fetching the billing data is concerned, you can fetch that data with either daily or hourly granularity. If you're fetching the data with daily granularity, just multiply the value you get with 31 to get the total size in GB. For hourly granularity, multiply the value you get with 744 to get the total size. There's a sample application available on Github on how to use Billing/Usage API which you can find here: https://github.com/Azure/BillingCodeSamples.
If you want to see this in action, feel free to try out the application I'm building. You can learn more about this specific functionality on our blog post at http://blog.cloudportam.com/cloud-portam-updates-detailed-storage-account-usage-reports.
There is no built-in mechanism to retrieve the size of a table. You'd need to compute it. Today's Storage Analytics metrics only provides blob capacity.
You can use tool Azure Storage Manager.
On Table property dialog, click 'Calc' button.
If you don't have too many databases, use this within a query window in SSMS for each database:
select
sys.objects.name, sum(reserved_page_count) * 8.0 / 1024 [SizeInMB]
from
sys.dm_db_partition_stats, sys.objects
where
sys.dm_db_partition_stats.object_id = sys.objects.object_id
group by sys.objects.name
order by sum(reserved_page_count) DESC
