Description

I have prepared this Powershell script to use it with SCOM. Of course it can be used as a standalone script too. I am basically creating a Rule in SCOM to trigger the below script which logs an event into the Application Event Log. Later on I am collecting the Event Logs with an “Event Collection Rule”. Alert Generating Rules or Monitors can be created, if you want to get notified about the Fragmentation status.

This Powershell Script can be downloaded from Technet Script Gallery too.
https://gallery.technet.microsoft.com/scriptcenter/SQL-DB-Fragmentation-with-92dad445

Powershell Script

# CheckSQLDBFragmentation.ps1
# Version 1.0
# Date: 9/01/2018
# Author: Cengiz KUSKAYA (www.Kuskaya.Info)
# Description: Check SQL DB Fragmentation and log an event into the Application Event Log

$ProviderList = (Get-WinEvent -ListLog Application).ProviderNames
if ($ProviderList -notcontains 'SQLDBFragmentation')
{New-eventlog -logname Application -source "SQLDBFragmentation"}

$regpath='HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server'
$sqlinstances=@((Get-ItemProperty -Path $regpath -Name InstalledInstances).InstalledInstances)
$servers =@()
if( $sqlinstances -eq 'MSSQLSERVER')
{
$servers=$env:COMPUTERNAME
}
else
{
foreach($sqlinstance in $sqlinstances)
{
$servers+=$env:COMPUTERNAME + "\" + $sqlinstance
}
}
$FileName='C:\log\DBFragInfo.log'
if (Test-Path 'C:\log')
{
If(Test-Path $FileName)
{
Clear-Content $FileName
}
}
else
{
Write-Host "Folder not exist on machine. Creating..."

New-Item -Force -ItemType directory -Path 'C:\log'
}
foreach ($server in $servers)
{
Write-Host "Fetching Database information on $server........"
$dbname=sqlcmd -E -S $server -Q "select name from sys.databases where name not in ('master','tempdb','model','msdb') ;" | select -Skip 2
$dbname = $dbname | select -First ($dbname.Count -2)
Write-Host $dbname.count "Database found on $server........"
foreach($db in $dbname)
{
$sql=sqlcmd -E -S $server -d $db -Q "select case when count(Object_name(object_id)) > 0 then 'Critical' else 'Normal' end as Status,count(Object_name(object_id)) as IndexCount,'$db' as DBName from sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,NULL) where avg_fragmentation_in_percent > 5;"
Add-Content -Path $FileName -Value $sql
}
}

$LogFile = "C:\Log\DBFragInfo.log"
$Text = "Critical"
$DATA = Get-ChildItem -Path $LogFile | ForEach-Object { (Get-Content $_.FullName | Select-String -Pattern $Text -allmatches) }

If (Get-ChildItem -Path $LogFile | ForEach-Object { (Get-Content $_.FullName | Select-String -Pattern $Text -allmatches) })
{Write-eventlog -logname Application -source SQLDBFragmentation -eventID 9999 -entrytype Error -message "Over 5% SQL DB Fragmentation detected on the following databases :`n`nStatus IndexCount DBName`n$DATA `n`nFor detailled table names regarding the fragmentations please run below query on the related databases : SELECT CASE WHEN SUM(F) > 0 THEN 'Critical' ELSE 'Normal' END 'Status', DB_NAME(database_id) 'DBNAME', TableName,SUM(F) 'FragmentedIndexCount' FROM (SELECT database_id, OBJECT_NAME(object_id) TableName,CASE WHEN avg_fragmentation_in_percent > 40 THEN 1 ELSE 0 END F FROM sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,NULL)) S GROUP BY TableName, database_id HAVING SUM(F) != 0; " -EA SilentlyContinue}
else
{Write-eventlog -logname Application -source SQLDBFragmentation -eventID 8888 -entrytype Information -message "No SQL DB Fragmentation detected on any database table." -EA SilentlyContinue}

Good luck !