Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 118
  • Last Modified:

Timeout expired

I am getting the error "System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding." The strange thing is its only happening from a certain web server. I took the same application to IIS 7 machine and it works fine. I also ran it on my local machine and its fine. The server it refuses to work from is one running IIS 6. Any ideas?
0
takwirirar
Asked:
takwirirar
  • 7
  • 6
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Please post the query that you are trying to execute.
0
 
takwirirarAuthor Commented:
cmd = New SqlDataAdapter("SELECT order_num FROM dbo.orders where status='Awaiting' or status='Received'", cnn)
            cmd.Fill(ds)

            DropDownList4.DataSource = ds
            DropDownList4.DataValueField = "order_num"
            DropDownList4.DataBind()
            ds.Clear()
            cmd.Dispose()

Open in new window


This returns 88 rows when I run it in SSMS
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
The table orders has any index on status column?
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
takwirirarAuthor Commented:
No index.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Depending on how many rows the table has the query can run in more time than the configured timeout. I recommend you to create an index on that column.
0
 
takwirirarAuthor Commented:
I have now created index and still getting timeout.

Index
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Clustered?
How did you create the index?
0
 
takwirirarAuthor Commented:
Yes clustered index. I right clicked on Indexes - Create Index - Clustered - on the status field
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Drop that index and then run the following command in a new query window:
CREATE INDEX IX_Orders_Status ON dbo.orders (status)

Open in new window

NOTE: You need to run this command in the correct database context.
0
 
takwirirarAuthor Commented:
Giving this as answer because it helped in some way. The issue was the DNS configuration of the offending server. It had the Google 8.8.8.8 address that was probably trying to resolve an intranet address which caused the issue. I removed the public DNS, cleared ARP cache and flush DNS. All is working now.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
So, not a database problem.
Just for curiosity, did you create the suggested index? If so, did you realize any performance boost ?
0
 
takwirirarAuthor Commented:
I can't say the performance boost is noticeable but I think having indexes is a great idea anyway. I'll probably have to do some research then ask a question about checking how long transactions take with/without indexes. Perhaps SQL profiler.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, you can start by using SQL Profiler to capture long running queries. Those are for sure very good candidates to have indexes.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now