Enhancing Your Troubleshooting Toolset with PowerShell - SQL Server 2012 Internals and Troubleshooting - page 398

foreach($index in $table.Indexes)
{
$fragmentation = $index.EnumFragmentation()
$averageFragmentation = $fragmentation.Rows[0].AverageFragmentation
if($averageFragmentation -lt .05)
{
continue
}
if($averageFragmentation -ge .05 -and $averageFragmentation -lt .3)
{
$index.Reorganize()
continue
}
$index.Rebuild()
}
}
Managing Disk Space Utilization of Backups
Working with the i le system is very straightforward with PowerShell. In the following example,
I have created a small script that removes transaction log backups that are older than two
days, differential backups that are older than eight days, and full backups that are older than
91 days. By splitting the Get-ChildItem query into three queries based on i le extension and last
write time, it was simply a matter of setting the number of days to subtract from the current date to
dei ne the retention period for each backup type (code i le: PS_DeleteOldBackups01.PS1 ):
Get-ChildItem .\Backups -include *.trn -recurse '
| Where-Object { $_.lastwritetime -lt (Get-Date).AddDays(-2) } '
| Remove-Item
Get-ChildItem .\Backups -include *.dif -recurse '
| Where-Object { $_.lastwritetime -lt (Get-Date).AddDays(-8) } '
| Remove-Item
Get-ChildItem .\Backups -include *.bak -recurse '
| Where-Object { $_.lastwritetime -lt (Get-Date).AddDays(-91) } '
| Remove-Item
Extracting DDL Using SMO
A misplaced index or a poorly dei ned table dei nition are two examples of how changing the DDL
of a database can have heavy performance implications. With this in mind, I created the following
script, which can be scheduled to run once a day and will extract all the DDL objects from the data-
base and store them on disk against the day that they were extracted. If poor performance is identi-
i ed, it's easy to compare the i les between days to identify whether any DDL changes caused the
performance of the database to degrade.