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

thCA5DDO1F

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

question_mark1

Now, if you try to get the size of all databases using the above query, it doesn’t work right, I know sad Sad smile (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- Smile  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 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

$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 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

clip_image002

Have fun with Cloud and Powershell!!


Comments (0)