Skip to content

Capacity Planning – Calculate SQL data file sizes remotely

by Dan Thompson on May 13th, 2013

This has been a very handy script recently. I created it so I can calculate the surface area of data and log files on disk. It can be executed remotely as long as you have administrative permissions. Using the script below I can for example calculate the log files total size on a server excluding TempDB as follows:

Get-SQLDataFileSize -ServerInstance DBS01\DEV -ExcludeTempDB -FileType "LOGS"

Or I can find the size of the DATA files (again excluding TempDB):

Get-SQLDataFileSize -ServerInstance DBS01\DEV -ExcludeTempDB -FileType "LOGS"

I was asked recently to split a bunch of databases from one server onto two servers. I used the function get a list of all the files on a server (data and logs), and then I could figure out how much disk space would be needed by each side of the split (I took the figures into Excel for this bit…i’m not an animal!).

So in order to find out the totals for data AND logs I would execute it as follows:

Get-SQLDataFileSize -ServerInstance DBS01\DEV -ExcludeTempDB

It’s been a handy one for the toolbelt recently. The full code listing is below. Use the copy button which pops up to copy the code.

Enjoy.

<#
    .SYNOPSIS
        Get-SQLDataFileSize function returns a file size for each SQL file and a total
    .DESCRIPTION
        Get-SQLDataFileSize function returns a file size for each SQL file and a total. 
	This can be limited to calculating just one database, or all databases and to just the log files, or the data files.
    .PARAMETER ServerInstance
        SQL Instance of the server to query. This must be on the local machine, unless you have administrative access to the remote machine.
    .PARAMETER ExcludeTempDB
        If you pass this switch, the calculations will not take into account the TempDB files.
    .PARAMETER DatabaseName
        The name of the database you require to calculate. If you include this option it will only calculate for this database, rather than all the databases on the server.
    .PARAMETER FileType
	Options: ROWS, DATA, LOG, LOGS, LDF, MDF, NDF, ALL. The Default is ALL. 
	
	ROWS/DATA	: all data files (excluding logs)
	LOG/LOGS	: all log files
	MDF		: only files with the MDF extension
	NDF		: only files with the NDF extension
	LDF		: only files with the LDF extension

	ALL		: includes all files regardless of extension and whether it is LOG or DATA.


    .EXAMPLE
	Get-SQLDataFileSize -ServerInstance DBS01\DEV -ExcludeTempDB -FileType "DATA"
    .EXAMPLE
	Get-SQLDataFileSize -ServerInstance DBS01\DEV -DatabaseName "ReportServer"
#>
function Get-SQLDataFileSize () 
{
	Param
	(
		[parameter(Mandatory=$True)][string] $ServerInstance,
		[switch] $ExcludeTempDB,
		[parameter(Mandatory=$False)][string] $DatabaseName=$null,
		[parameter(Mandatory=$False)][ValidateSet('ROWS','DATA','LOG','LOGS','LDF','MDF','NDF','ALL')][string]$FileType="ALL",
		[switch] $ShowFullPaths
	)


	[string] $type_desc = "";
	[string] $ext = "";

	switch ($FileType)
		{
			"ROWS" { $type_desc = "ROWS"; $ext = $null}
			"DATA" { $type_desc = "ROWS"; $ext = $null}
			"LOG" { $type_desc = "LOG"; $ext = $null}
			"LOGS" { $type_desc = "LOG"; $ext = $null}
			"LDF" { $type_desc = $null; $ext = ".ldf"}
			"NDF" { $type_desc = $null; $ext = ".ndf"}
			"MDF" { $type_desc = $null; $ext = ".mdf"}
			"ALL" { $type_desc = $null; $ext = $null}
		}

	$sql = "
			SELECT 
				SERVERPROPERTY('ComputerNamePhysicalNetBIOS') [Hostname], 
				physical_name, 
				CASE WHEN SUBSTRING(physical_name,2,1) = ':' 
					THEN '\\' + CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as varchar(255)) + '\' + REPLACE(physical_name,':','$') 
				END [physical_name_urn]
			FROM sys.master_files

			WHERE (type_desc = '$type_desc' OR '$type_desc' ='')
			AND (RIGHT(physical_name,4) = '$ext' OR '$ext' = '')
			AND (database_id = db_id('$DatabaseName') OR '$DatabaseName' = '')
			AND (database_id != db_id('tempdb') OR '$ExcludeTempDB'='False')
		";

	$logFiles = Invoke-SqlCmd -ServerInstance $ServerInstance -Database master -Query $sql `
			| ForEach-Object { Get-Item $_.physical_name_urn }

	if($ShowFullPaths) {
		$logFiles = $logFiles | Select FullName, Length
	} else {
		$logFiles = $logFiles | Select Name, Length
	}

	
		
	$logFilesSum = ($logFiles | Measure-Object -Property Length -Sum).Sum / 1GB
	
	$logFiles	
	"         {0:N2}" -f $logFilesSum + " GB"
}

From → SQL Server