Tips and Tricks for:  SQL Server

The Problem:   A Stalled SQL Server Agent cmdexec job.

After moving a job to a new server I found one of the jobs still running in the morning when I returned to work.  It was at a cmdexe step and hadn't used any CPU.  I also found two messages in the Event Logs.  The first one was in the Application Even Log:

The description for Event ID ( 0 ) in Source ( .NET Runtime ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: .NET Runtime version 1.1.4322.2300- xxxxxx.exe - Common Language Runtime Debugging Services: Application has generated an exception that could not be handled.

The other message was in the System Even log:

Solution: Proper error handling in .Net Programs

The immediate solution was a permission problem.  The program didn't have permission to write to it's log file.  But the real problem was that when it couldn't write to the log it threw an unhandled event.  The .net's runtime unhandled event handler popped up a message and was stuck.  SQL Server Agent doesn't attach to the NT console so there was nowhere for the popup to go, hence the events.  So the real solution is proper even handling in .Net programs.  Living with no event hander is not an option if the job is going to run in unattended in SQL Server Agent.