Timeout expired the timeout period elapsed prior To completion of the operation or the server is not responding

1

Hi,

I have developed one simple vb.net 2012 project with sql server 2008 R2 backend. sql server database has simple recovery module and server computer has 64 gb memory and dual processor capability.

but my problem is system suddenly getting hang for 2 or 3 minutes after that working good as usual.

During system hang am getting message says "time out expired sql server".

During System hang, Sql server database view's are also not working.

we have around 20 to 30 users only with two users in remote area.

I have used simple sql command query with simple sql transaction for commit or rollback in the vb.net code.

Exceute command is set to 0.
connection timeout is set by default 15 seconds
remote connections allow and remote query set to 600 seconds.

please help me to solve this issue!

Thanks
Shanawaz

2

probably something is not commit'ing quickly enough.

Have a look for Adam Machanic sp_whoisactive procedure. That will tell you who is blocking whom - i.e. whether someone is waiting on someone else.

Long shot: Database has got set to AutoClose

SELECT	DATABASEPROPERTYEX(name, 'IsAutoClose'), name
FROM	master..sysdatabases
ORDER BY name
3

Dear Kristen,

I check the Database properties. Auto close option is set to false.
Yesterday database is hanged for ten minutes. After restart sql server service its work again.
I will check with sp_whoisactive procedure. any other idea dude.

Thanks
Shanawaz

4

Probably blocked by a transaction which was never Committed / Rolled back.

Check that one first. If that isn't the answer then we can think of something else.

sp_whoisactive is what I use to see BLOCKING, but in case its a bit daunting there's probably something in SSMS that lets you see BLOCKING.

5

Hi,

Today, while posting from remote area, we feel system hang again for few minutes.

I did quickly maintenance backup, just for safety. then database comes to normal.

Remote Area connectivity via MPLS 2 MB connection only using cisco routers

what do you think kirsten?

Thanks
Shanawaz

6

Hi,

In sql server,

Allow remote connections is enabled
and
remote query timeout is set to 600

Thanks
shanawaz

7

Do what @Kristen suggested in his first response - download and install sp_whoisactive and run it from a new SSMS window when you see the blockage. That will tell you the active processes, and who is blocking whom.

If you don't want to download sp_whoisactive, or even in addition to that, you can run sp_who2 which is built into SQL Server, and look at the BLK BY column. If you see some procs being blocked by some others, try to identify what the blocking process is.

Another tool that would be useful is the sys.dm_tran_locks. You would need to join to other dmvs or system functions to understand what is blocking what. But I think sp_whoisactive does all that for you and gives you succint and useful info.

8

Still "feels" to me like something is holding a Transaction open for "a long time". We only ever use Transactions in server-side logic (e.g. in Stored Procedures, so that a) they are held for the minimum possible duration and b) something going wrong on the Client cannot hold the transaction open. I don't know your APP of course, but doing a transaction on the client can mean that they transaction is left open if the APP crashes, or has a software bug where the COMMIT or ROLLBACK is not issues; also if the connection to the client is "slow" then the transaction will be held open for a prolonged period. Ideally a transaction (which will block other users) should only be held for a few nanoseconds, milliseconds at most ... anything longer than that is likely to cause Blocking which is then noticeable / disruptive to other users.

9

Hi,
Today, I tested from remote area. we find the issue is from remote posting only. Until all transactions commited, whole client system is getting idle for few minutes after commited transactions it comes to normal.

Remote posting taking time for few minutes.

Transactions commited and rollback using command objects in vb.net application. Stored procedures are not used for transaction in vb.net application.

Tell us any possible ways to resolve this issue.

Thanks
Shanawaz

10

As I said "We only ever use Transactions in server-side logic (e.g. in Stored Procedures), so that a) they are held for the minimum possible duration and b) if something goes wrong on the Client the transaction will not be held open. I don't know your APP of course, but doing a transaction on the client can mean that the transaction is left open if the APP crashes, or if the software has a bug the COMMIT or ROLLBACK is not issued; also if the connection to the client is "slow" then the transaction will be held open for a prolonged period. Ideally a transaction (which will block other users) should only be held for a few nanoseconds, milliseconds at most ... anything longer than that is likely to cause Blocking which would then be noticeable / disruptive to other users"

I've fixed some typos though ...