I recently implemented the Performance Datawarehouse on our production server
after running it in our test environment for months with no problems. I am
currently only collecting the 3 basic collection sets. Today 2 'SQL Server
Data Collector' spids got hung and will not rollback. Normally the collection
jobs take about 1.5minutes according to the log. Today though, the
QueryStatisticscollection job was running for over 30minutes and causing the
ActivityMonitor to error. I stopped the collection sets and the associated
jobs. This did nothing to alleviate the problem. The spids that are hung are
reporting a lastwaittype of 'DEADLOCK_ENUM_MUTEX'--they are in a
'killed/rollback' state but are not doing so. This waittype means: Occurs
when the deadlock monitor and sys.dm_os_waiting_tasks try to make sure that
SQL Server is not running multiple deadlock searches at the same time.
I have read the article 'Troubleshooting the Data collector'
(http://technet.microsoft.com/en-us/library/bb677178.aspx) but it has not
really helped. There are no errors in the Datacollector logs. It appears that
the same collection set is running twice when viewing the logs and has not
stopped and the spids are not rolling back. I have searched for any
dcexec.exe processes running on the server as well as any of the
Datacollector SSIS packages running and there are none.
Is there a way for these spids to die gracefully? I can't really stop and
start the sql agent at this time which I suspect may be one way to do this.
These spids appear to be causing issues when trying to use the
ActivityMonitor since it uses the sys.dm_os_waiting_tasks DMV to show data.
Any suggestions would be helpful! Thank you!
||3/26/2010 8:11:01 PM