After upgrade to Spotlight 10.5.0, two transactions running on a monitored SQL instance are hung. The statement are:
This SPID has been running for over several hours. User has tried killing the SPID and restarting Spotlight but the SPID still exists on the monitored instance.
Spotlight 10.5.0 uses extended events for two purposes: 1) get deadlock events 2) the new Statements Waits collection. Part of this process involves issuing an alter statement on the extended events session and this is when the session can hang indefinitely due to a reported defect with SQL Server.
WORKAROUND 1:
Review and apply batch outlined in below Microsoft KB article 2511963:
“When you use a query to stop an Extended Events session or to alter its state in SQL Server 2008 or in SQL Server 2008 R2, the query stops responding (hangs), and the session cannot be cancelled by using a KILL statement.”
http://support.microsoft.com/kb/2511963
Another referenced article:
https://connect.microsoft.com/SQLServer/feedback/details/383878/extended-event-session-hangs
WORKAROUND 2:
Upgrade Spotlight to 10.5.1 version first from here:
https://support.quest.com/spotlight-on-sql-server-enterprise/10.5.1
Follow below steps to disable the extended events:
1. Locate the file Agent\conf\product\44.xml in the Spotlight Diagnostic Server installation folder (C:\Program Files\Quest Software\Diagnostic Server)
2. Make a backup copy of this file to a location outside of here and then edit this file with a text editor (NotePad)
3. Locate this line in file:
<property dataType="BOOLEAN" description="useextendedevents" displayName="UseExtendedEvents" encryptStyle="NONE" hidden="true" name="useextendedevents">true</property>
4. Change true (useextendedevents) to false
5. Save file.
6. Restart the Spotlight Diagnostic Server.
Note: Above setting can be changed back to true once the extended event patch has been applied
WORKAORUND 3:
In versions 11.0 and above perform below steps:
1. Select Configure | Diagnostic Server | Configure the use of Extended Events option
2. Uncheck 'Use Extended Events' checkbox and hit OK
