-
Title
Seeing alarms: "Collection '' failed : Timeout expired" for multiple categories of data collection -
Description
There are multiple categories of data collection failure for specific monitored instances with the "Collection failed : Timeout expired" message. For example:
Collection 'WaitEventsRaw' failed : Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. :The wait operation timed out [0xFFFFFFFE]
-
Cause
Spotlight uses Extended Events to query SQL Server for Wait Events, Workload Analysis and Deadlocks. If the issue occurs on specific SQL Server 2008 and SQL Server 2008 R2 instances, it is most likely related to SQL Server Extended Events defect as described by the following articles:
The effect of this Microsoft bug on Spotlight is that you'll see these alarm messages in Spotlight coming from your monitored SQL Server 2008 (R2). -
Resolution
We cannot do anything on the Spotlight side as this is an SQL Server Extended Event issue on SQL Server 2008 (R2). Two ways to temporary address the issue:
WORKAROUND 1:
It seems to only happen on SQL Server 2008 (R2) and it seems to happen randomly and gradually. If it happens again, maybe just wait for the next scheduled SQL Server 2008 (R2) reboot to clear it – assuming your organization has this policy.
WORKAROUND 2:
Disable Extended Events, but no data will be collected for Wait Events or Workload Analysis. To disable Extended Events on the SQL Server connection level (only available in Spotlight version 11.6.1 and higher):
1. Right-click on SQL Server connection | Properties.2. Un-check the box "Use Extended Events".3. Click OK.
To disable Extended Events globally for all SQL Server connections:
1. On main menu, go to Configure tab | Diagnostic Server.2. On new window, click the link "Use of Extended Events".3. Un-check "Use Extended Events" box and click OK. -
Additional Information
The error message means that the session making the Extended Event call is locked. Please note that this lock is only for that one session making the Extended Event call and ‘not’ the entire SQL Server Instance. Based on article 1, it seems once the session locks up, it can only be cleared with a reboot of the SQL Instance. Article 1 mentions cumulative patches but in article 2, it is unclear if the cumulative patch does work based on the comments.
Also, it seems the SQL Server Extended Event issue may not happen right away but is gradual. We are not doing anything out of the ordinary with extended events. You can review the queries here in the following default directories:
- C:\Program Files\Dell\Diagnostic Server\Agent\conf\Package\sqlserver_spotlight\2008\WorkloadAnalysis.sql
- C:\Program Files\Dell\Diagnostic Server\Agent\conf\Package\sqlserver_spotlight\2008\WaitEvents
- C:\Program Files\Dell\Diagnostic Server\Agent\conf\Package\sqlserver_spotlight\2008\Deadlocks