Occasionally Getting SqlException: Timeout expired

You need to investigate this on the server side to understand why is the execution timing out. Note that the server has no timeout, the timeout is caused by the default 30 seconds on SqlCommand.CommandTimeout.

A good resource is Waits and Queues, which is a methodology to diagnose performance bottlenecks with SQL Server. Based on the actual cause of the timeout, proper action can be taken. You must establish first and foremost whether you're dealing with slow execution (a bad plan) or blocking.

If I'd venture a guess, I would say that the unhealthy pattern of IF EXISTS... UPDATE is the root cause. This pattern is incorrect and will cause failures under concurrency. Two concurrent transaction executing the IF EXISTS simultaneously will both reach the same conclusion and both attempt to INSERT or UPDATE. Depending on the exiting constraints in the database you can end up with a deadlock (the lucky case) or with a lost write (the unlucky case). However, only proper investigation would reveal the actual root cause. Could be something totally different, like auto-growth events.

Your procedure is also incorrectly handling the CATCH block. You must always check the XACT_STATE() because the transaction may be already rolled back by the time your CATCH block runs. Is also not clear what you expect from naming the transaction, this is a common mistake I see often associated with confusing named transactions with savepoints. For a correct pattern see Exception Handling and Nested Transactions.

Edit

Here is a possible way to investigate this:

1. Change the relevant CommandTimeout to 0 (ie. infinite).
2. Enable the blocked process threshold, set it to 30 seconds (the former CommandTimeout)
3. Monitor in Profiler for Blocked Process Report Event
4. Start your workload
5. See if the Profiler produces any report events. If it does, they will pinpoint the cause.

These actions will cause a 'blocked process report' event every time you would had get a timeout, if the timeout was cause by blocking. You application will continue to wait until the blocking is removed, if the blocking is caused by a live-lock then it will wait forever.

Add this line to your connection string:

Connect Timeout=200; pooling='true'; Max Pool Size=200

You can set myCom.CommandTimeout = 200 also

If large number of data is there you can also increase the timeout seconds from 200 to 600 seconds.

Edit this in web.config as well.

Follow THIS doccument.

This can happens because of parameter sniffing. So just use local variables, declared within the stored proc. and use them appropriately.

Declare @InVar1 ...

.....

where condition=@Invar1