SQL Server – Daily Health Check Script with Powershell

08 October,2012 by Jack Vamvas

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

 

ServerName

Version

db_name

db_owner

type_desc

RecoveryMode

isAutoShrink

isPercentageGrowth

db_state

last_backup

checkDBLocation

Column1

Srv1\Inst1

9.00.5000.00

master

sa

ROWS

SIMPLE

0

True

ONLINE

06/10/2012 02:09:33

1

E

Srv1\Inst1

9.00.5000.00

master

sa

LOG

SIMPLE

0

True

ONLINE

06/10/2012 02:09:33

0

E

Srv1\Inst1

9.00.5000.00

tempdb

sa

ROWS

SIMPLE

0

True

ONLINE

 

0

G

 

 

 

 

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 = "jack@sqlserver-dba.com"
$emailTo = "jack@sqlserver-dba.com"
$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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server – Daily Health Check Script with Powershell


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer