Here is an update on my research of one known issue that sounds similar. The KB is a few weeks away, so I figure I can kill the suspense and let you in on the basics.
The SSIS service can hold handles on all DTExec.exe processes on your system for the purpose of incrementing and decrementing perfmon counters, and for showing running packages in your Object Explorer in SQL Server Management Studio.
Even after DTExec.exe and DTSHost.exe have exited, the handle held by the SSIS Service may not be immediately released. We found this is due to a problem in the .Net framework API which SSIS service relies upon to monitor the exited events from the various runtime processes. In my research, even though the process has exited, the Process object still exists in kernel mode, but I see very few kernel resources consumed by the zombie process (very little desktop heap, no threads, no user mode memory), so there is low risk that the extra zombie processes would cause problematic symptoms.
Few traits from my observations:
1. I could not see the DTExec.exe processes in Task Manager for this scenario described, therefore if you see yours in Task Manager its a different problem.
2. I could see the DTExec.exe processes using Sysinternals Processes Explorer tool, and the corresponding handles from MsDtsSrvr.exe when checking to show handles in the lower pane, and also setting preferences to show unnamed handles.
3. I could see the DTExec.exe processes in a kernel debug, and list the handles from within MsDtsSrvr.exe user mode process by doing !handle 0 f Process
4. If you use a lot of loops in your packages, to invoke child packages from a parent package for example, you are more likely to observe this problem, due to the sheer volume of SSIS runtime processes made on your machine. If you run an SSIS job very frequently, or lots of jobs, perhaps that lead up to 33000 handles being held.
Does it hurt anything?
In general, I imagine this is a relatively benign situation. If you feel you do have problematic symptoms due to the number of processes lingering, I imagine the first resource to become limited might be Desktop Heap. You can run a downloadable Microsoft tool called DHeapMon on your server to confirm if you have limited Desktop Heap resources for a certain service account (resources are fixed per service account, and also total across all service accounts is capped). If needed, measure with that tool in a .bat file loop once every 1 minute while saving the output into a .txt output file, and run this .bat leading up to the time when the lingering DTExec.exe processes are present to make sure your resources are not running out. There are many KB articles explaining how to adjust Desktop Heap allocations should you find one of the service accounts (if you use proxy accounts for various SQL Agent SSIS jobs) needs more desktop heap, or if there are too many separate accounts being used, and the total desktop heap is exhausting the maximum allowed.
See also http://support.microsoft.com/default.aspx/kb/949296
Download for DHeapMon http://www.microsoft.com/downloads/details.aspx?familyid=5CFC9B74-97AA-4510-B4B9-B2DC98C8ED8B&displaylang=en
Quick Workaround proposal to try, and also proof if the problem is the same root cause or not... Restarting SSIS MsDtsSrvr.exe service periodically should clear any excess DTExec.exe processes that linger due to this problem. If the number of processes gets out of control on some periodic basis, restarting the MsDtsSrvr.exe process will also free the handles to the processes and clear the extras.
Note if you cycle the SSIS Service, the SSIS Service can be set to leave running packages alone, or to intentionally kill them when the service is cycled. The flag which controls that is an XML tag <StopExecutingPackagesOnShutdown>false</StopExecutingPackagesOnShutdown> within the file C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml
There is info on that in the KB article: http://support.microsoft.com/kb/904798
We found the root cause of the Zombie DTExec.exe problem is in the .Net 2.0 framework implementation of System.Diagnostics.Process class. These managed process objects create user mode handles to the kernel processes and are supposed to free them when Dispose is called to free the managed object. By the way, we think SSIS is calling the Dispose method correctly. The problem happens only when the method EnableRaisingEvents=True is used by the SSIS Service, since in that case an extra handle to the OS process is taken. Later when SSIS calls EnableRaisingEvents=False to release monitoring of the DTExec processes, the handle should be freed, but .Net library does not do so immediately. This particular lingering handle will only last temporarily until the Managed object is freed, which happens whenever the garbage collection for the various .Net generations runs (timers control that and new activity ages the objects into less frequently cleaned generations). Therefore, I expect the problem of the handle and the extra DTExec.exe process it points to, to only last temporarily until .Net garbage collection runs.
The problem in the .Net framework System.Diagnostics.Process object was already fixed for the .Net 4.0 release, but has not yet been fixed for the 2.0 release that SSIS 2008 uses. I don't yet know if our customers need this fix, because I don't yet know if the observation has ever lead to any symptomatic problem. If you have a special request for a hotfix, please contact me via CSS and we can discuss the scenario further to understand the impact to your business and go from there.
Thanks, Jason H
Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance
