Daily Health Check Script with Powershell

A SQL Server  Daily Health Check Script with Powershell  is very effective in checking databases. I implement this health check  script early in the morning , generating a report and emailed to the DBA team. The Powershell script iterates through a list of SQL Server Instances and executes the sql script.

Data is critical to many business processes. Continuous access to the data is one of the key objectives of a DBA. A build up of many small problems can lead to a SQL Server outage. It’s important to fix small problems in a timely fashion. Part of fixing the problem, is to define a clear set of policies, and check regularly that the policies are met. Check SQL Server - Powershell and SQL Error Logs 

 The purpose of the Report is to check on various organisational  policy conditions . It’s different to the  Monitoring alert system, which triggers an alert when a situation arises. It assists in creating housekeeping tasks . For example , if it’s a policy to not allow  isAutoShrink , then this sql statement returns the isAutoShrink status of every database.

 Another condition maybe to check if the Data and Log files are maintained on separate drives. This SQL Script will check and return a flag to indicate the Data and Log Files are on the same drive.

Customise the sql code to satisfy the goals you’re trying to achieve in managing the SQL Server Inventory.

An example of the report

Server Daily Health Check

set-location E:\health\SQL_Server

$isodate=Get-Date -format s 
$isodate=$isodate -replace(":","")
$basepath=(Get-Location -PSProvider FileSystem).ProviderPath


$instancepath=$basepath + "\config\instances.txt"
$outputfile="\logs\sql_server_health_daily_check_" + $isodate + ".html"
$outputfilefull = $basepath + $outputfile



$emailFrom = "[email protected]"
$emailTo = "[email protected]"
$subject = "SQL Server Health Daily Check"
$body = "SQL Server Health Daily Check"
$smtpServer = "mysmtp"
$filePath = ""


#invoke stylesheet
. .\modules\stylesheet.ps1

#intro smtp function 
. .\modules\smtp.ps1


$dt = new-object "System.Data.DataTable"
foreach ($instance in get-content $instancepath)
{
$instance
$cn = new-object System.Data.SqlClient.SqlConnection "server=$instance;database=msdb;Integrated Security=sspi"
$cn.Open()
$sql = $cn.CreateCommand()
$sql.CommandText = "select @@servername as ServerName,
SERVERPROPERTY('ProductVersion') AS Version,
DB_NAME(mf.database_id) as [db_name],
suser_sname(da.owner_sid) as [db_owner],
type_desc,
CONVERT(sysname,DatabasePropertyEx(DB_NAME(mf.database_id),'Recovery')) AS [RecoveryMode],
CONVERT(sysname,DatabasePropertyEx(DB_NAME(mf.database_id),'IsAutoShrink')) AS [isAutoShrink],
mf.is_percent_growth AS [isPercentageGrowth],
mf.state_desc AS [db_state],
last_backup = (SELECT max(bus.backup_finish_date)
FROM msdb.dbo.backupset bus
INNER JOIN msdb.dbo.backupmediafamily bume ON bus.media_set_id = bume.media_set_id
WHERE bus.database_name = DB_NAME(mf.database_id))
,checkDBLocation = CASE WHEN (
select count(*) from sys.master_files as m1 
where  m1.type_desc IN ('LOG')  and mf.type_desc IN ('ROWS')
AND substring(m1.physical_name,1,1) = substring(mf.physical_name,1,1)
AND m1.database_id = mf.database_id
) > 0 THEN '1'
ELSE '0'
END,
substring(physical_name,1,1)
from sys.master_files as mf
INNER JOIN sys.databases as da ON da.database_id = mf.database_id
"
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()
 
}

$dt | select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | ConvertTo-Html -head $reportstyle -body "SQL Server Daily Health Check" | Set-Content $outputfilefull  

$filepath = $outputfilefull  
#Call smtp Function 
sendEmail $emailFrom $emailTo $subject $body $smtpServer $filePath

To reference the  "smtp.ps1"  - create a new powershell file and place the function found on : Send email from Powershell with attachment .   You don't need to keep the file in the modules folder , you can place the file in the same folder and reference. You are passing certain values into this function , including the reference to the attachment , which is added to the email

To reference the "stylesheet.ps1"    - create a new powershell file with that name , and place in code similar to:
$reportstyle="<!--
TABLE{border-width: 2px;border-style: solid}"
$style=$style + "TD{border-width: 2px;border-style: solid}"
$style=$style + "
-->"

Alternatively place the code directly in the script - such as on Powershell to HTML - SQL Server DBA.

Placing the code directly in the script , has the disadvantage of not being reusable , by other scripts

Read More

SQL Server – Send email using Powershell - SQL Server DBA

SQL Server - Powershell and SQL Error Logs

SQL Server DBA Top 10 automation tasks

SQL Server - Top 10 DBA mistakes