Encountered error running "Index Fragmentation" report (4301081)

• "Too many databases requested. For SQL versions lower than 2012, can only produce fragmentation report on one database at a time"
• "Failed to generate the report. If this issue persists please contact Quest Support"

ERROR   [runReportTask-0] script.system:dbwc_mssql_reports_index20fragmentation20report.callGetIndexFragmentationReportRMI - Failed to call getIndexFragmentationReport in RMI, reason: RemoteException occurred in server thread; nested exception is: 
com.quest.qsi.fason.framework.connections.common.exceptions.JDBCServerException: Failed to run INDEX_FRAGMENTATION_REPORT collection. Failed to execute query on instance [INSTANCENAME], user account [DOMAIN\USERNAME]. Reason : [[Foglight][SQLServer JDBC Driver]Execution timeout expired.

ERROR   [runReportTask-31] script.system:dbwc_mssql_reports_index20fragmentation20report.callGetIndexFragmentationReportRMI - Failed to call getIndexFragmentationReport in RMI, reason: RemoteException occurred in server thread; nested exception is: 
com.quest.qsi.fason.framework.connections.common.exceptions.JDBCServerException: Failed to run INDEX_FRAGMENTATION_REPORT collection. Failed to execute query on instance [INSTANCENAME], user account [DOMAIN\USERNAME]. Reason : [[Foglight][SQLServer JDBC Driver][SQLServer]Lock request time out period exceeded.

CAUSE 1

CAUSE 2

RESOLUTION 1

Add a new configuration to the SQL Server database agent for "INDEX_FRAGMENTATION_REPORT" to increase the timeout value for the on-demand collection

1. Navigate to Administration | Agents | Agent Status
2. Select the Database agent
3. Click the Edit Properties button
4. Click the link to Modify the private properties for this agent.
5. Scroll down to Collection Details
6. Click the Edit button next to On demand Collection Details
7. Click the Add Row button
8. Add the Collection Name INDEX_FRAGMENTATION_REPORT 
9. Add the Display Name INDEX FRAGMENTATION_REPORT
10. Add the Description INDEX_FRAGMENTATION_REPORT
11. Add the Query Timeout of 14400 (or higher)
12. Click the Save Changes button

Please note that beginning in the 5.9.4.20 and higher releases of the SQL Server cartridge, the INDEX_FRAGMENTATION_REPORT collection name is present. Steps 7-10 can be skipped in the steps above.

The following script can be run in the FMS Script Console to list the current settings of the INDEX_FRAGMENTATION_REPORT timeout value for all SQL Server agents in the FMS. The first row listed is the default collection for new SQL Server agents. 

//############ Global Params #################//
/*
namespace and agent type
*/
agentNameSpace = "DB_SQL_Server";
agentType = "DB_SQL_Server";
/*
Max rows per collection
*/
limit = "14400";
/*
Specify collection name to change specific collection ; if empty change all collections limit
*/
collectionName = "INDEX_FRAGMENTATION_REPORT";
//############################################//
out = new StringBuilder();
configService = server.ConfigService;
modifyASP(agentType);
return out.toString();
def modifyASP(agentTypeName) {
def cloneNames = configService.getKnownNamesForSharingName(agentNameSpace, agentTypeName, "colOnDemandCollectionDetails");
for (cloneName in cloneNames) {
def collectionDetails = configService.getSecondaryAsp(agentNameSpace, agentTypeName, "colOnDemandCollectionDetails", cloneName); 
if (collectionDetails != null) {
def rows = collectionDetails.getRows();
for (def row : rows) {
def colName = row.getString("colName");
if (collectionName.isEmpty() || colName.equals(collectionName)) {
out.append(String.format("%s ASP column for agent type %s set on %s to %s \n", cloneName, agentTypeName, colName, row.getString("colTimeout")));
}
}
}else{
out.append("Unable to find collection details names: ").append(cloneName).append("\n");
}
}
}

The following script can be run from the Script Console in the FMS to update the INDEX_FRAGMENTATION_REPORT timeout setting for all SQL Server agents.

//############ Global Params #################//
/*
namespace and agent type
*/
agentNameSpace = "DB_SQL_Server";
agentType = "DB_SQL_Server";
/*
Max rows per collection
*/
limit = "14400";
/*
Specify collection name to change specific collection ; if empty change all collections limit
*/
collectionName = "INDEX_FRAGMENTATION_REPORT";
//############################################//
out = new StringBuilder();
configService = server.ConfigService;
modifyASP(agentType);
return out.toString();
def modifyASP(agentTypeName) {
def cloneNames = configService.getKnownNamesForSharingName(agentNameSpace, agentTypeName, "colOnDemandCollectionDetails");
for (cloneName in cloneNames) {
def collectionDetails = configService.getSecondaryAsp(agentNameSpace, agentTypeName, "colOnDemandCollectionDetails", 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("colTimeout");
row.setValueByString("colTimeout", limit);
out.append(String.format("Modify %s ASP for agent type %s. %s Timeout column changed from %s to %s \n", cloneName, agentTypeName, colName, oldValue, row.getString("colTimeout")));
}
}
configService.saveConfig(collectionDetails);
}else{
out.append("Unable to find collection details names: ").append(cloneName).append("\n");
}
}
}

RESOLUTION 3

When the report creation fails with an "Execution timeout" message, the following can be used to increase the timeout.

1. From the Agent Status dashboard and select the problematic SQL Server agent
2. Click on Edit Properties to access the Agent Status Properties
3. Click on Modify the private properties for this agent.
4. Change the "Query timeout for on-demand collections" from 30 to 300 (seconds)
5. Save changes

If the report is still taking too long to complete, attempt to increase the report parameters "Minimal Partition Size" (E.g. from default "10" to "100") and "Minimal partition scan or update operations" (E.g. from the default "5" to "10") until the query completes in a reasonable amount of time.

RESOLUTION 4