02-08-2017 10:06 AM
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: ( [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : MethodCountCouldNotFindBest
Thanks & Regards,
Kumar DBA
Solved! Go to Solution.
02-10-2017 09:59 AM
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
02-09-2017 06:39 AM
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 }
02-09-2017 07:44 AM
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
02-10-2017 09:32 AM
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
02-10-2017 09:42 AM
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,
02-10-2017 09:59 AM
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
02-10-2017 10:30 AM
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.
-----
02-14-2017 01:47 PM
Thank you Chris for all your help.
I'm getting the following error message while exuecting the script and i mean the script continously but in middle i can see these errors.Can you please suggest me on this. Waiting for your response on this.
Add-Content : Stream was not readable.
At line:103 char:2
+ Add-Content $filename "<td align=center>$dbname</td>"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (D:\Test\EVDatabase.htm:String) [Add-Content], ArgumentException
+ FullyQualifiedErrorId : GetContentWriterArgumentError,Microsoft.PowerShell.Commands.AddContentCommand
Thanks in advance!!
Best Regards,
Kumar DBA
02-15-2017 05:48 AM
This seems like a limitation in the way the script writes its HTML file, using the Add-Content cmdlet. You can see others' frustration with this cmdlet around the web: here and here, for example.
I don't know what might be causing it, but really this error has nothing to do with EV or fragmentation and everything to do with writing text to a file using PowerShell. There are lots of options for how to do this, like Set-Content, Out-File, and even .NET methods in the System.IO.File class, which you can call from PowerShell using a type accelerator. I am disinclined to claim one way is the right way, but using Out-File has always worked for me when I need to write a script that outputs a log file. Using this I have never encountered this type of error, so anecdotally my experience suggests it is preferable to Add-Content.
So for example, this:
Add-Content $filename "<tr>"
becomes this:
"<tr>" | Out-File $filename -Append
Best of luck!
--Chris
02-15-2017 09:33 PM
Hi Chris,
Thank you so much for all your help..Really great i got the output but I can see the following error messages in powershell console,as you mentioned above i have to replace below code with below lines which im getting the errors.
"<tr>" | Out-File $filename -Append
Ex; "<tr> " | Out-File $filename "<td>$machineName</td>" like this please confirm once.
Requesting you to please help me,I' still learning and not the most efficient coder in powershell, so I appreciate if possible can you please kindly modify the script at below lines,it would be great help for me, i will be waiting for your positive response on this.
Add-Content : Stream was not readable.
At line:101 char:2
+ Add-Content $filename "<tr>"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (D:\Test\DBA.htmtring) [Add-Content], ArgumentException
+ FullyQualifiedErrorId : GetContentWriterArgumentError,Microsoft.PowerShell.Commands.AddContentCommand
Add-Content : Stream was not readable.
At line:102 char:2
+ Add-Content $filename "<td>$machineName</td>"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (D:\Test\DBA.htmtring) [Add-Content], ArgumentException
+ FullyQualifiedErrorId : GetContentWriterArgumentError,Microsoft.PowerShell.Commands.AddContentCommand
Add-Content : Stream was not readable.
At line:103 char:2
+ Add-Content $filename "<td align=center>$dbname</td>"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (D:\Test\DBA.htmtring) [Add-Content], ArgumentException
+ FullyQualifiedErrorId : GetContentWriterArgumentError,Microsoft.PowerShell.Commands.AddContentCommand
Add-Content : Stream was not readable.
At line:115 char:5
+ Add-Content $filename "<td bgcolor='#387C44' align=center>$dbpc</td>" }
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (D:\Test\DBA.htmtring) [Add-Content], ArgumentException
+ FullyQualifiedErrorId : GetContentWriterArgumentError,Microsoft.PowerShell.Commands.AddContentCommand
Add-Content : Stream was not readable.
At line:117 char:3
+ Add-Content $filename "</tr>"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (D:\Test\DBA.htmtring) [Add-Content], ArgumentException
+ FullyQualifiedErrorId : GetContentWriterArgumentError,Microsoft.PowerShell.Commands.AddContentCommand
Add-Content : Stream was not readable.
At line:101 char:2
+ Add-Content $filename "<tr>"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (D:\Test\DBA.htmtring) [Add-Content], ArgumentException
+ FullyQualifiedErrorId : GetContentWriterArgumentError,Microsoft.PowerShell.Commands.AddContentCommand
Add-Content : Stream was not readable.
At line:102 char:2
+ Add-Content $filename "<td>$machineName</td>"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (D:\Test\DBA.htmtring) [Add-Content], ArgumentException
+ FullyQualifiedErrorId : GetContentWriterArgumentError,Microsoft.PowerShell.Commands.AddContentCommand
Add-Content : Stream was not readable.
At line:103 char:2
+ Add-Content $filename "<td align=center>$dbname</td>"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (D:\Test\DBA.htmtring) [Add-Content], ArgumentException
+ FullyQualifiedErrorId : GetContentWriterArgumentError,Microsoft.PowerShell.Commands.AddContentCommand
Add-Content : Stream was not readable.
At line:104 char:2
+ Add-Content $filename "<td align=center>$tbname</td>"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (D:\Test\DBA.htmtring) [Add-Content], ArgumentException
+ FullyQualifiedErrorId : GetContentWriterArgumentError,Microsoft.PowerShell.Commands.AddContentCommand
Add-Content : Stream was not readable.
At line:105 char:2
+ Add-Content $filename "<td align=center>$ixname</td>"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (D:\Test\DBA.htmtring) [Add-Content], ArgumentException
+ FullyQualifiedErrorId : GetContentWriterArgumentError,Microsoft.PowerShell.Commands.AddContentCommand
Add-Content : Stream was not readable.
At line:104 char:2
+ Add-Content $filename "<td align=center>$tbname</td>"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (D:\Test\DBA.htmtring) [Add-Content], ArgumentException
+ FullyQualifiedErrorId : GetContentWriterArgumentError,Microsoft.PowerShell.Commands.AddContentCommand
Add-Content : Stream was not readable.
At line:105 char:2
+ Add-Content $filename "<td align=center>$ixname</td>"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (D:\Test\DBA.htmtring) [Add-Content], ArgumentException
+ FullyQualifiedErrorId : GetContentWriterArgumentError,Microsoft.PowerShell.Commands.AddContentCommand
Add-Content : Stream was not readable.
At line:110 char:5
+ Add-Content $filename "<td bgcolor='#387C44' align=center>$frg</td>" }
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (D:\Test\DBA.htmtring) [Add-Content], ArgumentException
+ FullyQualifiedErrorId : GetContentWriterArgumentError,Microsoft.PowerShell.Commands.AddContentCommand
Add-Content : Stream was not readable.
At line:115 char:5
+ Add-Content $filename "<td bgcolor='#387C44' align=center>$dbpc</td>" }
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (D:\Test\DBA.htmtring) [Add-Content], ArgumentException
+ FullyQualifiedErrorId : GetContentWriterArgumentError,Microsoft.PowerShell.Commands.AddContentCommand
Add-Content : Stream was not readable.
At line:117 char:3
+ Add-Content $filename "</tr>"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (D:\Test\DBA.htmtring) [Add-Content], ArgumentException
+ FullyQualifiedErrorId : GetContentWriterArgumentError,Microsoft.PowerShell.Commands.AddContentCommand
Add-Content : Stream was not readable.
At line:101 char:2
+ Add-Content $filename "<tr>"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (D:\Test\DBA.htmtring) [Add-Content], ArgumentException
+ FullyQualifiedErrorId : GetContentWriterArgumentError,Microsoft.PowerShell.Commands.AddContentCommand
Add-Content : Stream was not readable.
At line:102 char:2
+ Add-Content $filename "<td>$machineName</td>"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (D:\Test\DBA.htmtring) [Add-Content], ArgumentException
+ FullyQualifiedErrorId : GetContentWriterArgumentError,Microsoft.PowerShell.Commands.AddContentCommand
Add-Content : Stream was not readable.
At line:103 char:2
+ Add-Content $filename "<td align=center>$dbname</td>"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (D:\Test\DBA.htmtring) [Add-Content], ArgumentException
+ FullyQualifiedErrorId : GetContentWriterArgumentError,Microsoft.PowerShell.Commands.AddContentCommand
Thanks in advance!!
Best Regards,
Kumar DBA
02-16-2017 05:48 AM
Hi Chris,
Can you please help me on above issue.
Thanks in advance!!
Best Regards,
Kumar DBA
02-16-2017 06:41 AM - edited 02-16-2017 01:09 PM
Here is the script file with all the changes recommended in this thread.
Changes to AKL's original script:
1) Changed Add-Content cmdlets for writing report file to Out-File, as Add-Content was generating "Stream was not readable" errors.
2) Added condition to exclude non-EV databases (line 155).
3) Added condition to exclude disabled indexes (line 172).
4) Added logic to handle multiple rows of results from sys.dm_db_index_physical_stats.
02-16-2017 11:59 AM
Thank you chris for your quick response,
Now i'm getting different error message as shown below, I believe we need to use InputObject instead of inputfile.can you please confirm once or if not pls suggest what changes need to be done here.I appreciate your help !!,Please help.
-a--- 2/16/2017 2:55 PM 0 DBA.htm
A parameter cannot be found that matches parameter name 'InputFile'.
Out-File : A parameter cannot be found that matches parameter name 'InputFile'.
At line:145 char:16
+ Out-File $DBA -InputFile "<table width='100%'><tbody>"
+ ~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [Out-File], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.PowerShell.Commands.OutFileCommand
Thanks & Regards,
Kumar DBA
02-16-2017 12:17 PM
Yeah sorry I must have had a mental lapse while typing that part. It's supposed to be -InputObject, like it is everywhere else. I changed it and replaced the attachment on the prior post.
--Chris
02-16-2017 12:34 PM
Thank you Chris for your quick response and i have done all changes as you suggested but getting following error message and i tried with below code but no luck..requesting please check it once from your end.
$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 }
Mode LastWriteTime Length Name
---- ------------- ------ ----
-a--- 2/16/2017 3:30 PM 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: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : MethodCountCouldNotFindBest
Thanks in advance!!
02-16-2017 03:23 PM
Hi Chris,
Whenever you get a time,please look into this and help me out.
Thanks in advance!!
02-17-2017 01:52 PM - edited 02-17-2017 02:17 PM
Thank you Chris for all your help and now it seems like working fine but getting the results different manner .in other words is it possible to modify the script to remove the fragmentation which is in fragmentation as below 30% since I need only more than 30% fragmentation.Please find attached screenshot for more information.
I.e, the results should be as 30% more than in EVdatabase.html.,remaining below 30% can be removed from the out put.(only red colour)
Waiting for your positive response on this.
Thanks in advance!!
Best Regards,
Kumar
02-20-2017 09:20 AM
Hi Chris,
I believe we can use this below command greater than or equal for above issue.Please suggest me if its correct.
If ($frg -ge "30"){
or else $q = @"
select avg_fragmentation_in_percent, page_count
from sys.dm_db_index_physical_stats($dbid, $tbid, $ixid, NULL, NULL) WHERE avg_fragmentation_in_percent >= 30
OR page_count > 1000'
I appriciate your help !!
Thanks in advance!!
Best Regards,
02-20-2017 05:59 PM
Hi Chris,
I have fixed the issue now.Thank you so much for all your help
Best Regards,
09-27-2017 06:44 AM
Hi,
I am able to get the .htm file on the location inside the server but still the script error out as below
Cannot bind argument to parameter 'FilePath' because it is null.
Out-File : Cannot bind argument to parameter 'FilePath' because it is null.
+ Out-File $fileName -InputObject "</body>" -Append
+ ~~~~~~~~~
+ CategoryInfo : InvalidData: (:) [Out-File], ParentContainsError
RecordException
+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,M
icrosoft.PowerShell.Commands.OutFileCommand
I think i am failing to load the below part of the script
Function writeHtmlFooter
{
param($fileName)
Out-File $fileName -InputObject "</body>" -Append
Out-File $fileName -InputObject "</html>" -Append
}
----
Can you assist what I am missing since this script appears too much valuable and highly appreciate your help
Thanks and Regards
Eben