Getting SQL Server File Sizes and Space Used with dbatools

Posted on March 29, 2017 by RobSewell

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

02 - singel server.gif

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

03 - Measure Command.PNG

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

05 dynamic parameters.gif

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

06 - Many servers ogv.PNG

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)

07 - filter ogv

Not only on Windows, this command will work against SQL running on Linux as well

08 - linux.PNG

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

08a - filesize.PNG

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

09 - filessize

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

10 - size on disk

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

$SizeOnDisk |Sort-Object Filesize -Descending

11 - size sorted.PNG

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

12 - donuts.PNG

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}

13 - percent used.PNG

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

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

14 - autogrowth.PNG

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

15 totals.PNG

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

Show AutoGrowth Events with Powershell to CSV

Posted on February 15, 2015 by RobSewell

This week I was reading Pinal Daves post about Autogrowth Events

http://blog.sqlauthority.com/2015/02/03/sql-server-script-whenwho-did-auto-grow-for-the-database/

as it happened I had a requirement to make use of the script only a few days later. I was asked to provide the information in a CSV so that the person who required the information could manipulate it in Excel.

I am a great believer in Automation. If you are going to do something more than once then automate it so I wrote two functions, added them to TFS and now they will be available to all of my team members next time they load Powershell.

Why two functions? Well Pinal Daves script gets the information from the default trace for a single database but there may be times when you need to know the autogrowth events that happened on a server with multiple databases.

I use a very simple method for doing this as I have not found the correct way to parse the default trace with Powershell. The functions rely on Invoke-SQLCMD2 which I also have in my functions folder and pass the query from Pinal Daves Blog post as a here string

$Results = Invoke-Sqlcmd2 -ServerInstance $Server -Database master -Query $Query

To output to CSV I use the Export-CSV cmdlet

if($CSV)
{
$Results| Export-Csv -Path $CSV
}

And to open the CSV I add a [switch] parameter. You can find out more about parameters here or by

Get-Help about_Functions_Advanced_Parameters

so the parameter block of my function looks like

param
(
[Parameter(Mandatory=$true)]
[string]$Server,
[Parameter(Mandatory=$true)]
[string]$Database,
[Parameter(Mandatory=$false)]
[string]$CSV,
[Parameter(Mandatory=$false)]
[switch]$ShowCSV
)

Now when I am asked again to provide this information it is as easy as typing

Show-AutogrowthServer -Server SQL2014Ser12R2

Show-AutogrowthDatabase -Server SQL2014Ser12R2 -Database Autogrowth

and the results will be displayed as below

just a side note. Pinal Daves script uses @@servername in the where clause and if you have renamed your host the script will be blank. The resolution to this is to runt he following T-SQL

 sp_dropserver 'OLDSERVERNAME';
GO
sp_addserver NEWSERVERNAME, local;
GO

You can find the scripts here

Show-AutoGrowthServer

Show-AutoGrowthDatabase

and all of my Script Center Submissions are here

As always – The internet lies, fibs and deceives and everything you read including this post should be taken with a pinch of salt and examined carefully. All code should be understood and tested prior to running in a live environment.

Number of VLFs and Autogrowth Settings Colour Coded to Excel with PowerShell

Posted on October 6, 2014 by RobSewell

So you have read up on VLFs

No doubt you will have read this post by Kimberly Tripp and this one and maybe this one too and you want to identify the databases in your environment which have a large number of VLFs and also the initial size and the autogrowth settings of the log files.

There are several posts about this and doing this with PowerShell like this one or this one. As is my wont I chose to output to Excel and colour code the cells depending on the number of VLFs or the type of Autogrowth.

There is not a pure SMO way of identifying the number of VLFs in a log file that I am aware of and it is simple to use DBCC LOGINFO to get that info.

I also wanted to input the autogrowth settings, size, space used, the logical name and the file path. I started by getting all of my servers into a $Servers Array as follows

$Servers = Get-Content 'PATHTO\sqlservers.txt'

Whilst presenting at the Newcastle User Group, Chris Taylor b | t asked a good question. He asked if that was the only way to do this or if you could use your DBA database.

It is much better to make use of the system you already use to record your databases. It will also make it much easier for you to be able to run scripts against more specific groups of databases without needing to keep multiple text files up to date. You can accomplish this as follows

$Query = 'SELECT Name FROM dbo.databases WHERE CONDITION meets your needs'
$Servers = Invoke-Sqlcmd -ServerInstance MANAGEMENTSERVER -Database DBADATABASE -Query $query

I then create a foreach loop and a server SMO object (Did you read my blog post about snippets? the code for a SMO Server snippet is there) returned the number of rows for DBCC LOGINFO and the information I wanted.

foreach ($Server in $Servers)
    {
      $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Server
      foreach ($db in $srv.Databases|Where-Object {$_.isAccessible -eq $True})
      {
        $DB.ExecuteWithResults('DBCC LOGINFO').Tables[0].Rows.Count
         $db.LogFiles | Select Growth,GrowthType,Size, UsedSpace,Name,FileName
       }
    }

It’s not very pretty or particularly user friendly so I decided to put it into Excel

I did this by using my Excel Snippet

$snippet = @{
      Title = 'Excel Object';
      Description = 'Creates a Excel Workbook and Sheet';
      Text = @'
      # Create a .com object for Excel
    `$xl = new-object -comobject excel.application
    `$xl.Visible = `$true # Set this to False when you run in production
    `$wb = `$xl.Workbooks.Add() # Add a workbook
    `$ws = `$wb.Worksheets.Item(1) # Add a worksheet
    `$cells=`$ws.Cells
    #Do Some Stuff - perhaps -
      `$cells.item(`$row,`$col)=`'Server`'
      `$cells.item(`$row,`$col).font.size=16
      `$Cells.item(`$row,`$col).Columnwidth = 10
      `$col++
    `$wb.Saveas(`'C:\temp\Test`$filename.xlsx`')
    `$xl.quit()
    Stop-Process -Name EXCEL
    '@
    }
    New-IseSnippet @snippet

and placed the relevant bits into the foreach loop

foreach ($Server in $Servers)
    {
      $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Server
      foreach ($db in $srv.Databases|Where-Object {$_.isAccessible -eq $True})
      {
        $VLF = $DB.ExecuteWithResults('DBCC LOGINFO').Tables[0].Rows.Count
        $logFile = $db.LogFiles | Select Growth,GrowthType,Size, UsedSpace,Name,FileName
        $Name = $DB.name
        $cells.item($row,$col)=$Server
        $col++
        $cells.item($row,$col)=$Name
        $col++
        $cells.item($row,$col)=$VLF
        $col++
        $col++
        $Type = $logFile.GrowthType.ToString()
        $cells.item($row,$col)=$Type
        $col++
        $cells.item($row,$col)=($logFile.Size)
        $col++
        $cells.item($row,$col)=($logFile.UsedSpace)
        $col++
        $cells.item($row,$col)=$logFile.Name
        $col++
        $cells.item($row,$col)=$logFile.FileName

I had to use the ToString() method on the Type property to get Excel to display the text. I wanted to set the colour for the VLF cells to yellow or red dependant on their value and the colour of the growth type cell to red if the value was Percent. This was achieved like this

if($VLF -gt $TooMany)
    {
      $cells.item($row,$col).Interior.ColorIndex = 6 # Yellow
    }
    if($VLF -gt $WayTooMany)
    {
      $cells.item($row,$col).Interior.ColorIndex = 3 # Red
    }
    if($Type -eq 'Percent')
    {
      $cells.item($row,$col).Interior.ColorIndex = 3 #Red
    }

I also found this excellent post by which has many many snippets of code to work with excel sheets.

I used

$cells.item($row,$col).HorizontalAlignment = 3 #center
$cells.item($row,$col).HorizontalAlignment = 4 #right
$ws.UsedRange.EntireColumn.AutoFit()

although I had to move the Title so that it was after the above line so that it looked ok.

You can find the script here. As always test it somewhere safe first, understand what it is doing and any questions get in touch.

SQL DBA with A Beard

He's a SQL DBA and he has a beard

Tag Archives: Auto Growth

Getting SQL Server File Sizes and Space Used with dbatools

Like this:

Show AutoGrowth Events with Powershell to CSV

Like this:

Number of VLFs and Autogrowth Settings Colour Coded to Excel with PowerShell

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: