SQL Server: Quick Space & File Layout Analysis With PowerShell and PowerBI

Long time ago, I created a little chunk of TSQL to get a glimpse of my space and file layout information at the Database/File/Filegroup level. It is a basic routine that spins through all the databases of an instance to collect space usage data. Over the years, I have used it a lot and it has helped me identify things like

• What is taking up the most space on the instance by database?
• Where is the most free space?
• How big can certain databases get? (MAX)
• Where am I running out of space?
• What percent of space is database [xyz] of the whole instance?
• What percent of MAX size is still available for database [xyz]?
• …and so on

This however failed to give me the information in a graphic form that can be quickly consumed and interpreted. I had to work hard to make sense of the data.

The code is below. It produces output at three levels based on value of @InfoLevel  variable. Set @InfoLevel to * for “everything”, or Database or FileGroup or File if you want it at that level. You can also get the data for a specific Database and/or FileGroup by replacing the ‘*’ with the specific Database name and/or FileGroup name below for variables @DatabaseName and @FileGroupName.

DECLARE @SQL VARCHAR(MAX);
DECLARE @InfoLevel VARCHAR(10);
DECLARE @DatabaseName VARCHAR(50);
DECLARE @FilegroupName VARCHAR(100);

SET @InfoLevel = '*';		--* for everything (or) Database/FileGroup/File
SET @DatabaseName = '*';	--* for everything (or) the Database name
SET @FilegroupName = '*';   --* for all filegroups (or) specific filegroup name

CREATE TABLE #output(
			ServerName varchar(128),
			DBName varchar(128),
			FileId bigint,
			PhysicalName varchar(260),
			ReportDate datetime,
			Name varchar(128),
			GroupId int,
			FileGroupName varchar(128),
			Size_MB real,
			Free_MB real,
			Max_Size_MB real,
			Free_Of_Max_Size_MB real
		);

SET @SQL = 'USE [?];
IF ''?'' = REPLACE(''' + @DatabaseName + ''', ''*'', ''?'')
BEGIN
	INSERT #output
	SELECT CAST(SERVERPROPERTY(''ServerName'') AS varchar(128)) AS ServerName,
	''?'' AS DBName,
	f.fileid,
	f.filename AS PhysicalName,
	CAST(FLOOR(CAST(getdate() AS float)) AS datetime) AS ReportDate,
	f.Name,
	f.GroupId,
	g.groupname FileGroupName,
	CAST (size*8.0/1024.0 AS int) AS Size_MB,
	CAST((size - FILEPROPERTY(f.name,''SpaceUsed''))*8.0/1024.0 AS int) AS Free_MB,
	CASE WHEN maxsize = -1 THEN -1
		ELSE CAST (maxsize*8.0/1024.0 AS int)
	END AS Max_Size_MB,
	CASE WHEN maxsize = -1 THEN -1
		ELSE CAST((maxsize - FILEPROPERTY(f.name,''SpaceUsed''))*8.0/1024.0 AS int)
	END AS Free_of_Max_Size_MB
	FROM sysfiles f
	LEFT JOIN sysfilegroups g
	ON f.groupid = g.groupid
	WHERE COALESCE(g.groupname,''*'') = REPLACE(''' + @FilegroupName + ''', ''*'', COALESCE(g.groupname,''*''))
END;
';

exec sp_MSforeachdb @command1= @SQL; 

----------------------------------
--Database level totals
----------------------------------
IF COALESCE(@InfoLevel, '*') IN ('*', 'DATABASE')
BEGIN
	WITH Inst_Totals
	AS
	(
		SELECT
			SUM(Size_MB) AS Sum_Size_MB_Inst,
			SUM(Free_MB) AS Sum_Free_MB_Inst,
			SUM(Max_Size_MB) AS Sum_Max_Size_MB_Inst,
			SUM(Free_Of_Max_Size_MB) AS Sum_Free_Of_Max_Size_MB_Inst
		FROM #output
		GROUP BY ServerName
	)
	SELECT
		det.ServerName,
		det.DBName,
		'Database' AS ReportLevel,
		ReportDate,
		COUNT(1) File_Count,
		SUM(Size_MB) Size_MB,
		SUM(Free_MB) Free_MB,
		CASE WHEN SUM(Max_Size_MB) < 0 THEN -1
			ELSE SUM(Max_Size_MB)
		END AS Max_Size_MB,
		CASE WHEN SUM(Max_Size_MB) < 0 THEN -1
			ELSE SUM(Free_Of_Max_Size_MB)
		END AS Free_Of_Max_Size_MB,
		SUM(CASE WHEN Sum_Size_MB_Inst=0 THEN 0 ELSE ROUND(100 * Size_MB / Sum_Size_MB_Inst,2) END) AS Size_PCT_Inst,
		SUM(CASE WHEN Sum_Free_MB_Inst=0 THEN 0 ELSE ROUND(100 * Free_MB / Sum_Free_MB_Inst,2) END) Free_Size_PCT_Inst,
		CASE WHEN SUM(Max_Size_MB) < 0 THEN -1
			ELSE SUM(CASE WHEN Sum_Max_Size_MB_Inst=0 THEN 0 ELSE ROUND(100 * Max_Size_MB / Sum_Max_Size_MB_Inst,2) END)
		END AS Max_Size_PCT_Inst,
		CASE WHEN SUM(Max_Size_MB) < 0 THEN -1
			ELSE SUM(CASE WHEN Sum_Free_Of_Max_Size_MB_Inst=0 THEN 0 ELSE ROUND(100 * Free_Of_Max_Size_MB / Sum_Free_Of_Max_Size_MB_Inst,2) END)
		END AS Free_Of_Max_Size_PCT_Inst
	FROM #output det,
		Inst_Totals inst_tot
	GROUP BY
		det.ServerName,
		det.DBName,
		ReportDate;
END;

----------------------------------
--Database + Filegroup level totals
----------------------------------
IF COALESCE(@InfoLevel, '*') IN ('*', 'FILEGROUP')
BEGIN
	WITH Inst_Totals
	AS
	(
		SELECT
			SUM(Size_MB) AS Sum_Size_MB_Inst,
			SUM(Free_MB) AS Sum_Free_MB_Inst,
			CASE WHEN SUM(Max_Size_MB) < 0 THEN -1
				ELSE SUM(Max_Size_MB)
			END AS Sum_Max_Size_MB_Inst,
			CASE WHEN SUM(Max_Size_MB) < 0 THEN -1
				ELSE SUM(Free_Of_Max_Size_MB)
			END AS Sum_Free_Of_Max_Size_MB_Inst
		FROM #output
		GROUP BY ServerName
	),
	DB_Totals
	AS
	(
		SELECT
			DBName,
			SUM(Size_MB) AS Sum_Size_MB_DB,
			SUM(Free_MB) AS Sum_Free_MB_DB,
			CASE WHEN SUM(Max_Size_MB) < 0 THEN -1
				ELSE SUM(Max_Size_MB)
			END AS Sum_Max_Size_MB_DB,
			CASE WHEN SUM(Max_Size_MB) < 0 THEN -1
				ELSE SUM(Free_Of_Max_Size_MB)
			END AS Sum_Free_Of_Max_Size_MB_DB
		FROM #output
		GROUP BY ServerName, DBName
	)
	SELECT
		det.ServerName,
		det.DBName,
		'Database+Filegroup' AS ReportLevel,
		ReportDate,
		Name,
		GroupId,
		FileGroupName,
		SUM(Size_MB) Size_MB,
		SUM(Free_MB) Free_MB,
		CASE WHEN SUM(Max_Size_MB) < 0 THEN -1
			ELSE SUM(Max_Size_MB)
		END AS Max_Size_MB,
		CASE WHEN SUM(Max_Size_MB) < 0 THEN -1
			ELSE SUM(Free_Of_Max_Size_MB)
		END AS Free_Of_Max_Size_MB,
		--FG Level
		CASE WHEN SUM(Free_MB)=0 THEN 0 ELSE ROUND(100 * SUM(Free_MB) / SUM(Size_MB),2) END AS Free_Size_PCT_FG,
		CASE WHEN SUM(Max_Size_MB) < 0 THEN -1
			ELSE CASE WHEN SUM(Free_Of_Max_Size_MB)=0 THEN 0 ELSE ROUND(100 * SUM(Free_Of_Max_Size_MB) / SUM(Max_Size_MB) ,2) END
		END AS Free_Of_Max_Size_PCT_FG,
		--DB Level
		SUM(CASE WHEN Sum_Size_MB_DB=0 THEN 0 ELSE ROUND(100 * Size_MB / Sum_Size_MB_DB,2) END) AS Size_PCT_DB,
		SUM(CASE WHEN Sum_Free_MB_DB=0 THEN 0 ELSE ROUND(100 * Free_MB / Sum_Free_MB_DB,2) END) Free_Size_PCT_DB,
		CASE WHEN SUM(Max_Size_MB) < 0 THEN -1
			ELSE SUM(CASE WHEN Sum_Max_Size_MB_DB=0 THEN 0 ELSE ROUND(100 * Max_Size_MB / Sum_Max_Size_MB_DB,2) END)
		END AS Max_Size_PCT_DB,
		CASE WHEN SUM(Max_Size_MB) < 0 THEN -1
			ELSE SUM(CASE WHEN Sum_Free_Of_Max_Size_MB_DB=0 THEN 0 ELSE ROUND(100 * Free_Of_Max_Size_MB / Sum_Free_Of_Max_Size_MB_DB,2) END)
		END AS Free_Of_Max_Size_PCT_DB,
		--Instance Level
		SUM(CASE WHEN Sum_Size_MB_Inst=0 THEN 0 ELSE ROUND(100 * Size_MB / Sum_Size_MB_Inst,2) END) AS Size_PCT_Inst,
		SUM(CASE WHEN Sum_Free_MB_Inst=0 THEN 0 ELSE ROUND(100 * Free_MB / Sum_Free_MB_Inst,2) END) Free_Size_PCT_Inst,
		CASE WHEN SUM(Max_Size_MB) < 0 THEN -1
			ELSE SUM(CASE WHEN Sum_Max_Size_MB_Inst=0 THEN 0 ELSE ROUND(100 * Max_Size_MB / Sum_Max_Size_MB_Inst,2) END)
		END AS Max_Size_PCT_Inst,
		CASE WHEN SUM(Max_Size_MB) < 0 THEN -1
			ELSE SUM(CASE WHEN Sum_Free_Of_Max_Size_MB_Inst=0 THEN 0 ELSE ROUND(100 * Free_Of_Max_Size_MB / Sum_Free_Of_Max_Size_MB_Inst,2) END)
		END AS Free_Of_Max_Size_PCT_Inst
	FROM #output det,
		Inst_Totals inst_tot,
		DB_Totals db_tot
	WHERE
		det.DBName = db_tot.DBName
	GROUP BY
		det.ServerName,
		det.DBName,
		ReportDate,
		Name,
		GroupId,
		FileGroupName;
END;

----------------------------------
--Database + Filegroup + File level totals
----------------------------------
IF COALESCE(@InfoLevel, '*') IN ('*', 'FILE')
BEGIN
	WITH Inst_Totals
	AS
	(
		SELECT
			SUM(Size_MB) AS Sum_Size_MB_Inst,
			SUM(Free_MB) AS Sum_Free_MB_Inst,
			CASE WHEN SUM(Max_Size_MB) < 0 THEN -1
				ELSE SUM(Max_Size_MB)
			END AS Sum_Max_Size_MB_Inst,
			CASE WHEN SUM(Max_Size_MB) < 0 THEN -1
				ELSE SUM(Free_Of_Max_Size_MB)
			END AS Sum_Free_Of_Max_Size_MB_Inst
		FROM #output
		GROUP BY ServerName
	),
	DB_Totals
	AS
	(
		SELECT
			DBName,
			SUM(Size_MB) AS Sum_Size_MB_DB,
			SUM(Free_MB) AS Sum_Free_MB_DB,
			CASE WHEN SUM(Max_Size_MB) < 0 THEN -1
				ELSE SUM(Max_Size_MB)
			END AS Sum_Max_Size_MB_DB,
			CASE WHEN SUM(Max_Size_MB) < 0 THEN -1
				ELSE SUM(Free_Of_Max_Size_MB)
			END AS Sum_Free_Of_Max_Size_MB_DB
		FROM #output
		GROUP BY ServerName, DBName
	),
	Filegroup_Totals
	AS
	(
		SELECT
			DBName,
			FileGroupName,
			SUM(Size_MB) AS Sum_Size_MB_FG,
			SUM(Free_MB) AS Sum_Free_MB_FG,
			CASE WHEN SUM(Max_Size_MB) < 0 THEN -1
				ELSE SUM(Max_Size_MB)
			END AS Sum_Max_Size_MB_FG,
			CASE WHEN SUM(Max_Size_MB) < 0 THEN -1
				ELSE SUM(Free_Of_Max_Size_MB)
			END AS Sum_Free_Of_Max_Size_MB_FG
		FROM #output
		GROUP BY ServerName, DBName, FileGroupName
	)
	SELECT
		det.ServerName,
		det.DBName,
		'Database+FileGroup+File' AS ReportLevel,
		ReportDate,
		det.Name,
		det.GroupId,
		det.FileGroupName,
		det.FileId AS File_Id,
		PhysicalName,
		Size_MB,
		Free_MB,
		Max_Size_MB,
		Free_Of_Max_Size_MB,
		CASE WHEN Sum_Size_MB_FG=0 THEN 0 ELSE ROUND(100 * Size_MB / Sum_Size_MB_FG,2) END AS Size_PCT_FG,
		CASE WHEN Sum_Free_MB_FG=0 THEN 0 ELSE ROUND(100 * Free_MB / Sum_Free_MB_FG,2) END AS Free_Size_PCT_FG,
		CASE WHEN Max_Size_MB < 0 THEN -1
			ELSE CASE WHEN Sum_Max_Size_MB_FG=0 THEN 0 ELSE ROUND(100 * Max_Size_MB / Sum_Max_Size_MB_FG,2) END
		END AS Max_Size_PCT_FG,
		CASE WHEN Max_Size_MB < 0 THEN -1
			ELSE CASE WHEN Sum_Free_Of_Max_Size_MB_FG=0 THEN 0 ELSE ROUND(100 * Free_Of_Max_Size_MB / Sum_Free_Of_Max_Size_MB_FG,2) END
		END AS Free_Of_Max_Size_PCT_FG,
		CASE WHEN Sum_Size_MB_DB=0 THEN 0 ELSE ROUND(100 * Size_MB / Sum_Size_MB_DB,2) END AS Size_PCT_DB,
		CASE WHEN Sum_Free_MB_DB=0 THEN 0 ELSE ROUND(100 * Free_MB / Sum_Free_MB_DB,2) END Free_Size_PCT_DB,
		CASE WHEN Max_Size_MB < 0 THEN -1
			ELSE CASE WHEN Sum_Max_Size_MB_DB=0 THEN 0 ELSE ROUND(100 * Max_Size_MB / Sum_Max_Size_MB_DB,2) END
		END AS Max_Size_PCT_DB,
		CASE WHEN Max_Size_MB < 0 THEN -1
			ELSE CASE WHEN Sum_Free_Of_Max_Size_MB_DB=0 THEN 0 ELSE ROUND(100 * Free_Of_Max_Size_MB / Sum_Free_Of_Max_Size_MB_DB,2) END
		END AS Free_Of_Max_Size_PCT_DB,
		CASE WHEN Sum_Size_MB_Inst=0 THEN 0 ELSE ROUND(100 * Size_MB / Sum_Size_MB_Inst,2) END AS Size_PCT_Inst,
		CASE WHEN Sum_Free_MB_Inst=0 THEN 0 ELSE ROUND(100 * Free_MB / Sum_Free_MB_Inst,2) END Free_Size_PCT_Inst,
		CASE WHEN Max_Size_MB < 0 THEN -1
			ELSE CASE WHEN Sum_Max_Size_MB_Inst=0 THEN 0 ELSE ROUND(100 * Max_Size_MB / Sum_Max_Size_MB_Inst,2) END
		END AS Max_Size_PCT_Inst,
		CASE WHEN Max_Size_MB < 0 THEN -1
			ELSE CASE WHEN Sum_Free_Of_Max_Size_MB_Inst=0 THEN 0 ELSE ROUND(100 * Free_Of_Max_Size_MB / Sum_Free_Of_Max_Size_MB_Inst,2) END
		END AS Free_Of_Max_Size_PCT_Inst
	FROM #output det
		INNER JOIN Inst_Totals inst_tot
			ON 1=1
		INNER JOIN DB_Totals db_tot
			ON det.DBName = db_tot.DBName
		LEFT JOIN Filegroup_Totals fg_tot
			ON det.DBName = fg_tot.DBName
			AND det.FileGroupName = fg_tot.FileGroupName;

END;

DROP TABLE #output;

This is just great on its own as I get information shown below. It is basically data like this at the Database/FileGroup/File levels

• Database/Filegroup/File name
• Size
• Free size
• Max size
• Free of Max size
• Size as a percent at the instance level
• Free size as a percent at the instance level
• Max size as a percent at the instance level
• Free of Max size as a percent at the instance level

At the Instance/Database level

At the FileGroup level

At the File level

Why analyze this in PowerBI:

If you want to do something programmatic or do some quick analysis, it usually stops here. However, if you wanted to slice and dice this information to look at it different ways as shown in the screenshot below, you need PowerBI. When collecting this data using PowerShell and visualizing it in PowerBI you get a few benefits

• Can look at more than one instance and compare/contrast
• A picture is worth a thousand words and drillable one is worth a million!
• Don’t have to try out a 100 different WHERE clause filters to slice and dice
• Can send straight screenshots to upper management
• Analyze/Summarize Size by Env, Drive, FileExt, Intance, Database etc..

Now that we see what we want, let us see how easy it is to build it. To do so, we need to

1. Collect the space data from the instances we are interested in using PowerShell
2. Connect to the collected data from PowerBI and analyze!

How can I collect the data with PowerShell?

This is quite simple. I already have a blog post on how to collect anything to a custom table as long as you can query it!

https://sqljana.wordpress.com/2018/01/10/powershell-export-querytosqltable-export-all-of-glenn-berrys-dmvs-sp_whoisactive-sp_blitz-any-of-your-own-queries-to-sql-server-tables/

We will simply export the results of the TSQL at the top of this post using the method shown in my referenced blog post above.

Please replace the values with your specifics. You would save the TSQL block at the beginning of this post to a file and reference it in the $queryFile below. The other substitutions are intuitive.

#https://sqljana.wordpress.com/2018/01/10/powershell-export-querytosqltable-export-all-of-glenn-berrys-dmvs-sp_whoisactive-sp_blitz-any-of-your-own-queries-to-sql-server-tables/

[string] $saveToInstance = 'MySQLServer\MyInstanceName'
[string[]] $runOnInstance = @('FinDev\inst01','FinDev\inst02','FinDev\inst03','FinDev\inst04',
                            'FinUsr\inst01','FinUsr\inst02','FinUsr\inst03','FinUsr\inst04',
                            'FinPlt\inst01','FinPlt\inst02','FinPlt\inst03','FinPlt\inst04',
                            'FinLod\inst01','FinLod\inst02','FinLod\inst03','FinLod\inst04',
                            'FinPrd\inst01','FinPrd\inst02','FinPrd\inst03','FinPrd\inst04'
                            )
[string] $saveToDatabase = 'DBAUtil'
[string] $saveToSchema = 'dbo'

[string] $queryFile = '**** PATH LOCATION WHERE YOU SAVED THE SQL ****'
[string] $queryString = Get-Content $queryFile | Out-String

$query = New-Object -TypeName PSObject
$query | Add-Member -MemberType NoteProperty -Name QueryNr -Value 1
$query | Add-Member -MemberType NoteProperty -Name QueryTitle -Value 'SpaceInfo_File'
$query | Add-Member -MemberType NoteProperty -Name Query -Value ($queryString -replace "SET @InfoLevel = '\*'", "SET @InfoLevel = 'File'")
$query | Add-Member -MemberType NoteProperty -Name Description -Value 'Gets File level space information'
$query | Add-Member -MemberType NoteProperty -Name DBSpecific -Value $false
$query | Add-Member -MemberType NoteProperty -Name SaveResultsToTable -Value 'SpaceInfo_File'

#Now $queries can be passed into the function call
$output = Export-QueryToSQLTable `
        -Queries @($query) `
        -RunOnInstances $runOnInstance `
        -RunIncludeDBs @() `
        -RunExcludeDBs @() `
        -RunExcludeAllSystemDBs: $true `
        -RunExcludeAllUserDBs: $false `
        -RunOnDBsWithOwner @() `
        -RunOnDBsWithStatus @('Normal') `
        -SaveResultsToInstance $saveToInstance `
        -SaveResultsToDatabase $saveToDatabase `
        -SaveResultsToSchema $saveToSchema `
        -SaveResultsTruncateBeforeSave: $false

This will export the file level space usage information to a table named SpaceInfo_File. BTW, if you wanted to collect additional data, feel free to do so. You just have to include all the queries in the $queries parameter!

Code/Dashboard is on my GitHub

Once the information is collected to a table, use the PowerBI template (used in the screenshot) that I have saved on GitHub to point to it. You would have to change how “Environment” should be interpreted from the instance name. The related Export-QueryToSQLTable is also available on GitHub.

How to use the Space Analysis Dashboard?

Once you are done downloading the PowerBI dashboard and connecting to your source, you can start analyzing. It is simple and straight-forward to use. Just remember to multi-select if necessary. Also you can click on a single color/selection in any chart/matrix to filter the other visuals for the selected one. You could even multi-select colors in charts. Just explore! You cannot break it. You may however find out that you need some additional indexes depending on the amount of data you collect and how you analyze.

Filter by any or all of the following (multi-select allowed):

For example you may want to see how much space is used on E:\ drive in PRD and PLT environments for the database FinMain by mdf files. What constitutes an “Environment” for you can be defined in the expression for “Env” in the PowerBI dashboard (or) by creating a view on the SQL Server side which has the column Env.

Space usage at the “Environment” level:

Here you can see at a high level, the space consumed at the environment level. As you can see quickly

• USR is the largest followed by PRD and LOD environments that are similar in size.
• From the bar chart, we can see that FinMain database is the biggest DB in each every environment!
• Total size of the USR environment is 4 TB (data from the matrix)
• Across environments for all the instances, we are using 11.3 TB of space for this project

Space usage at the “Instance” level (drill in one level):

I just drilled-in one level on all the charts to get to the instance-level data.

We can see that

• The “inst01” instances across the board are the largest instances.
• Again, the FinMain database is the largest database (Acqa color on bar chart)
• A third of the space is free (500 GB) in FinDev\inst01 (Row 1 on matrix)

You could drill down further to the database level

There are also some alternate views that you could explore. This one uses the TableSorter PowerBI visual.

Filtered by just .ldf to see Log file sizes

Below, I filtered to show just the .ldf files to see the log file spaces across the board. Again, the FinUser\inst01.FinMain’s log stands out! You could drill up and down with this filter in place to make other findings.

Comparing a single database across environments

Let us compare the size between the different environments for “FinMain” database by filtering on the DB. The line in the bar chart shows the free size within the DB.

You would probably use this differently than I do based on your needs but I think I have made the point that this is so much better than analyzing data in the “results grid” as text with varying WHERE clauses in queries!

I would love to build another dashboard that shows the space trending data..something to do in the future.