"DBSS_Backup_Locations For input string: ###,###,###,###" errors in SQL Server agent log (4227047)

The amount of free space for backups cannot be collected. Errors similar to the following are found in the SQL Server agent log.

ERROR [AGENTNAME-lowPriorityPool-1-[DBSS_Backup_Locations][]]
com.quest.qsi.fason.sqlserver.collection.processor.database.backup.DBSSBackupFreeDiskProcessor - Failed to get free space of disk [\\DISKNAME\] . Reason: For input string: "###,###,###,###" java.lang.NumberFormatException: For input string: "###,###,###,###"

(where # represents an integer number from 0 through 9) 

The regular expression in the hidden ASP (Agent Status Property) bcNumberDelimiter value was not \D. This prevented the commands from being dropped and the value being parsed into all digits.

• In the December 2014 SQL Server 5.7.1 cartridge the bcNumberDelimiter value was changed from (\.|\,) to a default value \\D as part of a correction for defect SSDB-13278.
• In the April 2015 SQL Server 5.7.5.1 cartridge the bcNumberDelimiter regular expression was also changed from \\D to a default value of \D as part of a correction for defect SSDB-13917.

Database agents created prior to April 2015 may continue to have an incorrect regular expression value in the bcNumberDelimiter ASP field.

The following groovy script can be run from the Script Console to list all of the current values for the bcNumberDelimiter hidden ASP value in the FMS for all SQL Server agents

def out = new StringBuilder();
def agentService = server.get("AgentService"); def configService = server.get("ConfigService"); def agents = agentService.findByAdapterAndType("FglAM", "DB_SQL_Server"); for (agent in agents) {
   def primaryASP = configService.getAgentInstancePrimaryAsp(agent.getAgentNamespace(), agent.getTypeId(), agent.getId());
   if (primaryASP) {
      out.append(String.format("%s, %s: bcNumberDelimiter=%s\n", agent.getRemoteClientId(), agent.getName(), primaryASP.getString("bcNumberDelimiter")));
   }
}

return out.toString();

The following groovy script can be run from the Script Console to change the bcNumberDelimiter hidden ASP value to the correct \D value for a single SQL Server agent. Please replace the word AGENTNAME with the name of the SQL Server agent.

(Note: the script uses \\D in the insert because of the escape character required by groovy. The listing script (above) can be used to confirm the inserted value.) 

configService = server.get("ConfigService");
agentService = server.get("AgentService");

def modifyAgentPropertiesPrimary(agent) {		
	def primary = configService.getAgentInstancePrimaryAsp("DB_SQL_Server", "DB_SQL_Server", agent.getId());
	primary.setValueByString("bcNumberDelimiter", "\\D");
	configService.saveConfig(primary);		
}


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

  if ((agents != null) && (agents.size() > 0)) {
    return agents.get(0);
  } else {
    return null;
  }
}


AGENTNAME = "AGENTNAMEHERE";

agent = getAgentByName(AGENTNAME);
buff = new StringBuffer();

if (agent != null){
	modifyAgentPropertiesPrimary(agent);	
	buff.append( "Agent '" + AGENTNAME + "' ASP has been changed; \n" );	
} else {
   buff.append( "Agent '" + AGENTNAME + "' not found ; \n" );	
}

return buff.toString();

The following groovy script can be run from the Script Console to change the bcNumberDelimiter hidden ASP value to the correct \D value for all SQL Server agents.

(Note: the script uses \\D in the insert because of the escape character required by groovy. The listing script (above) can be used to confirm the inserted value.) 

srvConfig 			= server["ConfigService"];
srvAgent 		  	= server["AgentService"];

namespace = "DB_SQL_Server"
agentType = "DB_SQL_Server"

def updateASP(primaryASP) {
	primaryASP.setValueByString("bcNumberDelimiter" , "\\D");
	srvConfig.saveConfig(primaryASP);
}

primaryASP = srvConfig.getAgentTypePrimaryAsp(namespace, agentType);
updateASP(primaryASP);
def allAgents = srvAgent.findByAdapterAndType("FglAM", "DB_SQL_Server");
allAgents.each {agent ->
	primaryASP = srvConfig.getAgentInstancePrimaryAsp(namespace, agentType, agent.getId());
	updateASP(primaryASP);
}

return true;