January 20, 2015 at 6:55 am
Need Powershell script to find the Fragmentation details greater than 25% for a list of servers and the results needs to be exported in excel. Please help.
January 20, 2015 at 9:28 am
Is there any particular reason it has to be powershell?
Its easy enough to read from a txt file with a list of Servers and call a query against the dmv and return the results in a batch script using sqlcmd.
For example, This is roughly what you would need in a batch file.
for /f %%a IN (ServerList.txt) do sqlcmd -S %%a -d master -E -i dmv_query.sql -h -1 >> Results.csv
Csv files can be opened natively in excel.
I do have something similar for powershell but I can't share it due to a lot of sensitive details in the code.
MCITP SQL 2005, MCSA SQL 2012
January 21, 2015 at 3:00 am
I got the power shell query. but it is better if we can alter the query by adding some columns which are important.
Also if you could share your power shell query that would be a great help, especially for SQL 2000
$EVDatabase = "D:\Fragmentation\Fragmentation.htm"
$serverlist = "D:\Fragmentation\Serv.txt"
$QueryTimeout = 0
New-Item -ItemType file $EVDatabase -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 "td {"
add-content $fileName "font-family: Tahoma;"
add-content $fileName "font-size: 11px;"
add-content $fileName "border-top: 1px solid #999999;"
add-content $fileName "border-right: 1px solid #999999;"
add-content $fileName "border-bottom: 1px solid #999999;"
add-content $fileName "border-left: 1px solid #999999;"
add-content $fileName "padding-top: 0px;"
add-content $fileName "padding-right: 0px;"
add-content $fileName "padding-bottom: 0px;"
add-content $fileName "padding-left: 0px;"
add-content $fileName "}"
add-content $fileName "body {"
add-content $fileName "margin-left: 5px;"
add-content $fileName "margin-top: 5px;"
add-content $fileName "margin-right: 0px;"
add-content $fileName "margin-bottom: 10px;"
add-content $fileName ""
add-content $fileName "table {"
add-content $fileName "border: thin solid #000000;"
add-content $fileName "}"
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 Details - $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 $EVdatabase
foreach ($server in Get-Content $serverlist)
{
Add-Content $EVdatabase "<table width='100%'><tbody>"
Add-Content $EVDatabase "<tr bgcolor='#CCCCCC'>"
Add-Content $EVDatabase "<td width='100%' align='center' colSpan=6><font face='tahoma' color='#003399' size='2'><strong> $server </strong></font></td>"
Add-Content $EVDatabase "</tr>"
writeTableHeader $EVDatabase
# 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 $EVDatabase $server $dbname $tbname $ixname $frval $pgcnt
}
}
}
}
}
Add-Content $EVDatabase "</table>"
}
Writehtmlfooter $EVDatabase
$date = ( Get-Date ).ToString('yyyy/MM/dd - hh:mm')
January 21, 2015 at 3:09 am
For SQL2000 you'll need to write a T-SQL query that calls DBCC SHOWCONTIG & probably do some modifications to get it into the same format as data returned from the DMV in SQL2005+
January 5, 2017 at 1:01 pm
I have a same requirement to find the SQL fragmentation details greater than 30% for a list of servers and the results needs to be exported in excel..
Can anyone pls provide the script..
Thanks in advance!!
SQL server DBA
January 5, 2017 at 1:03 pm
Hi Senthil,
I'm getting the following error message as per my requirement( i mean i have modified the script)
Error: .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
Can you please help me on this...
Thanks in advance!!
Thanks,
DBA
SQL server DBA
January 6, 2017 at 4:06 am
Here is another option in powershell as I had been asked over IM to share my PS code. Whilst I can't share what we use due to sensitivity issues this may help.
First of all define a text file called serverlist.txt, in this list all servers you want to target like so:
SQLServerA
SQLServerB
SQLServerC\NamedInstance
Then save the following SQL as a file and place it in the same folder as the Serverlist.txt file
SET NOCOUNT ON
GO
IF OBJECT_ID('tempdb..FragmentedIndexes') IS NOT NULL
DROP TABLE tempdb..FragmentedIndexes
CREATE TABLE tempdb..FragmentedIndexes (
ServerName sysname,
DatabaseName sysname,
TableName sysname NULL,
IndexName sysname,
indexid int,
page_count bigint,
index_type_desc nvarchar(100),
avg_fragmentation_in_percent float,
fragment_count int
)
INSERT INTO tempdb..FragmentedIndexes
exec sp_msforeachdb '
SELECT@@servername as ''ServerName'',
''?'' AS DatabaseName,
OBJECT_NAME(A.[object_id]) as ''TableName'',
COALESCE(B.[name], ''N\A'') as ''IndexName'',
A.[index_id],
A.[page_count],
A.[index_type_desc],
A.[avg_fragmentation_in_percent],
A.[fragment_count]
FROM [?].sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,''LIMITED'') A
INNER JOIN [?].sys.indexes B
ON A.[object_id] = B.[object_id]
and A.index_id = B.index_id
WHERE avg_fragmentation_in_percent >= 30
order by avg_fragmentation_in_percent, page_count desc'
SELECT * FROM Tempdb..FragmentedIndexes
IF OBJECT_ID('tempdb..FragmentedIndexes') IS NOT NULL
DROP TABLE tempdb..FragmentedIndexes
GO
Save the following powerhsell as a .ps1 file in the same folder as the above two files:
cls
$serverList=$args[0]
$sqlFile=$args[1]
# Add snapins \ modules if required
$snapinAdded = Get-PSSnapin | Select-String SqlServerCmdletSnapin100
if (!$snapinAdded)
{
Add-PSSnapin SqlServerCmdletSnapin100
}
$snapinAdded = Get-PSSnapin | Select-String SqlServerProviderSnapin100
if (!$snapinAdded)
{
Add-PSSnapin SqlServerProviderSnapin100
}
#Load server list file to variable object
$file = Get-Content $serverList
#Get the current directory to a variable
$scriptDir = Split-Path -Path $MyInvocation.MyCommand.Definition -Parent
#Create a variable to hold our results
$results=@()
#For each line in file
Foreach ($server in $file)
{
Try
{
Write "Starting Processing $server"
#Define output file name
$resultfile = $scriptDir + "\" + "FragmentedIndexes" + $server + ".csv"
#Connect to the SQL instance and run the sql script
Invoke-Sqlcmd -InputFile $scriptDir\$sqlFile -ServerInstance $server | Export-Csv $resultfile -NoTypeInformation
Write "Completed Processing $server"
}
Catch
{
$ErrorMessage = $_.Exception.Message
Write "Failed Processing $server"
Write "Error Message: $ErrorMessage"
}
}
Now to run open the powershell command line navigate to the folder you saved your scrips and execute as:
./<PS Script Name> <serverlist.txt> <sqlscript.sql>
MCITP SQL 2005, MCSA SQL 2012
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply