# Getting SQL Server File Sizes and Space Used with dbatools

I read a great blog post about answering the question how big is the database using T-SQL on SQL Buffet and wondered how much I could do with the dbatools module

The dbatools module (for those that don’t know) is a PowerShell module written by amazing folks in the community designed to make administrating your SQL Server significantly easier using PowerShell. The instructions for installing it are available here It comprises of 182 separate commands at present (11 March 2017 Version 0.8.938)

I know that there is a Get-DBADatabaseFreeSpace Command written by Mike Fal b | t and using Glenn Berry’s diagnostic queries

First thing as always is to look at the help

 Get-Help Get-DbaDatabaseFreespace -ShowWindow

which will show you the help for the command and some examples

Lets look at the details for a single instance

 Get-DbaDatabaseFreespace -sqlserver $server This is what it looks like and yes it really is that fast, I have not speeded this up. 232 ms to get those details for an instance with 19 databases What information do you get ? Lets look at the information for a single database, you get an object for each file Server : SQL2014SER12R2 Database : DBA-Admin FileName : DBA-Admin_System FileGroup : PRIMARY PhysicalName : F:\DBA-Admin_System.MDF FileType : ROWS UsedSpaceMB : 3 FreeSpaceMB : 253 FileSizeMB : 256 PercentUsed : 1 AutoGrowth : 0 AutoGrowType : MB SpaceUntilMaxSizeMB : 16777213 AutoGrowthPossibleMB : 0 UnusableSpaceMB : 16777213 Server : SQL2014SER12R2 Database : DBA-Admin FileName : DBA-Admin_Log FileGroup : PhysicalName : G:\DBA-Admin_Log.LDF FileType : LOG UsedSpaceMB : 32 FreeSpaceMB : 224 FileSizeMB : 256 PercentUsed : 12 AutoGrowth : 256 AutoGrowType : MB SpaceUntilMaxSizeMB : 2528 AutoGrowthPossibleMB : 2304 UnusableSpaceMB : 0 Server : SQL2014SER12R2 Database : DBA-Admin FileName : DBA-Admin_User FileGroup : UserFG PhysicalName : F:\DBA-Admin_User.NDF FileType : ROWS UsedSpaceMB : 1 FreeSpaceMB : 255 FileSizeMB : 256 PercentUsed : 0 AutoGrowth : 256 AutoGrowType : MB SpaceUntilMaxSizeMB : 5119 AutoGrowthPossibleMB : 4864 UnusableSpaceMB : 0 There is a lot of useful information returned for each file. Its better if you use Out-GridView as then you can order by columns and filter in the top bar. As always, PowerShell uses the permissions of the account running the sessions to connect to the SQL Server unless you provide a separate credential for SQL Authentication. If you need to connect with a different windows account you will need to hold Shift down and right click on the PowerShell icon and click run as a different user. Lets get the information for a single database. The command has dynamic parameters which populate the database names to save you time and keystrokes But you may want to gather information about more than one server. lets take a list of servers and place them into a variable. You can add your servers to this variable in a number of ways, maybe by querying your CMDB or using your registered servers or central management server $SQLServers = 'SQL2005Ser2003','SQL2012Ser08AG3','SQL2012Ser08AG1','SQL2012Ser08AG2','SQL2014Ser12R2','SQL2016N1','SQL2016N2','SQL2016N3','SQLVnextN1','SQLvNextN2'


and then

Get-DbaDatabaseFreespace -SqlInstance $SQLServers | Out-GridView As you can see, you get a warning quite correctly, that the information for the asynchronous secondary node of the availability group databases is not available and I did not have all of my servers running so there are a couple of could not connect warnings as well. You can still filter very quickly. dbatools is tested from SQL2000 to SQL vNext as you can see below (although I don’t have a SQL2000 instance) Not only on Windows, this command will work against SQL running on Linux as well So if we want to know the total size of the files on disk for the database we need to look at the FileSizeMB property $server = 'SQL2014Ser12R2'
$dbName = 'AdventureWorksDW2014' Get-DbaDatabaseFreespace -SqlServer$server -database $dbName | Select Database,FileName,FileSizeMB Of course that’s an easy calculation here but if we have numerous files then it may be tougher. we can use the Measure-Object command to sum the properties. We need to do a bit of preparation here and set a couple of calculated properties to make it more readable $server = 'SQL2014Ser12R2'
$dbName = 'AdventureWorksDW2014'$database = @{Name = 'Database'; Expression = {$dbname}}$FileSize = @{Name = 'FileSize'; Expression = {$_.Sum}} Get-DbaDatabaseFreespace -SqlServer$server -database $dbName | Select Database,FileSizeMB | Measure-Object FileSizeMB -Sum | Select$database ,Property, $filesize Maybe we want to look at all of the databases on an instance. Again, we have to do a little more work here $server = 'SQL2014Ser12R2'
$srv = Connect-DbaSqlServer$server
$SizeonDisk = @()$srv.Databases |ForEach-Object {
$dbName =$_.Name
$database = @{Name = 'Database'; Expression = {$dbname}}
$FileSize = @{Name = 'FileSize'; Expression = {$_.Sum}}
$SizeOnDisk += Get-DbaDatabaseFreespace -SqlServer$server -database $dbName | Select Database,FileSizeMB | Measure-Object FileSizeMb -Sum | Select$database ,Property, $Filesize }$SizeOnDisk

If we wanted the databases ordered by the size of their files we could do this

$SizeOnDisk |Sort-Object Filesize -Descending As it is PowerShell we have an object and we can use it any way we like. Maybe we want that information in a text file or a csv or an excel file or in an email, PowerShell can do that  ## In a text file$SizeonDisk | Out-file C:\temp\Sizeondisk.txt
Invoke-Item C:\temp\Sizeondisk.txt
## In a CSV
$SizeonDisk | Export-Csv C:\temp\Sizeondisk.csv -NoTypeInformation notepad C:\temp\Sizeondisk.csv ## Email Send-MailMessage -SmtpServer$smtp -From DBATeam@TheBeard.local -To JuniorDBA-Smurf@TheBeard.Local
-Subject "Smurf this needs looking At" -Body $SizeonDisk ## Email as Attachment Send-MailMessage -SmtpServer$smtp -From DBATeam@TheBeard.local -To JuniorDBA-Smurf@TheBeard.Local 
-Subject "Smurf this needs looking At" -Body "Smurf" -Attachments C:\temp\Sizeondisk.csv

I had a little play with Boe Prox PoshCharts (you have to use the dev branch) to see if I could get some nice charts and unfortunately the bar charts did not come out so well but luckily the donut and pie charts did. (I’m a DBA I love donuts!)

$SizeonDisk| Out-PieChart -XField Database -YField FileSize -Title "UsedSpaceMB per Database on$Server" -IncludeLegend -Enable3D
$SizeonDisk| Out-DoughnutChart -XField Database -YField FileSize -Title "UsedSpaceMB per Database on$Server" -IncludeLegend -Enable3D

So the point is, whatever you or your process requires you can pretty much bet that PowerShell can enable it for you to automate.

You can make use of all of the properties exposed by the command. If you want to only see the files with less than 20% space free

 Get-DbaDatabaseFreespace -SqlServer $server | Where-Object {$_.PercentUsed -gt 80}

you can also use the command to check for file growth settings as well

 Get-DbaDatabaseFreespace -SqlServer $server | Where-Object {$_.AutoGrowType  -ne 'Mb'}

Or maybe you want to know the FileSize, Used and Free Space per database

 $server = 'SQL2014Ser12R2'$srv = Connect-DbaSqlServer $server$SizeonDisk = @()
$srv.Databases |ForEach-Object {$dbName = $_.Name$database = @{Name = 'Database'; Expression = {$dbname}}$MB = @{Name = 'Mbs'; Expression = {$_.Sum}}$SizeOnDisk += Get-DbaDatabaseFreespace -SqlServer $server -database$dbName | Select Database,FileSizeMB, UsedSpaceMB, FreeSpaceMb |  Measure-Object FileSizeMb , UsedSpaceMB, FreeSpaceMb -Sum  | Select $database ,Property,$Mb
}
$SizeOnDisk  Hopefully that has given you a quick insight into another one of the fabulous dbatools commands. Any questions, comment below or head over to the SQL Server Community Slack via https://sqlps.io/slack Happy Automating NOTE – The major 1.0 release of dbatools due in the summer 2017 may have breaking changes which will stop the above code from working. There are also new commands coming which may replace this command. This blog post was written using dbatools version 0.8.942 You can check your version using  Get-Module dbatools and update it using an Administrator PowerShell session with  Update-Module dbatools You may find that you get no output from Update-Module as you have the latest version. If you have not installed the module from the PowerShell Gallery using  Install-Module dbatools Then you can use  Update-dbatools Advertisements # Add Adventure Works Database to Windows Azure VM This has been an interesting journey. The Adventure Works database is frequently used in blogs and reference books and I wanted to install it in my Windows Azure Learning Lab and I also wanted to automate the process. The easiest way is to download the Windows Azure MDF file from http://msftdbprodsamples.codeplex.com/ jump through all the security warnings in Internet Explorer and save the file and then create the database as follows CREATE DATABASE AdventureWorks2012 ON (FILENAME = 'PATH TO \AdventureWorks2012_Data.mdf') FOR ATTACH_REBUILD_LOG ; That is the way I will do it from now on! After reading this page I tried to download the file with Powershell but it would not as I could not provide a direct link to the file. Maybe someone can help me with that. So I thought I would use my SkyDrive to hold the MDF file and map a drive on the server. \to do this you need to add the Desktop Experience feature to the server. This can be done as follows Import-Module ServerManager Add-WindowsFeature Desktop-Experience -restart This will take a few minutes to install, reboot and then configure the updates before you can log back in. While it is doing this log into your SkyDrive and navigate to a folder and copy the URL to notepad It will look something like this https://skydrive.live.com/?lc=2137#cid=XXXXXXXXXXXXXXXX&id=CYYYYYYYYYYYYYYYY Copy the GUID after the cid= and write this command net use T: \\d.docs.live.net@SSL\XXXXXXXXXXXXXXXX /user:$user $password I keep this in a script and pass the user and password in via Read-Host However, if you try to copy the item from the folder you will get an error The file size exceeds the limit allowed and cannot be saved So you will need to alter a registry key as follows Set-ItemProperty -Path HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\WebClient\Parameters -Name FileSizeLimitInBytes -Value 4294967295 and then restart the WebClient service Then run the net use command to map the drive and copy the file with Copy-Item But my script to auto install the Adventure Works database via Powershell once you have completed all the pre-requisites is $user = Read-Host "user"
$password = Read-Host "Password" net use T: \\d.docs.live.net@SSL\XXXXXXXXXXXXXXX /user:$user \$password
New-Item c:\AW -ItemType directory
Invoke-Sqlcmd -ServerInstance YourServerName -Database master -Query "CREATE DATABASE AdventureWorks2012
FOR ATTACH_REBUILD_LOG ;"

To be honest I don’t think I will use this method as my copy failed twice before it succeeded so I will just download the file and create the database!!

Please don’t ever trust anything you read on the internet and certainly don’t implement it on production servers without first both understanding what it will do and testing it thoroughly. This solution worked for me in my environment I hope it is of use to you in yours but I know nothing about your environment and you know little about mine