June 16, 2009 at 3:53 am
Our sql-server needed restarting on Sunday after the SQL-Agent hung the server - Couldn't Terminal Service onto the server. I stopped the agent but couldn't start it again from Enterprise manager, although I could access the data in the tables SQL Enterprise Manager.
When the server was Dumped and then restarted. We then switched off MS DTC as before the server was restarted (didn't have it running before). We find MS DTC slows down jobs and we don't do any cross server.
The next morning 3 jobs failed with the following error message: Error: -2147220368 (80040470); Provider Error: 0 (0) Error string: The Microsoft Distributed Transaction Coordinator (MSDTC service) is not available.
The steps that failed run DTS packages. The only difference we could find between the jobs that succeeded and the ones that failed was:- One or Two SQL Execute tasks (and VB Script task) in each failed job had (different) settings ticked in the Transaction section of the Options tab in Workflow Properties dialog box. The ones that succeeded had nothing ticked in this section. And the connection used in the DTS package is the same server as the jobs / DTS packages / database are on.
My questions are: Why didn't this happen before the restart as nothing has changed on the DTS packages for weeks? Will unchecking these Transaction settings stop the job erroring? Why is MS DTC required for this when there are no Distributed Transactions in the jobs and no explicit transactions are defined in any of the steps?
Any help would be gratefully received.
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply