Why does Sql Server connection TimesOut

Question

Hi

I am using Enterprise Library Data for my Sql database. I am using version 3.1. I am using this code to execute a long running sp (about 1 min).

Dim db As SqlDatabase = New SqlDatabase(System.Web.Configuration.WebConfigurationManager.ConnectionStrings("portalConnection").ConnectionString)
                    db.ExecuteNonQuery("spnametoexecute")

Connection string looks like this

<add name="portalConnection" connectionString="Server=IP;Database=DBName;uid=User;pwd=PWD; Timeout=180;"
  providerName="System.Data.SqlClient" />

Provlem is that I always get a TimeOut Exception. Why Is that ?

System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Answer 1

I think you set incorrect server ip or your server doesn't configured as well. Check if your allow Pipes and tcp/ip in configuration of your server. It's very often forgetting.

Answer 2

Well, there are a few possibilities:

• your query is too complex and takes too long (try writing better TSQL, adding indexes, or increasing the timeout)
• you are being blocked by another SPID (perhaps yourself on another connection)

Often, the second is the problem. Look at the blockages etc (sp_who / sp_who2) to see if there are any blocks. And use a trace/profiler to investigate the first option.

Answer 3

Do you ALWAYS get a timeout, or only when the long-running query executes? When you run it manually - that is, in management studio, how long does it take? Your connection is set to time out in 3 minutes, which is an ENORMOUS amount of time.

You need to attack this at a couple of levels:

1. Profile & Optimize the query
2. Test with very simple queries to see if there is a connection problem

Answer 4

I'm not too familliar with enterprise data library but I'd see if the SqlDatabase object has a timeout property. Try setting the timeout in your code and test the behaviour.