Awaiting DTC Message:UOW<TranID>

Awaiting DTC Message:UOW<TranID>

Post by Lito Domingue » Fri, 22 Dec 2000 07:23:47



Questions: Are there any enhancements available on SQL 7 regarding this problem??
Are there automated methods available to delete/abort* transaction?
Or the only method is via DTC Admin Console and force it to commit or abort??

Below are excerpt from
http://www.veryComputer.com/

Microsoft SQL Server version 6.5

--------------------------------------------------------------------------------
BUG #: 17882 (SQLBUG_65)

SYMPTOMS
When a connection is involved in a distributed transaction, SQL Server relies on
the transaction coordinator (Microsoft Distributed Transaction Coordinator, or
DTC) to notify it regarding transaction outcome so that it knows whether to
commit or abort the transaction. DTC uses a two phase commit protocol among all
enlisted servers and only commits if all servers have indicated that they are
prepared to commit.

Any time before the beginning of the two phase commit process, you can use the
Transact-SQL KILL statement to close a SQL Server connection that is causing
blocking or other undesirable behavior. However, after the two phase commit
protocol begins and SQL Server has indicated that it has the transaction in a
prepared state, it will no longer respond to the KILL statement.

CAUSE
According to the two-phase commit protocol, any resource manager (that is, SQL
Server) that has successfully responded to the transaction coordinator that it is
in a prepared state must guarantee that it can successfully commit or roll back
the transaction after the transaction coordinator has determined the transaction
outcome. To do this, SQL Server will hold all locks acquired during the
transaction and will not respond to the Transact-SQL KILL statement.

If DTC unexpectedly fails, or fails to notify SQL Server regarding the
transaction outcome, the resources used by the transaction will be tied up until
manual intervention occurs.

STATUS
Microsoft has confirmed this to be a problem in SQL Server version 6.5. This
problem has been corrected in U.S. Service Pack 5 for Microsoft SQL Server
version 6.5. For information about downloading and installing the latest SQL
Server Service Pack, see http://www.veryComputer.com/

For more information, contact your primary support provider.

MORE INFORMATION

There are no automated methods to restart DTC if it fails, so you must manually
restart the service. When DTC is restarted and is recovering its transaction log,
it will detect that a transaction was pending and some resource managers may not
have been notified. It will then notify the enlisting servers of the transaction
outcome.

After SQL Server has responded that it is prepared, it will wait on notification
from DTC regarding the transaction outcome. If it has not received any
notification within 60 seconds, it will print a message in the error log similar
to the following:

98/10/20 09:15:43.76 spid11 Awaiting DTC message.
UOW: e2a10172-bda9-11d1-bf4c-00600893905d State: PREPARED
The UOW value (a Unit Of Work ID) is a unique identifier for the transaction.
This message will repeat every 60 seconds until notification has been received
from DTC. If you see this message, open the DTC Administrative Console, or the
DTC service in SQL Enterprise Manager. On the Transaction tab, you will see a
list of DTC transactions and their current state, and you should see a
transaction with a Unit of Work ID value that matches the one in the error log
message. From this console, you can select the transaction, right-click, and
force the transaction to commit or abort. If using the DTC Administrative Console
does not alleviate the condition, and stopping and restarting the DTC service
doesn't either, the only way to remedy the situation is to stop and restart SQL
Server. During recovery, SQL Server will interrogate DTC to determine the
transaction outcome.

There may be rare circumstances where, even after restarting SQL Server, the
transaction outcome cannot be determined. This may occur if there is a permanent
network failure between the SQL Server and the commit coordinator, or it may
occur in situations where a hardware failure resulted in the loss of the DTC log
file. In such circumstances, SQL Server will log an error 3437 and stop recovery
of the database:
Msg 3437: Error recovering database 'pubs' - could not connect to
the DTC to check completion status of xact: Rid pageid is
0x132f and row num is 0x11
Beginning in SQL Server 6.5 Service Pack 5, SQL Server has two enhancements that
may be useful in resolving situations such as this. First, a new sysprocesses
waittype value, 0x0411, has been added. This value can be used to confirm that
SQL Server is waiting on DTC. Second, if the above methods have failed to resolve
the problem, the Transact-SQL KILL statement has been enhanced to allow an
optional WITH COMMIT or WITH ABORT clause, as shown in the following syntax:
KILL spid [WITH {COMMIT | ABORT}]
A warning will be written to the error log, indicating the UOW ID and the forced
transaction outcome. Take caution when using this new KILL statement syntax,
because it may result in inconsistencies among the various servers enlisted in
the transaction. This syntax should only be used when the other methods have
proved unable to resolve the issue. If the KILL ... WITH COMMIT/ABORT syntax is
used and the target spid is not waiting on DTC, error 6108 will be raised.

If you find that this issue recurs, open a support incident to pursue the
underlying issues with SQL Server or DTC that are causing this situation.

Additional query words: sp sp5MS msdtc orphan command T-SQL tran-sql trans- sql
transql transsql tsql terminate rollback errorlog SEM prodsql

Question: Are there any enhancements available on SQL 7 regarding this problem??
Are there automated methods available to delete/abort* transaction?
Or the only method is via DTC Admin Console and force it to commit or abort??

 
 
 

1. Awaiting DTC message. UOW

Hi,

We've had a distributed transaction writing every half hour to a remote
database running quite happily
(across trusted domains) for a while now.
Then all of a sudden, the remote SQL Server ground to nearly a complete
halt.
Every 10 secs, the following error was logged:

Awaiting DTC message. UOW: 598e77ca-0f50  (etc - very long number with
hyphens !) state: PREPARED

It looks like DTC is being blocked from completing the distributed
transaction somehow but we're not sure
why.
Another error that occurs frequently (but we don't know if it's affecting
anything) is:

Lazywriter: WARNING, couldn't find slot, 8/8, scanned 8

Has any-one come across these problems before ?

Any help greatly appreciated.

Mike C

Email:

2. Importing tables

3. Awaiting DTC message

4. IL-Chicago-82277--ORACLE Financials-Problem Solving Skills-Migration-Mainframe-C

5. Error:Awaiting DTC message

6. ?? install SQL Server 7 on an NT server

7. awaiting DTC message

8. Informix On-Line 5.0 & AIX 3.2.2

9. <<<RDBMS/UNIX/SYBASE ENGINEERS WANTED>>>>Oakland, CA

10. <><><> www.FotoCD.net <><><>

11. >>>>>---- BETA TESTERS WANTED -------<<<<<<<