How should I avoid MSSQL timeout?

Question

I have 2 programs:

A server that produce data and insert it in MSSQL(written with C++ and use ODBC for connection).
A client that read data from MSSQL and show it to the user(written in .NET(C#, VB) and use .Net SqlClient).

Now my problem is when my server is running and insert data into MSSQL, my client can't select data from it or even sometime the connection will be failed. Currently I am running both server and client on a single machine but they should run on network! I try to increase connection timeout in my connection string and it work sometime but not always. My server load is not too high(may be one insert every second). So what is wrong in my server and what should I do to find the error?

for the timeout [SlqCommand.TimeOut](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout(v=vs.110).aspx) — tschmit007, Jan 15 '13 at 13:53
otherwise you can try SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED — tschmit007, Jan 15 '13 at 14:01
@tschmit007 Thanks for the comment, I set that but it will work some times and fail other times, my problem is mostly in connection time not execution time(when program connect successfully, it usually work with no error). And my problem is am I doing something wrong in my server, since one insert per second is not that much to stop MSSQL from accepting new connections, is it? — BigBoss, Jan 15 '13 at 14:02
yes theorically MSSQL can handle 2^16 connections. Is your db set to SINGLE_USER ? (select databasepropertyex('dbname', 'UserAccess')) — tschmit007, Jan 15 '13 at 14:05
@tschmit007 No never, actually my server only execute a set of stored procedures — BigBoss, Jan 15 '13 at 14:25
can you try (in your .Net client) to ExecuteScalar 'select @@version' before querying your table. I'm really surprised with this timeout. — tschmit007, Jan 15 '13 at 14:43
Are you closing and releasing the connections properly in your C++ code. A connection leak could cause this problem see http://stackoverflow.com/questions/5741813/track-connection-leaks — Steve Ford, Jan 15 '13 at 15:06
What do you mean "connection time not execution time"? Please post the code that is timing out. If that server with 1 insert per second is causing the client to not even be able to open a connection then something is wrong. — paparazzo, Jan 15 '13 at 15:10
Did you make sure that your client has 1005 connectivity with SQL Server when you server software stopped? — Serg, Jan 15 '13 at 15:18
@SteveFord In my C++ server I open one connection when I connect to MSSQL and use it until I close my connection with server and never open/reopen any other connection. So I can't leak any connection! — BigBoss, Jan 15 '13 at 17:09
No. SQL Server is a multi user database. You may have locks in the database etc. but no connection is NOT the same. THis points to a fundamental issue. — TomTom, Jan 16 '13 at 06:14

score 1 · Accepted Answer · answered Jan 16 '13 at 06:12

If the reading client can't connect, you need to understand why. That has nothing to do with the other client writing.

If the reading client is connecting OK but the query it issues times out, and the query should safely execute in, say, a few seconds, that's an indication of a lock (perhaps held by a transaction that was never committed). When the problem occurs, check things like sys.dm_tran_locks on the server for a process holding a lock on the table.

How should I avoid MSSQL timeout?

1 Answers1