Thank you Fredrik and rob for your quick help.my request is 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 thi sresults over the email(MSSQL Server DB)
$dbfrg value is float and its like 99.9 like that…
If possible can you please review my code,Not sure which part i have to remove it.
$DBA = “C:\test\DBA_test.htm”
$serverlist = “C:\servers.txt”
$QueryTimeout = 0
New-Item -ItemType file $DBA_test -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
Write-host “connection information:”
$connection #List connection information
Write-host “connect to database successful.”
$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’)
}
Function writeTableHeader
{
param($fileName)
Add-Content $fileName ""
Add-Content $fileName "Server Name"
Add-Content $fileName "Database Name"
Add-Content $fileName "Table Name"
Add-Content $fileName "Index Name"
Add-Content $fileName "Average Fragmentation"
Add-Content $fileName "Page Count"
Add-Content $fileName ""
}
Function writeHtmlFooter
{
param($fileName)
}
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 “”
Add-Content $filename “$machineName”
Add-Content $filename “$dbname”
Add-Content $filename “$tbname”
Add-Content $filename “$ixname”
$frg = [Math]::Round($dbfrg,2)
If ($frg -gt “30”){
Add-Content $filename “$frg”}
else {
Add-Content $filename “$frg” }
If ($dbpc -gt "1000"){
Add-Content $filename "$dbpc"}
else {
Add-Content $filename "$dbpc" }
Add-Content $filename ""
}
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 $DBA_test
foreach ($server in Get-Content $serverlist)
{
Add-Content $DBA_Test “”
Add-Content $DBA_test “”
Add-Content $DBA_test “ $server ”
Add-Content $DBA_test “”
writeTableHeader $DBA_test
# 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 $DBA_test $server $dbname $tbname $ixname $frval $pgcnt
}
}
}
}
}
Add-Content $DBA_test “”
}
Writehtmlfooter $DBA
$date = ( Get-Date ).ToString(‘yyyy/MM/dd - hh:mm’)
sendEmail xxxxxxx