I am not able to rerun my script and get the following error:
A maximum of 10 output pairs can be monitored per script monitor. You currently have 10 pairs defined and are trying to generate more, bringing the total above the allowed 10. Processing will not start until existing statistics are disabled or removed.
How can I accomplish this? Can I simply delete the statistics at the top of the script and re-generate? We are only going to run this once a day so it is not super important that we save the data.
Script:
$connection= new-object system.data.sqlclient.sqlconnection #Set new object to connect to sql database
$Connection.ConnectionString ="server=servername;database=db;uid=user;pwd=password" # Connectiongstring setting for local machine database with window authentication
$connection.open() #Connecting successful
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand #setting object to use sql commands
$SqlQuery = "SELECT TOP (10) i.name AS IndexName, ROUND(avg_fragmentation_in_percent, 2) AS [Fragmentation %]
FROM sys.dm_db_index_physical_stats(6, NULL, NULL, NULL, NULL) AS s INNER JOIN sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id
where (s.database_id = 6) AND (i.name IS NOT NULL) AND (OBJECTPROPERTY(s.object_id, 'IsMsShipped') = 0)
ORDER BY [Fragmentation %] DESC"
$SqlCmd.CommandText = $SqlQuery # get query
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter #
$SqlAdapter.SelectCommand = $SqlCmd #
$SqlCmd.Connection = $connection
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()
$i = 1
foreach ($Row in $DataSet.Tables[0].Rows)
{
$Message="Message."+$i+": "+$($Row[0])
$Statistic="Statistic."+$i+": "+$($Row[1])
Write-Host $Message
Write-Host $Statistic
$i++
}
exit(0)
