March 26, 2010 at 1:55 pm
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 (see attachment) 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!
TW
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply