Need a powershell script for SQL index Fragmentation
- 8 years ago
The nice thing about PowerShell is that it will tell you exactly where the error lies.
"The index "2" on table "HelpData" is disabled."
So the script doesn't like getting the fragmentation stats on a disabled index. There are two obvious improvements we can make to help with this.
First, I'm not familiar with this HelpData table that's causing the problem. I don't think it's part of any of EV's database schemas. Are your EV databases sharing a SQL server with databases from other applications? Regardless if whether the index is disabled, this script should not be checking for fragmentation on non-EV databases.
Notice that the script as posted just blindly gets all the databases on the SQL instance:
# Connect to the specified instance $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $server # Get the databases for the instance, and iterate through them $dbs = $s.Databases foreach ($db in $dbs) { # Check to make sure the database is not a system database, and is accessible if ($db.IsSystemObject -ne $True -and $db.IsAccessible -eq $True) { ...
It's probably smarter to filter out all the non-EV databases before proceeding:
# Get only the EV databases for the instance, and iterate through them $dbs = $s.Databases | Where-Object {($_.Name -like "EnterpriseVault*") -or ($_.Name -like "EVVS*")}
Second, we can tell it to skip any indexes that are disabled, since we know they will generate an error.
Note that the script is already filtering out XML indexes:
# We don't want to process XML indexes if ($ix.IsXmlIndex -eq $False) { ...
So it's easy enough to add another condition to make sure we skip disabled indexes too:
# We don't want to process XML indexes or disabled indexes if (($ix.IsXmlIndex -eq $False) -and ($ix.IsDisabled -eq $False)) { ...
I hope that helps.
--Chris