Forum Discussion

Level 3

8 years ago

Need a powershell script for SQL index Fragmentation

Hello All,

Need Powershell script to find the Fragmentation details greater than 30% for a list of servers and the results needs to be exported in excel. We need this results over the email in HTML or CSV format.

But I have referred to below link but i tried to execute it that getting following error message,tried different menthods but no luck,can someone provide the script that would very helpful to me.https://vox.veritas.com/t5/Downloads/SQL-Database-Fragmentation-Level-Email-Reporting/ta-p/811611

Error message:

Mode LastWriteTime Length Name
---- ------------- ------ ----
-a--- 2/8/2017 2:29 AM 0 EVDatabase.htm
Cannot find an overload for "Round" and the argument count: "2".
Cannot find an overload for "Round" and the argument count: "2".
At line:106 char:2
+ $frg = [Math]::Round($dbfrg,2)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: ( Smiley Happy [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : MethodCountCouldNotFindBest

Thanks & Regards,

Kumar DBA

ChrisLangevin
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

ChrisLangevin
Level 6
8 years ago
Kumar,
The problem is that the query in lines 179-180 is written expecting a single result.

select avg_fragmentation_in_percent, page_count from sys.dm_db_index_physical_stats($dbid, $tbid, $ixid, NULL, NULL)
I tested this in a lab (substituting the proper values for $dbid, $tbid, and $ixid of course), and it returns two rows.

PS C:\Users\Administrator> $res avg_fragmentation_in_percent page_count ---------------------------- ---------- 0.29382957884427 2042 0 9780
This results in this line...

$frval = $res.avg_fragmentation_in_percent
...storing both rows' avg_fragmentation_in_percent values in $frval, and PowerShell automatically makes $frval an array instead of a decimal.

Then when $frval is passed into the writeServiceinfo function, that function expects it to be a single number instead of an array, and it tries to round that number. But of course the Math.Round method doesn't have any overload that accepts an array as the first argument, and so you get the error.

Easy solution: don't assume you're getting only one row back from that query. Replace lines 182-185 with this:
$res = @(invoke-sqlcmd2 $server $dbname $q) foreach ($row in $res) { $frval = $row.avg_fragmentation_in_percent $pgcnt = $row.page_count writeServiceinfo $EVDatabase $server $dbname $tbname $ixname $frval $pgcnt }
- Chintu
  Level 3
  8 years ago
  THank you Chris your quick response and help.
  But I'm getting folloiwng error message while executing the script as you suggested.I'm using powershell version 4.
  Mode                LastWriteTime     Length Name
  ----                -------------     ------ ----
  -a---         2/10/2017   7:56 AM          0 EVDatabase.htm
  The index "2" on table "HelpData" is disabled.
  Exception calling "Fill" with "1" argument(s): "The index "2" on table "HelpData" is disabled."
  At D:\Test\EVSQLFragmentationReport_1.ps1:16 char:2
  +     [void]$da.fill($ds)
  +     ~~~~~~~~~~~~~~~~~~~
      + CategoryInfo          : NotSpecified: (:) [], ParentContainsErrorRecordException
      + FullyQualifiedErrorId : SqlException
  
  Please suggest me how should i proceed further on this.
  
  Thanks & Regards,
  CHintu
  - ChrisLangevin
    Level 6
    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
CConsult
Moderator
8 years ago
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.alloc_unit_type_desc,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc
- Chintu
  Level 3
  8 years ago
  Thank you(CConsult) for your response,But I have that script and need a script for if the SQL server Database fragmentation is more than 30% which using servers.txt and need a get email these results in HTML or CSV format.
  I have referred below article,while executing this script in powershell version i'm getting the following error message. and the issue here MATH.round. if you have any other script can you please provide that would really help me to proceed further.
  Please help me....https://vox.veritas.com/t5/Downloads/SQL-Database-Fragmentation-Level-Email-Reporting/ta-p/811611
  
  Mode                LastWriteTime     Length Name
  ----                -------------     ------ ----
  -a---         2/10/2017   7:56 AM          0 EVDatabase.htm
  The index "2" on table "HelpData" is disabled.
  Exception calling "Fill" with "1" argument(s): "The index "2" on table "HelpData" is disabled."
  At D:\TestEVSQLFragmentationReport_1.ps1:16 char:2
  +     [void]$da.fill($ds)
  +     ~~~~~~~~~~~~~~~~~~~
      + CategoryInfo          : NotSpecified: (:) [], ParentContainsErrorRecordException
      + FullyQualifiedErrorId : SqlException
  
  Best regards,

Forum Discussion

Need a powershell script for SQL index Fragmentation

Related Content

Scripting and PowerShell integration

PST export using powershell

Powershell script for exclude lists.

Running PowerShell script immediately after BE job finishes

EV Server 3rd party backup Powershell script

Recent Discussions

Recorded EV FSA presentation

Elasticsearch JVM heap size over 90%

Is it possible to query the index database?

Latest support matrix has dropped support for 14.x on Server 2012 R2.

Veritas Enterprise Vault performance issue