SQL Server agent collects only 200 objects per collection (4294976)

RESOLUTION 1

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.

The following script can be used to increate the max rows value for a since SQL Server agent.

• set the value of rows to increase the value to in the maxRows value in line 1
• collectionNames contains the collections to increase, each in double quotes
• Replace MYAGENTNAME with the SQL Server agent name
• The script can be applied from Administration | Tool | Script Console | Add (copy and paste the script in the text block) | Run.

maxRows = "1500";
agentService = server.get("AgentService");
configService = server.get("ConfigService");
collectionNames =  ["DBSS_Logical_Disks","DBSS_File","DBSS_Virtual_File_Statistics"];
	

	
AGENTNAME="MYAGENTNAME";
agent = getAgentByName(AGENTNAME);

if (agent != null){
	modifyCollectionDetail(agent);
} else {
println("no agent found :" + AGENTNAME);
}
return true;




def getAgentByName(agent_name) {  
  agents = agentService.findByName(agent_name);

  if ((agents != null) && (agents.size() > 0)) {
println("found the agent " + agents.get(0));
    return agents.get(0);
  } else {
    return null;
  }
}

def modifyCollectionDetail(agent) {
        def primary = configService.getAgentInstancePrimaryAsp(agent.getAgentNamespace(), agent.getTypeId(), agent.getId());
		def cloneName= primary.getString("CollectionDetails");
		println("cloneName " + cloneName);
		
	  def collectionDetailsASP = configService.getSecondaryAsp(agent.getAgentNamespace(), agent.getTypeId(),"CollectionDetails",cloneName );
	
	if (collectionDetailsASP == null){
		println("null asp");
		return;
	}
	
	for (secRow in collectionDetailsASP.getRows()) {
		def collectionName = secRow.getString("colName");
		//println("name " + collectionName);
		
		for(collName in  collectionNames) {
		 if(collectionName.equals(collName)) {
		def orgValue= secRow.getString("colMaxRows");
				  secRow.setValueByString("colMaxRows", maxRows);
				println("found the collection:"+collName+", original value:"+ orgValue+ ", current value:"+maxRows);

			}
		}
		//configService.saveConfig(collectionDetailsASP);
	}
	configService.saveConfig(collectionDetailsASP);
}

def getAgent(agentName) {
                def agents = agentService.findByName(agentName)
                def agent = agents[0];
                return agent;
}

The script below be used 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.
• Quest R&D does not recommend significantly increasing maxRows size for all agents on a FglAM for several different collections concurrently as this can affect the memory usage on the FglAM server.

//############ 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");
}
}
}