Execution timeout expired or Lock request time out period exceeded for SQL Server On-Demand RMI collections (4311079)

Reports and pages based on On-Demand collections are do not display information.

An error message may be listed in the database agent log similar to the following may be displayed.

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.

or

java.lang.RuntimeException: java.lang.RuntimeException: Failed to execute collection [BLOCKING_LIST], reason=[Foglight][SQLServer JDBC Driver][SQLServer]Lock request time out period exceeded.

The following collections may have errors similar to those above:

• Blocking_List
• Buffer_Cache_List
• Config_Suggestions
• CT_Check_Default_Trace
• Database_Index_Density_Vectors
• Database_Index_Details
• Database_Index_Fragmentation_Info
• Database_Index_Histogram
• Database_Index_List
• Database_Tables_List
• Enable_Default_Trace
• Error_Log_Scan
• Indexes_Fragmentation_Report
• Job_Messages
• Kill_Session
• Locks_List
• Log_Shipping
• Replication_Agent_Session_Merge_Articles
• Replication_Agents_Actions_for_Session
• Replication_Agents_Sessions_by_type
• Session_List
• Session_with_oldest_XTP_transaction
• SQL_Server_Reporting_Services_history_execution_per_report
• SQL_Server_Reporting_Services_ConfigurationInfo
• SQL_Server_Reporting_Services_top_active_users
• SQL_Server_Reporting_Services_Reports_by_status
• SQL_Server_Reporting_Services_History_General
• SSIS_Messages
• SSIS_Execution_Steps
• SSIS_Package_Executions
• Top1000_dm_Exec_Query_Stats_Entries
• Top_SQL_Batch_Text
• Top_SQL_PLan
• Top_SQL_Text_log

The on-demand default timeout is 30 seconds.

colCallbacksQueryTimeout=30

Some queries take longer to complete because of the data they retrieve. The 30 second default value isn't long enough for some larger databases.

RESOLUTION

Increase the query timeout for on-demand collections

1. Navigate to the Global View main databases dashboard
2. Select one or more SQL Server agents
3. Click Settings and then Administration in the menu
4. Select the Collections menu item
5. Click the number next to Query timeout for on-demand collections
6. Update the value to 300
7. Click the Set button
8. Click the Save Changes button

or

To increase the Query Timeout for On-demand collections using the Agent Status Properties

1. Navigate to Administration | Agents | Agent Status
2. Place a checkbox next to the SQL Server agent that has the issue 
3. Click on Edit and then Edit Properties in the menu
4. Click on the link to modify the private properties for the agent
5. Scroll down the Collection Details area
6. Click the number in the row Query timeout for on-demand collections
7. Increase the query timeout to 300 seconds
8. Click the Save button
9. Navigate back to Agent Status (Administration | Agents | Agent Status or by clicking the Agent Status link in the top right corner of the page)
10. Click on the Deactivate button
11. Click on the Activate button

NOTE: Both sets accomplish the same change.