diTii.com Digital News Hub

Sign up with your email address to be the first to know about latest news and more.

I agree to have my personal information transfered to MailChimp (more information)


Wiki: Billing Numbers Directly From Transact-SQL

SQL Azure exposes two Dynamic Managed Views called sys.database_usage and sys.bandwidth_usage that show you the activity for your account. For e.g., here’s a query that show your database storage usage in US dollars for the current month on all the databases on your server: Normal 0 false false false EN-US X-NONE X-NONE

SELECT  SKU,  SUM   (  CASE WHEN USAGE.SKU = 'Web' THEN (Quantity * 9.99/31) WHEN USAGE.SKU = 'Business'  THEN (Quantity * 99.99/31) END ) AS CostInDollars FROM sys.Database_Usage USAGE 
WHERE  datepart(yy, TIME) = datepart(yy, GetUTCDate()) AND datepart(mm, TIME) = datepart(mm, GetUTCDate()) GROUP BY  SKU

This query has to be run when you are connected to the master database of your server. The output looks like this:

Storage isn’t the only cost in using SQL Azure, you can also have costs associated with transferring data to and from the data center that SQL Azure resides in. To calculate these costs in US dollars for current month, execute this Transact-SQL on your master database for server:

SELECT USAGE.Time_Period, USAGE.Direction, CASE WHEN USAGE.Direction = 'Egress' THEN 0.15 * USAGE.BandwidthInKB/(1024*1024) WHEN USAGE.DIRECTION = 'Ingress' THEN 0.10 * USAGE.BandwidthInKB/(1024*1024) END AS CostInDollars FROM ( SELECT Time_Period, Direction,         SUM(Quantity) AS BandwidthInKB FROM   sys.Bandwidth_Usage  WHERE  dtepart(yy, TIME) = datepart(yy, GetUTCDate())  AND  datepart(mm, TIME) = datepart(mm, GetUTCDate())  AND  class = 'External' GROUP BY    Time_Period, Direction ) AS USAGE

The output looks like this:


Share This Story, Choose Your Platform!

Get Latest News

Subscribe to Digital News Hub

Get our daily newsletter about the latest news in the industry.
First Name
Last Name
Email address
Secure and Spam free...