Here are the few powershell commands to do basic health check of MS SQL Server Instance:
1) SQL Database Status
2) SQL Database File Size
3) SQL Instance Blocking & Session details
Here are the few powershell commands to get the health check at Windows Level:
1) Drive Free Space
2) Top 5 CPU Utilization
3) Top 5 Memory Utilization
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;
