Get SQL Server database size with PowerShell

Introduction

For various reasons, following the evolution of the data size of databases is interesting for a dba or for the application team. Most of the time, this is something built-in for monitoring tools – convenient to follow the evolution over the time.
But in certain circumstances, you must turn to another alternative – no access to the monitoring tool, no monitoring tool…

Automation is nowadays a central topic, and the interest of PowerShell is no longer to be demonstrated in Windows environments. That is why, in this blog, I propose you to achieve our goal with this language.
It offers several main advantages, such as:

• Saving time
• Reducing human errors
• Standardizing actions
• Abstracting the complexity

To simplify this automation, I will rely on the PowerShell module dbatools. Very helpful, it includes many commands to automate dba’s daily tasks such as administration, migration, maintenance…

Size per database

First, you need to configure the variable $MyInstance to target the instance which is hosting the databases. The command Get-DbaDatabase will retrieve all the databases available on the instance – it includes many information, but we will only focus on those we are interesting on.
As reminder, you can use Get-Member command to list properties and methods available to in the object.

$MyInstance = '<ServerName>';
Get-DbaDatabase -SqlInstance $MyInstance | Format-Table SqlInstance, 
	Name, `
	@{Label="DataSpaceUsage_MB"; Expression={$_.DataSpaceUsage/128}}, `
	@{Label="SpaceAvailable_MB"; Expression={$_.SpaceAvailable/128}}, `
	@{Label="TotalSize_MB"; Expression={$_.Size}} -AutoSize;

Size per file

The command Get-DbaDbFile will retrieve all the database files available for all your instances, including main data files / next data files and also log files – it also includes many information, such as autogrowth configuration.

$MyInstance = '<ServerName>';
Get-DbaDbFile -SqlInstance $MyInstance | Format-Table SqlInstance, 
	Database, `
	LogicalName, `
	TypeDescription, `
	@{Label="UsedSpace_MB"; Expression={$_.UsedSpace}}, `
	@{Label="AvailableSpace_MB"; Expression={$_.AvailableSpace}}, `
	@{Label="TotalSize_MB"; Expression={$_.Size}} -AutoSize;

Conclusion

Through this blog, we worked on retrieving database sizes in an automated manner with PowerShell.
As a next step, it is possible to iterate the result on multiple instances loaded from a Central Management Server (CMS) with the command Get-DbaRegServer. It is also possible to persist the results in a table with the command Write-DbaDbTableData.