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}

Enjoy it !