Tag: Database space

A Better Way To Get SQL File Space Information

I wish this was actually a better way, but really it’s an idea for a better way to get at the data.

Something that has come up at every place I’ve worked is the desire to monitor growth on data and log files for databases. If you aren’t using some 3rd party monitoring software this is a pretty painful operation which forces us to jump back to cursor type operations where we have to gather information one database at a time (although this does not apply to transaction logs).


Gathering Log Space

To currently gather log space you would use DBCC SQLPERF(‘LOGSPACE’) which will give you size and used percentage for every log file on a server.

Dumping that information into a table allows you to manipulate the data to figure out KB/MB/GB usage (rather than just plain percent).

As an example (GatherLogSpace.sql):
download (1)

Gathering Log Space

This is a reasonable amount of work, but what if you want to get space used in data files? Well that gets a whole lot more complicated.


Gathering Data File Space

In order to gather information on space used in data files you actually have to go to each and every database and run a query using sys.dm_db_file_space_usage.

As an example (GatheringDataFileSpace.sql):
download (2)

Gathering the space available in data files

Strangely the sys.dm_db_file_space_usage DMV only contains the data file information. The log file data can only be obtained using the first process. This means that there is no single place that you can go to get file usage information for a database.


What about PowerShell?

PowerShell could give you the data, but it is still a cursor type operation, and you have to work through each database, drilling down to the log information, the filegroup and file inside each of those filegroups.

What we really need is a nice set based query that we could run in TSQL to give us all the information that is needed.


What I Would Like To See

Right now you could go and grab the size for each and every file for every database inside of SQL Server by querying sys.master_files:

download (3)
Basic information from sys.master_files

There’s a whole lot more information in sys.master_files, including growth data, so you can know exactly how much each file will grow the next time it does.

Even though there’s this great data there is nothing to give us space used data. It would probably take too much overhead to keep that information up to date, but there’s no reason that we shouldn’t be able to easily get at the data when we want to.

Ideally we would see the existing DMV changed into a function, which would accept a database_id and file_id, and return the same data that it does now.

If that were the case it would be simple to run one query that would give us the size of each file, used space, free space, and how much the file would grow the next time an autogrowth hit.

This could quickly and easily give us all the data we want:

download (4) Mock up of easily obtained file space information

As a hope and desire to see this happen I’ve created a Microsoft Connect item – sys.dm_db_file_space_usage Should Be A Function

If you manage databases, and it’s something that you’d really like to have (who wouldn’t?) then please go and give it an upvote. There’s a chance it might make it into the product at some point, and that would make a DBAs life a little bit easier.


Checking Database Space With PowerShell

Have you ever been asked the question “quick, I need to know how much free space there is in the Nodrap database on server Cromulent”?

Ever wished that you could quickly grab that information?

You might well have a TSQL script that will grab that information for you. I’ve been working on pulling that information centrally, grabbing that data for multiple servers becomes a much simpler task when using PowerShell.



Why PowerShell and not TSQL?

For me the big bonus that PowerShell provides me for grabbing this data is that I can pull it quickly and easily, I can also simply run it for multiple machines and the same script works for versions SQL 2000 and up.



How big is my database?

Open up your favorite PowerShell ISE (you can even use the one that comes with Windows). If the aren’t already loaded you’ll need to add the SQL snapins

Add-PSSnapin SqlServer* 



To get a list of databases, their sizes and the space available in each on the local SQL instance.

dir SQLSERVER:SQLlocalhostdefaultdatabases | Select Name, Size, SpaceAvailable | ft -auto


CheckDBWPS SizeSpace


Very quick and easy. The information here is a little misleading though.

The Size is the size of the database and includes size of the transaction log(s). SpaceAvailable only represents the space available in the data files however, unless we’re looking at SQL 2000 in which case it’s the free space in the data and log files. To make things even more confusing Size is reported in MB and SpaceAvailable in KB.

To get more details we need to look at the transaction log information.



How big is my transaction log?

To grab this information we need to go deeper. In this case we will focus on the AdventureWorks database.

dir SQLSERVER:SQLlocalhostdefaultdatabasesAdventureWorkslogfiles | 

    Select Name, Size, UsedSpace | ft -auto


CheckDBWPS LogSpace


 This information is all in KB which helps have it make a little more sense.


Doing the math

Now we have the size of the database, the size of the log and the free space in each it’s some quick math which gives us the sizing information.

  • Size = Database Size in MB
  • Size / 1024 (from log) = Log size in MB
  • UsedSpace / 1024 = Log used in MB
  • (Size – UsedSpace) / 1024 = Log free in MB
  • Size – (Size / 1024) (from log) = Data files size in MB
  • SpaceAvailable / 1024 = Space free in data files
  • Size – (SpaceAvailable / 1024) – (Size / 1024) (from log) = Space used in data files
  • Size – ((SpaceAvailable / 1024) – ((Size – UsedSpace) / 1024) = Space used in data files (SQL 2000)


Outputting nice results

Running all this and grabbing the data from different areas can lead to messy results. This is where the PowerShell DataTable come to the rescue.

A DataTable is a PowerShell object, much like a SQL table that can hold data for you.

First you create the object, define and add columns and then add rows before finally returning the data.

Here’s a quick example:

$dataTable = New-Object system.Data.DataTable "SomeStuff"

#Specify the column names for the data table

$col1 = New-Object system.Data.DataColumn FunkyText,([string])

$col2 = New-Object system.Data.DataColumn JustANumber,([int])

$col3 = New-Object system.Data.DataColumn TodaysDate,([datetime])


#Add the columns to the data table




#Create a new row

$row = $dataTable.NewRow()

#Add data for each column in the row

$row.FunkyText = "Something really funky"

$row.JustANumber = 1395

$row.TodaysDate = ((Get-Date ).ToString("yyyy-MM-dd HH:mm:ss"))

#Add the new row to the datatable



#Output the datatable

$dataTable | out-gridview


CheckDBWPS Datatable



Putting everything together

Running the following script will pull the data and log information for all the databases on the SQL instance specified in the top parameter. It also handles named instances without modification, and is factored to return the correct information for SQL 2000 as well as higher versions

Note: Run DBCC UPDATEUSAGE for SQL 2000 instances to ensure that the data is accurate

Param ($Servername= 'localhost')

Function Get-DBSizes ($Servername)



#Check to see if it's a name instance, if it is the location will be different

if ($Servername -ilike "**") { $BaseLocation = "SQLSERVER:SQL$Servernamedatabases" }

    else { $BaseLocation = "SQLSERVER:SQL$Servernamedefaultdatabases" }

$dblist = dir $BaseLocation -Force | select Name

foreach ($db in $dblist)


    $location = $db.Name

    $locationFixed = $location -replace "[.]", "%2e"

    #Grab the database information

    $DBInfo = dir $BaseLocation -Force | Where-Object {$_.Name -eq $location; $_.Refresh()} | 

        select Name, 





    #Pull the log information

    #Use measure-object to sum up sizes in the event that we might have more than one log file

    $logsize = dir $BaseLocation$locationFixedlogfiles | Where-Object {$_.Name; $_.Refresh()} | Measure-Object -Property Size -Sum 

    $logused = dir $BaseLocation$locationFixedlogfiles | Where-Object {$_.Name; $_.Refresh()} | Measure-Object -Property UsedSpace -Sum 

    $sp = $DBInfo.SpaceAvailable

    $TotalDBSizeMB = $DBInfo.size

    $LogSizeMB = ($logsize.sum / 1024)

    $LogUsedMB = ($logused.Sum / 1024)

    $LogFreeMB = ($LogSizeMB - $LogUsedMB)

    $DataFilesSizeMB = ($TotalDBSizeMB - $LogSizeMB)

    $SpaceAvail = ($dbinfo.SpaceAvailable / 1024)

    #Because SQL2000 spaceavailable    includes log space we have to do different calculations depending on version

    #Run DBCC UPDATEUSAGE on your 2000 databases nightly to help ensure this data is accurate

    $Compat = $DBInfo.CompatibilityLevel

    if ($Compat -eq 'Version80') { $DataFileFreeMB = ($SpaceAvail - $LogFreeMB)    }

        else { $DataFileFreeMB = $SpaceAvail }



    $DataFileUsedMB = ($DataFilesSizeMB - $DataFileFreeMB)

    $DataFilePercentUsed = ($DataFileUsedMB / $DataFilesSizeMB) * 100

    $DataFilePercentFree = 100 - $DataFilePercentUsed 

    $LogPercentUsed = ($LogUsedMB / $LogSizeMB) * 100

    $LogPercentFree = 100 - $LogPercentUsed

    $date = (Get-Date ).ToString("yyyy-MM-dd HH:mm:ss")


    #Write the results into the data table

    $row = $dataTable.NewRow()

    $row.ServerName = $Servername

    $row.DatabaseName = $location

    $row.TotalDBSizeMB = $TotalDBSizeMB

    $row.DataFilesSizeMB = $DataFilesSizeMB

    $row.DataFilesUsedMB = $DataFileUsedMB

    $row.DataFilesFreeMB = $DataFileFreeMB

    $row.DataPercentUsed = $DataFilePercentUsed

    $row.DataPercentFree = $DataFilePercentFree

    $row.LogFilesSizeMB = $LogSizeMB

    $row.LogFilesUsedMB = $LogUsedMB

    $row.LogFilesFreeMB = $LogFreeMB

    $row.LogPercentUsed = $LogPercentUsed

    $row.LogPercentFree = $LogPercentFree

    $row.Date = $date


    #And we are done




#Create data table to hold the results

$dataTable = New-Object system.Data.DataTable "Results"

#Specify the column names for the data table

$col1 = New-Object system.Data.DataColumn ServerName,([string])

$col2 = New-Object system.Data.DataColumn DatabaseName,([string])

$col3 = New-Object system.Data.DataColumn TotalDBSizeMB,([int])

$col4 = New-Object system.Data.DataColumn DataFilesSizeMB,([int])

$col5 = New-Object system.Data.DataColumn DataFilesUsedMB,([int])

$col6 = New-Object system.Data.DataColumn DataFilesFreeMB,([int])

$col7 = New-Object system.Data.DataColumn DataPercentUsed,([decimal])

$col8 = New-Object system.Data.DataColumn DataPercentFree,([decimal])

$col9 = New-Object system.Data.DataColumn LogFilesSizeMB,([int])

$col10 = New-Object system.Data.DataColumn LogFilesUsedMB,([int])

$col11 = New-Object system.Data.DataColumn LogFilesFreeMB,([int])

$col12 = New-Object system.Data.DataColumn LogPercentUsed,([decimal])

$col13 = New-Object system.Data.DataColumn LogPercentFree,([decimal])

$col14 = New-Object system.Data.DataColumn Date,([datetime])

#Add the columns to the data table
















#Provide the name of the SQL server that we want to check

$WarningPreference = "silentlycontinue"

#Call the function to populate the results

#get-content c:Tempserverlist.txt | % {Get-DBSizes $_  } 

Get-DBSizes $Servername


#Output the results

$dataTable | Out-GridView



Click to embiggen


Final note

Download the PS1 file and save it on your machine, from there you can call the script and just pass in the SQL instance to get the results eg:

./Get-DatabaseSize.ps1 localhost
./Get-DatabaseSize.ps1 cromulentawesomesauce

In a follow up post I’ll show how we apply a minor tweak to the script and have the data loaded into a SQL database.