Find the Size of Azure Databases using Powershell

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)

Code Snippet

CREATE TABLE #DatabaseInfo ( database_id varchar(30) , name NVARCHAR(500) , AlltablesizeinKB int ) CREATE TABLE #DatabaseInfo1 ( dbname varchar(400), dbsizeinKB int ) 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 SELECT name FROM sys.databases WHERE name NOT IN ('master') OPEN dbcursor1 FETCH NEXT FROM dbcursor1 INTO @dbname; WHILE @@FETCH_STATUS = 0 BEGIN 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 BEGIN INSERT INTO #DatabaseInfo EXEC sp_executesql @command INSERT INTO #DatabaseInfo1 EXEC sp_executesql @command1 END ELSE SELECT @command FETCH NEXT FROM dbcursor1 INTO @dbname; END SELECT * FROM #DatabaseInfo SELECT * from #databaseinfo1 DROP TABLE #DatabaseInfo DROP TABLE #DatabaseInfo1 CLOSE dbcursor1 DEALLOCATE dbcursor1

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 https://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

Code 

#Make sure you have imported your PublishSettings file as per https://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 $x++ } $range = $sheet.usedRange $range.EntireColumn.AutoFit() $workbook.charts.add() #$vFullPath = 'C:\DataProtector\Data\AzureDb.xls' #$Excel.SaveAs($vFullPath) #$Excel.Close()

 

There are plenty of properties available for each database. If you want to see how to hardcode the credentials check out https://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!!