Forum Discussion

Chintu's avatar
Chintu
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 ...
  • ChrisLangevin's avatar
    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's avatar
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's avatar
    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's avatar
      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

      • Chintu's avatar
        Chintu
        Level 3
        8 years ago

        Thank you Chris for your quick help.

        I have modified below mentioned 155 and 172 lines as you suggested, if so I will execute the script against all the DB’s which is more than 1500 DB’s.

        Are your EV databases sharing a SQL server with databases from other applications? – currently we are planning to implement this script non Ev DB’s, I believe below modification should work for all Databases if EV or non EV.

        Please confirm from your end as well.

         

        # Get only the EV databases for the instance, and iterate through them

        $dbs = $s.Databases | Where-Object {($_.Name -like "EnterpriseVault*") -or ($_.Name -like "EVVS*")}

        # We don't want to process XML indexes or disabled indexes

                                        if (($ix.IsXmlIndex -eq $False) -and ($ix.IsDisabled -eq $False)) {

         

         

        Best Regards.

        -----