command timeout - SQL 2005 express - critical customer down

  • Thread starter Thread starter Darin
  • Start date Start date
D

Darin

we have a VB application that we updated on a customer's machine. The
customer has SQL 2005 Express (they had MSDE and we had the same error
so we upgraded to express ohpeing that would fix the issue). THe
software runs fine from the server, but when trying to access it from a
client we get:

Timeout expired. The timeout period elapsed prior to obtaining a
connection from the pool. this may have occurred because all pooled
connections were in use and max pool size was reached.

In the application, we first try to connect to the SQL MASTER database
to make sure we can connect to the server, this is where the error is
occurring. The connection string is:

str = "Data Source=SERVER;"
str &= "Initial Catalog=MASTER;"
str &= "User Id={login}
str &= ";Password={password};"
str &= "Min Pool Size=1;"
str &= "Max Pool Size=50;"
str &= "Packet Size=32767;"

As you can see i have a pool size of 50 setup. Noone else is running the
software (since it errors out). Also, everything I have read says the
default timeout is 30 seconds, but this error appears in 15 seconds.
Also, in the program I have a try catch loop checking both
SQLClient.SQLException and general Exception, and this timeout message
is an Exception, NOT an SQL Exception.

Another interesting sidenote is if i chance the server name in the
connection string to a name that isn't on their network, I receive the
exact same timeout error. So, it isn't even getting to the SQL server at
all.

Our application uses .NET 2.0. The software worked fine on their setups
before yesterday, and that version was .NET 1.1.

Now, their IT guy came in and did some work on the internet router, and
i don't know exactly what he did, i am contacting him to find out.

This is critical as the customer is down.

Darin
 
we have a VB application that we updated on a customer's machine. The
customer has SQL 2005 Express (they had MSDE and we had the same error
so we upgraded to express ohpeing that would fix the issue). THe
software runs fine from the server, but when trying to access it from a
client we get:

Timeout expired. The timeout period elapsed prior to obtaining a
connection from the pool. this may have occurred because all pooled
connections were in use and max pool size was reached.

In the application, we first try to connect to the SQL MASTER database
to make sure we can connect to the server, this is where the error is
occurring. The connection string is:

str = "Data Source=SERVER;"
str &= "Initial Catalog=MASTER;"
str &= "User Id={login}
str &= ";Password={password};"
str &= "Min Pool Size=1;"
str &= "Max Pool Size=50;"
str &= "Packet Size=32767;"

As you can see i have a pool size of 50 setup. Noone else is running the
software (since it errors out). Also, everything I have read says the
default timeout is 30 seconds, but this error appears in 15 seconds.
Also, in the program I have a try catch loop checking both
SQLClient.SQLException and general Exception, and this timeout message
is an Exception, NOT an SQL Exception.

Another interesting sidenote is if i chance the server name in the
connection string to a name that isn't on their network, I receive the
exact same timeout error. So, it isn't even getting to the SQL server at
all.

Our application uses .NET 2.0. The software worked fine on their setups
before yesterday, and that version was .NET 1.1.

Now, their IT guy came in and did some work on the internet router, and
i don't know exactly what he did, i am contacting him to find out.

This is critical as the customer is down.

Darin

*** Sent via Developersdexhttp://www.developersdex.com***
Click to expand...

Have you installed SQL Express while there was some earlier versions
already installed ?
If yes, then please check the "Instance" name on the server. Since you
have SQL Server already installed, the
next installation goes under some other name - for example -
SQLExpress
You may follow these steps to identify whether there are 2 instances
of sql server on same machine.

Go to some folder on the server machine.
and create a UDL file. (Hope you know how to create UDL file)
Now select a proper provider - in this case SQL Server
On the second tab, you will find drop down for Data Source (machine on
which sql server is installed)
If you find entries like -- DBServer and DBServer\SQLExpress, then
there are 2 instances of SQL Server on machine with name DBServer.

So now when you try to connect, it does not resolve to the proper
version of SQL Server.
Solution :
1. Make sure that you start the agent service on the sql server
machine (DBServer) first.
2. Modify your connection string changing only the data source name.
Now with reference to above example,
your connection string will contain data source name like - DBServer
\sqlexpress where the DBServer is machine name and sqlexpress
is the instance name.
The instance name may be different. Please double check it while
changing connection string.

If you have further problems, I would be glad to help you. Also, if it
works for you, just post the same here, so that I can understand it
worked.

Thanks,
coolCoder.
 
You need to distinguish Connecction Timeout and Command TimeOut. In your
case, it seems Connection Timeout.

Connection timeout is the time period your application is trysing to connect
to the SQL Server, if the connection takes time to create, the trying will
last the given time period- timeout, say 30 sec. If during this time period,
the connection could not be created, you get Connection Timeout exception.

So, it is obvious the connection cannot be established in your case. When
you change the server name to a fake one, you got exactly same timeout
error, of course, because the connection could not be created. It has
nothing to do with POOL size.

Before start worrying on pool size, you need to find out why you cannot
connect: are the computers connected? does the SQL Server allow remote
connection? firewall?... Not knowing much on how the application and sql
server were set up, hard to say what is wrong. But the hind of IT guy
changed router is likely one of the reasons.
 
Well, I have fixed it.

It turns out that the firewall was turned on on the server so we had to
add a port in the exception list for 1433. Strange the error we were
receiving, and of course the IT person said he didn't do anything with
the firewall on the server.

I am glad it got fixed.

Darin
 
I have good news and bad news...
The good news is that this is fixable, the bad news is that you haven't
fixed it--just patched the problem.
Most SQL Server installations (including SQL Express) use dynamic IP
assignments--each named instance gets its own dedicated port assigned when
the service starts. 1433 is no longer guaranteed to be the port on which SQL
Server listens.
Make sure the SQL Browser service is started on the server. This should
address this issue.

See my whitepaper on connecting and chapter 9 of my latest book for more
details.
http://betav.com/mtcgi/mt-search.cgi?IncludeBlogs=3&search=connecting

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
 
Back
Top