SQL Server : Powershell Commands for Basic Health Check

Here are the few powershell commands to do basic health check of MS SQL Server Instance:

1) SQL Database Status

invoke-sqlcmd -query "SELECT SERVERPROPERTY('ServerName') as SQLInstance,name as DBName,
state_desc as State from sys.databases;" -ServerInstance  | Format-table –AutoSize;

2) SQL Database File Size

invoke-sqlcmd -query "DECLARE @command varchar(1000); SELECT @command = 
'USE ? SELECT SERVERPROPERTY(''ServerName'') as SQLInstance, name as DB_FileName,
convert(varchar(100), FileName) as FileName, cast(Size as decimal(15,2))*8/1024 as ''SizeMB'',
FILEPROPERTY (name, ''spaceused'')*8/1024 as ''UsedMB'', Convert(decimal(10,2),convert(decimal(15,2),
(FILEPROPERTY (name, ''spaceused'')))/Size*100) as PercentFull,filegroup_name(groupid) as ''FileGroup'',
FILEPROPERTY (name, ''IsLogFile'') as ''IsLogFile''  FROM dbo.sysfiles order by IsLogFile'
EXEC sp_MSforeachdb @command" -ServerInstance  | Format-table –AutoSize;

3) SQL Instance Blocking & Session details

invoke-sqlcmd -query "SELECT SERVERPROPERTY('ServerName') as SQLInstance,s.session_id,
r.blocking_session_id,db_name(r.database_id) as [Database],r.wait_time, s.login_name,
s.cpu_time/60000 as[CPU_Time(mins)],s.memory_usage, r.[status], r.percent_complete,r.nest_level, [Text]
from sys.dm_exec_sessions as s 
inner join 
sys.dm_exec_requests as r 
on s.session_id = r.session_id cross apply sys.dm_exec_sql_text (sql_handle) 
where s.status = 'running' order by s.cpu_time desc;" -ServerInstance  | Format-table –AutoSize;

Here are the few powershell commands to get the health check at Windows Level:

1) Drive Free Space

Get-WmiObject -Class win32_Volume | Select-object Name, Label, 
@{Name="Size(GB)";Expression={[decimal]("{0:N0}" -f($_.capacity/1gb))}}, 
@{Name="Free Space(GB)";Expression={[decimal]("{0:N0}" -f($_.freespace/1gb))}}, 
@{Name="Free (%)";Expression={"{0,6:P0}" -f(($_.freespace/1gb) / ($_.capacity/1gb))}} 
| Format-table –AutoSize;

2) Top 5 CPU Utilization

Get-Process | Sort-Object -Property CPUPercent -Descending | Select-Object -First 10 
| Select-Object -Property  @{ Name = "TimeStamp"; Expression = {Get-Date}}, Name, CPU, 
@{Name = "CPUPercent"; Expression = {$TotalSec = (New-TimeSpan -Start $_.StartTime).TotalSeconds; 
[Math]::Round( ($_.CPU * 100 / $TotalSec), 2)}}, Description | Format-table –AutoSize;

3) Top 5 Memory Utilization

Get-Wmiobject -Class WIN32_PROCESS | Sort-Object -Property ws -Descending | Select-Object -First 5 
| Select-Object -Property @{ Name = "TimeStamp"; Expression = {Get-Date}}, processname, 
@{Name="Mem Usage(MB)";Expression={[math]::round($_.ws / 1mb)}},
@{Name="ProcessID";Expression={[String]$_.ProcessID}},
@{Name="UserID";Expression={$_.getowner().user}} | Format-table –AutoSize;