Need a powershell Script for SQL Fragmentation.Pls help

Question

Post reply

Need a powershell Script for SQL Fragmentation.Pls help

Database admin(DBA)

SSCertifiable

Points: 5373
More actions
January 5, 2017 at 12:59 pm

#339186

Hi All,
I Hope everyone is doing well!!
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..
For your information,i got the powershell script and modified script as per my requirement..But i'm getting the following error message . Can you please help me on this one...
Error: FYI...the error is coming from line:$frg = [Math]::Round($dbfrg,2)
Cannot find an overload for "Round" and the argument count: "2".
Cannot find an overload for "Round" and the argument count: "2".
At line:108 char:2
+ $frg = [Math]::Round($dbfrg,2)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : MethodCountCouldNotFindBest
Below is the script:
$DB_Admin = "C:\DBFrag.htm"
$serverlist = "C:\SQL.txt"
$QueryTimeout = 0
New-Item -ItemType file $DB_Admin -Force
function Invoke-Sqlcmd2 ($server,$database,$query)
{
$conn=new-object System.Data.SqlClient.SQLConnection
$conn.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $Server,$Database
$conn.Open()
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables[0]
}
Function writeHtmlHeader
{
param($fileName)
$date = ( Get-Date ).ToString('yyyy/MM/dd - hh:mm')
Add-Content $fileName "<html>"
Add-Content $fileName "<head>"
Add-Content $fileName "<meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'>"
Add-Content $fileName '<title> Database Fragmentation</title>'
add-content $fileName '<STYLE TYPE="text/css">'
add-content $fileName ""
add-content $fileName "</style>"
Add-Content $fileName "</head>"
Add-Content $fileName "<body>"
add-content $fileName "<table width='100%'>"
add-content $fileName "<tr bgcolor='#CCCCCC'>"
add-content $fileName "<td colspan='7' height='25' align='center'>"
add-content $fileName "<font face='tahoma' color='#003399' size='4'><strong>Database Fragmentation - $date</strong></font>"
add-content $fileName "</td>"
add-content $fileName "</tr>"
add-content $fileName "</table>"
}
Function writeTableHeader
{
param($fileName)
Add-Content $fileName "<tr bgcolor=#CCCCCC>"
Add-Content $fileName "<td width='20%' align='center'>Server Name</td>"
Add-Content $fileName "<td width='20%' align='center'>Database Name</td>"
Add-Content $fileName "<td width='20%' align='center'>Table Name</td>"
Add-Content $fileName "<td width='20%' align='center'>Index Name</td>"
Add-Content $fileName "<td width='20%' align='center'>Average Fragmentation</td>"
Add-Content $fileName "<td width='20%' align='center'>Page Count</td>"
Add-Content $fileName "</tr>"
}
Function writeHtmlFooter
{
param($fileName)
Add-Content $fileName "</body>"
Add-Content $fileName "</html>"
}
Function sendEmail
{ param($from,$to,$subject,$smtphost,$htmlFileName)
$body = Get-Content $htmlFileName
$smtp= New-Object System.Net.Mail.SmtpClient $smtphost
$msg = New-Object System.Net.Mail.MailMessage $from, $to, $subject, $body
$msg.isBodyhtml = $true
$smtp.send($msg)
}
Function writeServiceInfo
{
param($fileName,$machinename,$dbname,$tbname,$ixname,$dbfrg,$dbpc)
Add-Content $filename "<tr>"
Add-Content $filename "<td>$machineName</td>"
Add-Content $filename "<td align=center>$dbname</td>"
Add-Content $filename "<td align=center>$tbname</td>"
Add-Content $filename "<td align=center>$ixname</td>"
$frg = [Math]::Round($dbfrg,2)
If ($frg -gt "30"){
Add-Content $filename "<td bgcolor='#FF0000' align=center>$frg</td>"}
else {
Add-Content $filename "<td bgcolor='#387C44' align=center>$frg</td>" }
If ($dbpc -gt "1000"){
Add-Content $filename "<td bgcolor='#FF0000' align=center>$dbpc</td>"}
else {
Add-Content $filename "<td bgcolor='#387C44' align=center>$dbpc</td>" }
Add-Content $filename "</tr>"
}
# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$v = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | out-null
}
# Handle any errors that occur
Trap {
# Handle the error
$err = $_.Exception
write-host $err.Message
while( $err.InnerException ) {
$err = $err.InnerException
write-output $err.Message
};
# End the script.
break
}
writeHtmlHeader $DB_Admin
foreach ($server in Get-Content $serverlist)
{
Add-Content $DB_Admin"<table width='100%'><tbody>"
Add-Content $DB_Admin"<tr bgcolor='#CCCCCC'>"
Add-Content $DB_Admin"<td width='100%' align='center' colSpan=6><font face='tahoma' color='#003399' size='2'><strong> $server </strong></font></td>"
Add-Content $DB_Admin"</tr>"
writeTableHeader $DB_Admin
# 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) {
# Store the database name for reporting
$dbname = $db.Name
$dbid = [string]$db.ID
$tbs = $db.Tables
foreach ($tb in $tbs) {
# Store the table name for reporting
$tbname = $tb.Name
$tbid = [string]$tb.ID
$ixs = $tb.Indexes
foreach ($ix in $ixs) {
# We don't want to process XML indexes
if ($ix.IsXmlIndex -eq $False) {
# Store the index name for reporting
$ixname = $ix.Name
$ixid = [string]$ix.ID
# Get the Fragmentation and page count information
$q = @"
select avg_fragmentation_in_percent, page_count
from sys.dm_db_index_physical_stats($dbid, $tbid, $ixid, NULL, NULL)
"@
$res = invoke-sqlcmd2 $server $dbname $q
$frval = $res.avg_fragmentation_in_percent
$pgcnt = $res.page_count
writeServiceinfo $DB_Admin $server $dbname $tbname $ixname $frval $pgcnt
}
}
}
}
}
Add-Content $DB_Admin"</table>"
}
Writehtmlfooter $EVDatabase
$date = ( Get-Date ).ToString('yyyy/MM/dd - hh:mm')
sendEmail xxxxxxxx
SQL server DBA

Viewing post 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply