Powershell Script To Check SQL Server Database Status

While working with Powershell I have begun to move away from using the SSMS GUI to get basic details on SQL instances. Management Studio is an excellent tool but I find myself keeping Powershell open and using it for quick queries to answer the basics. One of the basics is getting a list of Databases with their status, sizes and more in 1 query.

I tried using the “Get-SqlDatabase” one and it’s “WAY” slow for me for some reason. It also does not bring back exactly what I want either.

Get-SqlDatabase -ServerInstance $sqlinstance "MyServer\SQLInstance"

Instead I went back to the Invoke-Sqlcmd command and a simple SQL statement, I already have written, that can be run on any SQL instance. This statement will return the database details as well as the sizes and details on the data files too.

$sqlinstance = "MyServer\SQLInstance"

$query = "SELECT
	db_name(db.database_id) AS [DB],
	db.[state_desc],
	db.create_date,
	db.[compatibility_level],
	db.recovery_model_desc,
	mf.[name] AS [Internal_Name],
	mf.[physical_name],
	mf.[type_desc],
	CAST(mf.size * 8.0/1024/1024 AS DECIMAL(8,2)) AS [Size_GB],
	CASE
		WHEN CAST(mf.max_size AS nvarchar(10)) = '-1' THEN 'UNLIMTED'
		ELSE CAST(CAST(mf.max_size * 8.0/1024/1024 AS DECIMAL(8,2)) AS nvarchar(50))
	END AS [Max_Size_GB],
	CASE
		WHEN CAST(is_percent_growth AS nvarchar(10)) = '1' THEN CAST(mf.growth AS nvarchar(10))+'%'
		WHEN CAST(mf.growth AS nvarchar(10)) = 0 THEN NULL
		ELSE CAST(CAST(mf.growth * 8.0/1024 AS INT) AS nvarchar(10))
		END AS [Growth_GB_MB]
FROM sys.master_files mf 
INNER JOIN sys.databases db 
	ON db.database_id = mf.database_id"

Invoke-Sqlcmd -ServerInstance $sqlinstance -Query $query | Out-Gridview  ## format-table

Out-Gridview and Format-Table

Just to be ultra-efficient I decided to comment out the Format-Table commandlet and use the Gridviewer instead. Th e”Out-Gridview” will take your results (db tales com) and provide a small popup window that allows you to view, filter and sort the result set.

Try it out and see what you think, i’ve been using the GridView almost exclusively now.