CODEWITHSUNDEEP
c#sqlsql-serversql-server-2008t-sql
tishantha
tishantha

Reputation: 497

MSSQL Timeout error

I'm using MSSQL Server 2008. I have to work with several databases at a time. Some tomes the system gives an error "Transaction Timeout" when the insert or update records. But it works after a few minute.
There are few users are using differences windows applications to manipulate data in databases.
I want to know, is there any relation between this issue and multiple database usage ? enter image description here Is this query type (multiple database linked in a query) will effect to the Timeout ?

Upvotes: 2

Views: 164

Answers (2)

Francesco De Lisi
Francesco De Lisi

Reputation: 1523

If you're using SqlConnection, SqlCommand and Transaction you may want to check and set each of these timeout property to manage properly your application behaviour.

See TransactionOptions for IsolationLevel and TimeOut (the first one maybe has to be Required)

See SqlCommand.CommandTimeout if you're using commands.

A good way to use transaction could be

TimeSpan timeout = TimeSpan.FromSeconds(300); // 5 minutes

using(TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, timeout))
{
        // Code ...
}

Keep in mind that a lot of concurrent transactions could affect SQL Server efficiency. Maybe you need different instances of the same Server to use it properly, but the cons come with synchronization of each instance.

Upvotes: 1

Stan
Stan

Reputation: 1999

Highly depends on the timeout's reason. Frequently it is caused when resources are locked by one application and the second application waits too long. Multiple databases, even in single instance, are using Distibuted Transaction Coordinator.

A transaction within a single instance of the Database Engine that spans two or more databases is actually a distributed transaction. The instance manages the distributed transaction internally; to the user, it operates as a local transaction. http://technet.microsoft.com/en-us/library/jj856598(v=sql.110).aspx

And the DTC is much slower than working in the scope of the same database, so it causes data to be locked for a wider timeframe and might cause timeouts.

Upvotes: 1

Related Questions