One of my customers recently ran into a very strange issue. We had many SSIS Packages that were running as a SQL Server Job.
But, off late we saw all the Jobs that were running a SSIS Package failed. All other SQL Jobs were executing without any issues. All those SSIS Jobs were failing with the Same Error message.
Executed as user: <Account>. The step did not generate any output. The return value was unknown. The process exit code was -1073741502. The step failed.
It was really a generic error message with no information with it. Everything ran fine if we ran it from BIDS or Command Prompt. The issue arises only when we ran it as a Job.
We created a new package with a simple execute SQL task and even that failed as a Job. So, we enabled the SSIS Logging for the Package. Unfortunately, it wasn’t even creating the Log file. So thought that it was not even reaching the point of Execution and it was failing even before that.
We ran a Process Monitor to check if that could give us any lead. But Even Procmon didn’t have any information on it. We checked the Event Viewer Logs. But no Error messages in that as well.
Suddenly, I saw an Informational Message being logged in the Event Viewer.
Application Popup: DTExec.exe- Application Error: The application was unable to start correctly(0xc0000142). Click ok to close the application.
As Expected it was failing at the start stage.
So finally after some research, found that this issue could occur if the Desktop Heap goes out of Memory. Every Desktop has a single Desktop heap associated with it. The desktop heap stores certain user interface objects, such as windows, menus, and hooks. When an application requires a user interface object, functions within user32.dll are called to allocate those objects. If an application does not depend on user32.dll, it does not consume desktop heap. So if the Desktop heap is Out of Memory, then Process won’t start.
This behavior is kind of explained in http://support.microsoft.com/kb/824422/en-us
The Resolution is to basically increase the Desktop Heap Size.
HOW TO CONGIGURE DESKTOP HEAP
Backup the registry refer kb (322756 How to back up and restore the registry in Windows )
1. At a command prompt, type REGEDT32.EXE to start Registry Editor.
2. In Registry Editor, locate the following registry key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\SubSystems
3. In the right pane of Registry Editor, click Windows.
4. On the Edit menu, click Modify.
5. In the Edit String dialog box, locate the SharedSection parameter string in the Value data box, and then specify a larger value for the SharedSection parameter.
Note The SharedSection parameter specifies the system and desktop by using the following format, where <xxxx> defines the maximum size of the system-wide heap (in kilobytes), <yyyy> defines the size of the per desktop heap, and <zzzz> is the size of the desktop heap for each desktop that is associated with a non-interactive Windows station:
SharedSection= <xxxx>, <yyyy> , <zzzz>
Change the following value from:
SharedSection=1024, 3072, 1536 (We are making this as double.)
6. Restart the machine.
And Yaaayyy! It worked!!!
Happy Learning!! J
Author : Mahaja(MSFT), Suport engineer, Microsoft
Reviewed by : Snehadeep(MSFT), SQL Developer Technical Lead, Microsoft