WHEN timeout

http://weblogs.sqlteam.com/dang/archive/2007/10/20/Use-Caution-with-Explicit-Transactions-in-Stored-Procedures.aspx
Use Caution with Explicit Transactions in Stored Procedures

worktable too big

Attention events can cause open transactions and blocking in SQL Server
http://blogs.msdn.com/b/pamitt/archive/2010/11/07/attention-events-causing-open-transaction-and-blocking.aspx

 

 

LOCK TIME OUT:

 

SELECT @@LOCK_TIMEOUT

 

 

WAY TO CAPTURE TIME OUT

In Profiler, you can add the event "Attention" under "Errors and Warnings". It's definition is

Collects all attention events, such as client interrupt request or when a client connection is broken.

As far as other errors (deadlocks, contraints, etc), try "Execution Warning" (reports warning that occurred during the execution of SQL statement or procedure) or "User Error Message" (the error message displayed to the user in the case of an error or exception).

 

Attention events can cause open transactions and blocking in SQL Server

 

Solutions:

So now that we know how Attention events can result in open transactions and blocking problems, what can we do to resolve this problem? Here are two solutions that I have successfully tried while working with customers -

• Check after each transaction (or before starting a new transaction in the same session) to see if the transaction is complete by using the following statement:

IF @@TRANCOUNT > 0 ROLLBACK

• Use SET XACT_ABORT ON for the connection, or in any stored procedures which begin transactions and are not cleaning up following an error. In the event of a run-time error, this setting will abort any open transactions and return control to the client. Note that T-SQL statements following the statement which caused the error will not be executed. It’s a good practice to set XACT_ABORT to on before starting any user transactions so that any batch terminating error (such as Attention events) would roll back the entire transaction. If you can’t modify the application easily to specify XACT_ABORT, you can try the USER_OPTIONS configuration setting in SQL Server to turn on XACT_ABORT on an entire instance of SQL Server. The below snapshot shows how to turn on XACT_ABORT on an instance of SQL Server –

Important: This (or setting it at the connection level) does not guarantee desired behavior in case of an Attention event though, since application code can override the setting. The only completely reliable way would be to set XACT_ABORT to ON before every BEGIN TRAN