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

DatabaseLevelSpaceUsage

At the FileGroup level

DatabaseFileGroupUsage

At the File level

DatabaseFileUsage

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..

SpaceDashboard

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.

SpaceDashboard_Filters

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

SpaceDashboard_EnvLevel

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)

SpaceDashboard_InstanceLevel

You could drill down further to the database level

SpaceDashboard_MatrixDatabaseLevel

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

SpaceDashboard_TableSorter

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.

SpaceDashboard_LogFilesOnly

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.

SpaceDashboard_CompareFinMainAcrossEnvs

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.

Advertisements

23 thoughts on “SQL Server: Quick Space & File Layout Analysis With PowerShell and PowerBI

  1. Looks very nice.
    Small note. When summing Max_Size_MB you are summing -1 (which means unlimited) with other max size limits. 🙂
    When file autogrowth limit is -1 you should take disk free space I think.

    1. Good point. Your suggestion (or) N/A should be used instead of summing up -1 (for MAX). Starting with 2008, this DMV seems to get the drive information. It does complicate things slightly if the files are spread on multiple drives/volumes.

      SELECT f.database_id, f.file_id, volume_mount_point, total_bytes, available_bytes
      FROM sys.master_files AS f
      CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id);

  2. Hi, SQLJana,

    I got below, please help.

    Unable to export queries to database tables! Error in step: “Running query: [sp_WhoIsActiveInfo3] on [master]]”
    A parameter cannot be found that matches parameter name ‘QueryTimeout’.
    At E:\temp\SQLDashboard\Export-QueryToSQLTable.ps1:810 char:9
    + Throw “Unable to export queries to database tables! Error in …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : OperationStopped: (Unable to expor…’QueryTimeout’.:String) [], RuntimeException
    + FullyQualifiedErrorId : Unable to export queries to database tables! Error in step: “Running query: [sp_WhoIsActiveInfo3] on [master]]”
    A parameter cannot be found that matches parameter name ‘QueryTimeout’.

    1. Hi Tony. Sorry about that. I have been working with multiple versions (publishing vs in-house) and I messed up. Can you please replace all “Invoke-DBASQLCmd” with “Invoke-Sqlcmd2” (should be three occurrences) and give it a try?

      1. thank you, Jana. That’s the trick and I got sp_whoisactive working. I am more interested in your script for database/filegroup/file information. When I ran it, it complained Database ‘?’ does not exist.
        Please share your knowledge again.

        Unable to export queries to database tables! Error in step: “Running query: [SpaceInfo_File] on [master]]”
        Database ‘?’ does not exist. Make sure that the name is entered correctly.
        At E:\temp\SQLDashboard\Export-QueryToSQLTable.ps1:810 char:9
        + Throw “Unable to export queries to database tables! Error in …
        + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo : OperationStopped: (Unable to expor…ered correctly.:String) [], RuntimeException
        + FullyQualifiedErrorId : Unable to export queries to database tables! Error in step: “Running query: [SpaceInfo_File] on [master]]”
        Database ‘?’ does not exist. Make sure that the name is entered correctly.

      2. Can you please try to run the SQL as is on the instance using SSMS and see if you are able to run it? Sp_msforeachdb is an undocumented stored proc that is somewhat unreliable as it is known to skip databases but I have not seen this specific error.

  3. Sorry, I forgot to mention that, yes, from SSMS, it works great and I got all results as in your article. When I save it to a file (for $queryFile) and invoke it from powershell, it fails with above error.

  4. Sorry again. Can you please make the path replacements in this template and give it a try? I missed a escaping backslash (also updated the post)

    [string] $saveToInstance = ‘localhost’
    [string[]] $runOnInstance = @(‘localhost’)
    [string] $saveToDatabase = ‘DeleteMe’
    [string] $saveToSchema = ‘dbo’

    [string] $queryFile = ‘C:\Temp\GetSpaceUsageStats.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

  5. I get the following error

    Unable to export queries to database tables! Error in step: “Decide on target schema/table names etc]”
    SaveResultsToTable is empty. Specify a non-empty QueryTitle for query or as an attribute with non-empty value in Queries input parameter! See examples for reference.
    At C:\script\Export-QueryToSQLTable.ps1:856 char:9
    + Throw “Unable to export queries to database tables! Error in …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : OperationStopped: (Unable to expor… for reference.:String) [], RuntimeException
    + FullyQualifiedErrorId : Unable to export queries to database tables! Error in step: “Decide on target schema/table names etc]”
    SaveResultsToTable is empty. Specify a non-empty QueryTitle for query or as an attribute with non-empty value in Queries input parameter! See examples for reference.

    Any ideas please?

    1. Hi Jeff,

      I am sorry for the late response. It is complaining about the QueryTitle being empty. If you follow the sample, it shows how to fill in the properties on the input object you need to pass in. If a target table name is not specified, it uses the QueryTitle to come-up with the table name. Please let me know once you check the sample.

      1. Hi SQLJana

        This is my powershell script. I still can’t see where the problem lies.

        #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 = ‘localhost’
        [string[]] $runOnInstance = @(‘localhost’)
        [string] $saveToDatabase = ‘DBAUtil’
        [string] $saveToSchema = ‘dbo’

        [string] $queryFile = ‘C:\script\GetSpaceUsageStats.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

        Thanks for responding and regards,
        Jeff

  6. Hi Jeff,

    Sorry you are having problems. I am not sure where the issue is. I took the exact code you have above and replaced single quotes and double quotes that were messed up by the HTML to use the right single and double quotes but I am able to run it fine as you can see. I did replace the instance/database name to work with the setup I have. Can you please make sure that all the single and double quotes (beginning and ending quotes) are proper?

    #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 = ‘localhost’
    [string[]] $runOnInstance = @(‘localhost’)
    [string] $saveToDatabase = ‘DBAUtil’
    [string] $saveToSchema = ‘dbo’

    [string] $queryFile = “C:\~Tmp\SpaceInfo.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
    Run of every instance specified
    Run each query and export results to table
    Get the list of qualifying databases
    Run query on qualifying databases
    Running query: [SpaceInfo_File] on [master]
    ————————————————–
    Saving to: [dbo.SpaceInfo_File]
    Creating PK on: [SpaceInfo_File]
    Completed export!

    1. Thank you for taking the time to run my code. At least it confirms that the syntax is correct. I also cut and pasted from HTML so perhaps I do have an invalid character in there somewhere.
      Regards

  7. SQLJana,

    Tried it. I got below, please advise:

    Saving to: [dbo.SpaceInfo_File]
    Run each query and export results to table
    Unable to export queries to database tables! Error in step: “Run each query and
    export results to table]”
    Cannot validate argument on parameter ‘PercentComplete’. The 200 argument is greater
    than the maximum allowed range of 100. Supply an argument that is less than or equal
    to 100 and then try the command again.
    At line:856 char:9
    + Throw “Unable to export queries to database tables! Error in …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : OperationStopped: (Unable to expor… command again.:St
    ring) [], RuntimeException
    + FullyQualifiedErrorId : Unable to export queries to database tables! Error in s
    tep: “Run each query and export results to table]”
    Cannot validate argument on parameter ‘PercentComplete’. The 200 argument is grea
    ter than the maximum allowed range of 100. Supply an argument that is less than o
    r equal to 100 and then try the command again.

    Questions:
    1. Does it matter where you save Export-QueryToSQLTable.ps1 and do you have to run it from your local every time you run the PS.
    2. Every time you want to update DBAUtil database, do you have to run powershell to collect data (Can it be automated?)
    3. Does the DBAUtil DB keeps growing or can I implement script to keep the DB at certain size.
    4. I was able to run it on one instance and keeps failing when there are more than one instance listed on $runOnInstance.

    Kindly appreciate any help I can get.

  8. Hi JaneDBA,

    First, thank you for giving this a try.

    Sorry about the bug with multiple instances. I had to reset the query counter. The update is now on GitHub. Can you please reload and give it a try?
    https://github.com/SQLJana/Export-QueryToSQLTable/blob/master/Export-QueryToSQLTable.ps1

    Regarding your other questions:
    1. Does it matter where you save Export-QueryToSQLTable.ps1 and do you have to run it from your local every time you run the PS.
    You can place the script(s) on a network share and schedule it from anywhere using SQLAgent or using Windows Scheduler. It need not be from your localhost. Also you can save it to any instance/database.

    2. Every time you want to update DBAUtil database, do you have to run powershell to collect data (Can it be automated?)
    For the space-data collection, it was a one-time analysis for me but if you want to collect periodically, please schedule. I like the method described here: https://www.mssqltips.com/sqlservertip/2233/scheduling-a-sql-server-powershell-script-using-the-task-scheduler/

    3. Does the DBAUtil DB keeps growing or can I implement script to keep the DB at certain size.
    Yes. At the end of the day, you can customize it to fit your needs and if your needs are to summarize or remove older data, please go for it. You can schedule a separate Agent job that does that periodically.

    4. I was able to run it on one instance and keeps failing when there are more than one instance listed on $runOnInstance.
    Hopefully I fixed the error with my latest fix. Can you please give it a try? https://github.com/SQLJana/Export-QueryToSQLTable/blob/master/Export-QueryToSQLTable.ps1

    Thanks

    1. Jana,

      It working great. The only issue I’m faced with on how to correct my environments in the PowerBI dashboard. I wanted my servers to fall under the following environment (Prod, Dev, and Test).
      Thanks.

      1. Yay!! Thanks for confirming that you got it working for yourself.

        In my case, the environment name is in the host name, so it was easy. If you have something similar, you can adjust the formula for “Env”. Alternatively, you could create a SQL Server View off the table with collected data with logic that determines the Env (could be an elaborate CASE statement). Then, repoint the BI report to the view and use that new Env column for the filter.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s