SQL Server Agent Jobs Not Completing

Problem
Random jobs and SSIS packages started failing, generating the following error message: “DTExec.exe - Application Error : The application failed to initialize properly (0xc0000142). Click on OK to terminate the application.”  
 
Symtoms
SQL Server 2005 Maintenance Jobs that run continuously. The job starts, but never complete. This occurs on all jobs that run a SSIS package. Jobs that only run T-SQL run fine. The only message found is an informational Windows system log that appears every time the job runs:

Event Source: Application Popup
Event ID: 26
Description: Application popup: DTExec.exe - Application Error : The application failed to initialize properly (0xc0000142). Click on OK to terminate the application.

Workaround
This problem went away after I consolidated many SQL Server Replication Jobs. I am not sure why, but I suspect it had something to do with the non-interactive desktop heap (KB 824422).

After searching on the Internet for some time, I came across MS KB 824422 (http://support.microsoft.com/kb/824422) This was close to my problem, but not exactly. The KB did make me think about how many processes where running concurrently. The following week I was getting several replication job errors. We did have a couple hundred SQL Server replication jobs run continuously. Consolidating the replication jobs had been a project I tasked myself with a month earlier. I decided to start that project and see what happens. Sure enough, the errors went away. Keep in mind, I did NOT make any registry changes as the KB suggested. I just reduced the number of concurrent SQL Jobs.

I call this a workaround because I cannot explain exactly how this fixed the problem. Here is script I use to help me consolitate replication articles into fewer publications.

Read my forum post on SQL Server Central. If anyone has experience with this, I would appreciate your feedback.

Query Execution Performance

This is a useful script to test query execution time and view the IO statistics. This is especially useful for developers to create efficient queries. This utility simply measures the execution time for your query. It also returns IO Statistics, such as scan count, logical reads and more. Just paste in your query and execute.

Download the file here.

I recommend that developers closely analyze not just the execution duration, but also the IO statistics and the execution plan.

To learn more about these topics check out the following resources:

• SQL Server Books Online: Analyzing a Query
• SQL Server Books Online: Displaying Graphical Execution Plans
• SQL Server Books Online: SET STATISTICS IO

Replication Publication Articles

This is a useful script when planning where to place a new publication. I like grouping common articles into one publication to make for easier administration . I also like knowing the row count for those articles.

DECLARESearchStr NVARCHAR(100)
SET @SearchStr = '%Orders%' ;

SELECT p.name AS PublicationName ,
       a.name AS ArticalName ,
       t.row_count AS RowCnt
FROM   dbo.syspublications p
       INNER JOIN dbo.sysarticles a
           ON p.pubid = a.pubid
       LEFT JOIN sys.dm_db_partition_stats t
           ON a.objid = t.object_id
WHERE t.index_id < 2
       AND p.name LIKE @SearchStr
       AND a.name LIKE @SearchStr
ORDER BY p.name , a.name ;

Download the file here.

This is a useful script when planning where to place a new publication. I like grouping common articles into one publication to make for easier administration. I also like knowing the row count for those articles. I developed this simple script to help me consolidate replication articles into fewer publications. In this case, there was one publication per article, and there were a lot of articles (yikes). All publications where push subscriptions. This made the production database do all the work.

In the Begining

I have been a SQL Server DBA and Developer for nearly ten years. In that time I have amassed a lot of scattered resources across the internet. I decided to create this blog to centralize these resources in one place. This is not just for my benefit, but hopefully you will find it useful too. This blog will not just contain links to useful resources are articles, but also real-world solutions that I have encountered.

Since this blog is designed as a resource for SQL Server DBAs, I encourage everyone to participate. There are no stupid questions, especially mine, so regardless of your experience please share you feedback and opinions.

Jon