There is a T-SQL way of finding the size of the database and there are some public domain scripts to find the same, I also created one and that’s here (indeed with more clarity and structured output)
CREATE TABLE #DatabaseInfo (
, name NVARCHAR(500)
, AlltablesizeinKB int
CREATE TABLE #DatabaseInfo1 (
DECLARE @dbname nvarchar(100)
DECLARE @command NVARCHAR(4000)
DECLARE @command1 NVARCHAR(4000)
DECLARE @testflag SMALLINT -- 0 execute. 1 Test
SET @testflag = 0
DECLARE dbcursor1 CURSOR FAST_FORWARD FOR
name NOT IN ('master')
FETCH NEXT FROM dbcursor1 INTO @dbname;
WHILE @@FETCH_STATUS = 0
SET @command =
'select database_id, sys.objects.name, sum(reserved_page_count) * 8192 / 1024 from
sys.dm_db_partition_stats, sys.objects, sys.databases
where is_ms_shipped=0 and database_id>1 and sys.dm_db_partition_stats.object_id = sys.objects.object_id group by database_id, sys.objects.name
SET @command1= 'select [name], sum(reserved_page_count) * 8192 / 1024 as dbsizeinKB
from sys.dm_db_partition_stats, sys.databases where database_id>1 group by database_id, sys.databases.name'
-- select * from sys.objects
IF @testflag = 0
INSERT INTO #DatabaseInfo
EXEC sp_executesql @command
INSERT INTO #DatabaseInfo1
EXEC sp_executesql @command1
FETCH NEXT FROM dbcursor1 INTO @dbname;
SELECT * from #databaseinfo1
DROP TABLE #DatabaseInfo
DROP TABLE #DatabaseInfo1
Now the Problem
Now, if you try to get the size of all databases using the above query, it doesn’t work right, I know sad (sorry wrong emoticon)- Surprised! better word
Firstly, you cannot use “USE Database” in Azure, hence changing the context of the user database from within the query to another, doesn’t work. Secondly, I tried to run the query in one go for all databases and retrieve the results, but then I realized that sys.dm_db_partition_stats in Azure is USER DB scoped and hence we cannot run the query in the context of master either, ALRIGHT!!!
I have figured out a better way of doing this bit and have automated the output generation in excel. Here is what needs to be done. Thanks to Matt Lavery my friend, PFE at Microsoft Australia who gave a very good idea and logic behind getting this result using Powershell, I changed this further and made it look a little more enhanced and better.
This is how it works, try it and let me know, how you find it. Follow the steps as is
1. Download the Azure Powershell module directly from http://go.microsoft.com/?linkid=9811175&clcid=0x409 (Save the exe- this will install Azure Powershell in your machine) and use the cmdlets that it provides. The only problem with this is that you have provide the credentials to the server either via a prompt or you can hard code these if you like (my example below uses a prompt).
2. Here is what I have put together for checking the database capacity and have also added MaxSize which will help you identify the Maxsize each database can grow in your subscription. Change the Azure DB server name accordingly in this script! Use Powershell_ISE for better visibility. Let me know if you have issues running the script or have any questions!
(Note: You can copy and paste the code below into a notepad, rename it to anything (DBsize) and then change the extension to .ps1 (DBSize.ps1))- I am sure you all know how to execute Powershell, but me being me- Open-> Windows Azure Powershell (Admin mode)-> Browse till the folder where you have saved the powershell script->.\DBSize.ps1
#Make sure you have imported your PublishSettings file as per http://msdn.microsoft.com/en-us/library/jj554332%28v=azure.100%29.aspx#BKMK_Configure
#Import the Azure module
Import-Module "C:\Program Files (x86)\Microsoft SDKs\Windows Azure\PowerShell\Azure\Azure.psd1"
$cred = Get-Credential
#create a crediential to use
# NOTE: You will be prompted for Authentication to the server
$ctx = New-AzureSqlDatabaseServerContext -ServerName "ttgochqr7t-Change the Server name to yours only in the red zone and get rid of the yellow" –Credential $cred
#get all the dbs
$dbs = Get-AzureSqlDatabase $ctx
$excel = new-object -comobject excel.application
$excel.visible = $true
$chartType = "microsoft.office.interop.excel.xlChartType" -as [type]
$workbook = $excel.workbooks.add()
$workbook.WorkSheets.item(1).Name = "dbs"
$sheet = $workbook.WorkSheets.Item("dbs")
$x = 2
$sheet.cells.item(1,1) = "DB Name"
$sheet.cells.item(1,2) = "Current Size (MB)"
$sheet.cells.item(1,3) = "Max Size (GB)"
foreach ($db in $dbs)
$sheet.cells.item($x,1) = $db.Name
$sheet.cells.item($x,2) = $db.SizeMB
$sheet.cells.item($x,3) = $db.MaxSizeGB
$range = $sheet.usedRange
#$vFullPath = 'C:\DataProtector\Data\AzureDb.xls'
There are plenty of properties available for each database. If you want to see how to hardcode the credentials check out http://blogs.msdn.com/b/windowsazure/archive/2013/02/07/windows-azure-sql-database-management-with-powershell.aspx, though I wouldn’t suggest this due to security reasons.
Final output looks like this
Have fun with Cloud and Powershell!!