-
Title
SQL Server agent collects only 200 objects per collection -
Description
SQL Server agent collects only 200 objects. If more objects exists, they will not be collected / monitored. For example the "I/O by File" table display only 200 entries, even there are more files available or the agent only collects job status for 200 jobs but there are many other jobs.
As a result, objects (jobs or files) are not being monitored.
-
Cause
The collection limit is set from 200 to 1000 (max) for SQL Server agents.
Beginning with the 5.7.5.1 database cartridge, the value for the maximum number of rows retrieved was removed from the Agent Status Properties page.
-
Resolution
Attached is the script which can be use to apply to change the collection limits from 200 to 1000 (max) for all installed SQL Server agents.
The script can be applied from Administration | Tool | Script Console | Add (copy and paste the script in the text block) | Run.The variable"collectionName" is set to job collection,but it can be changed to any other collection name. If the variable stays empty, all collections will be changed. Be aware that this will produce additional load on your system.//############ Global Params #################//
/*namespace and agent type*/agentNameSpace = "DB_SQL_Server";agentType = "DB_SQL_Server";/*Max rows per collection*/limit = "1000";/*Specify collection name to change specific collection ; if empty change all collections limit*/collectionName = "DBSS_Agent_Job_List";//############################################//out = new StringBuilder();configService = server.ConfigService;modifyASP(agentType);return out.toString();def modifyASP(agentTypeName) {def cloneNames = configService.getKnownNamesForSharingName(agentNameSpace, agentTypeName, "CollectionDetails");for (cloneName in cloneNames) {def collectionDetails = configService.getSecondaryAsp(agentNameSpace, agentTypeName, "CollectionDetails", cloneName);if (collectionDetails != null) {def rows = collectionDetails.getRows();for (def row : rows) {def colName = row.getString("colName");if (collectionName.isEmpty() || colName.equals(collectionName)) {def oldValue = row.getString("colMaxRows");row.setValueByString("colMaxRows", limit);out.append(String.format("Modify %s ASP for agent type %s. %s MaxRows column changed from %s to %s \n", cloneName, agentTypeName, colName, oldValue, row.getString("colMaxRows")));}}configService.saveConfig(collectionDetails);}else{out.append("Unable to find collection details names: ").append(cloneName).append("\n");}}}DISCLAIMER:The information in the script provided is known to work successfully; however, it has not been officially tested by Quest Quality Control. If any of these instructions are changed and/or incorrectly used, intentionally or unintentionally, this solution becomes unsupported by Quest Support and Development.Quest Support and Development recommend always making a backup of the current Foglight installation and database prior to execution of any scripts that may modify it.Please note that increasing the maxrows beyond the 1000 row level is possible however it can have a detrimental effect on the performance of monitored host, FglAM, and FMS due to the increased amount of data collected. Increasing the amount of data collected (by default) may necessitate additional hardware resource requirements. -
Attachments