Issue: We have SQL server replication is configured for too many databases in single set of publisher and subscriber.
Reason: Error 0xc0000142 indicates there is a issue with desktop heap. We are not left with much free heap.
- Increase the heap size by modifying registry followed with server reboot
- a) HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management\SessionViewSize (increase the value from 48 to 64, for example)
- b) HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Session Manager\SubSystems\Windows (increase the third value of SharedSection by 256 kilobytes, for example)
- Change the replication agents from running continuously to running on a scheduled basis.
- Change the server location on which the replication agents are running. May be changing from push to pull will help.
- Use separate accounts for the replication agents created for different databases.
Note: If job runs under different proxy accounts. For each proxy account, the non-interactive desktop heap for that proxy account will be allocated. For example, the third value of the SharedSection parameter is 512. If you use a proxy account to start a job, the 512 KB desktop heap will be allocated, even if the job itself uses only 10 KB of the desktop heap.