Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 [Microsoft][ODBC SQL Server Driver]Timeout expired

Author  Topic 

Rob1988
Starting Member

5 Posts

Posted - 2010-10-30 : 13:32:18
Hello everyone,

Right i've spent 5 days trying to figure out why this problem is happening without success so any help here is much appreciated.

The Database / Server / Network

We are running a fairly small database which is about 400mb in size. The server is SBS2008 with SQL Server 2008 and exchange (6 mailboxes) on it. The server is an I7 920 with 16GB of DDR3 Ram which is ultilised at only 70% during busy times while the CPU is being use at 20% at busy times. 1.5TB of storage space in a 4 Drive 500gb RAID5 setup. All clients are running on Gigabit LAN (with Gigabit switch, ect) and are running AMD 9650 Quad Cores with 2GB DDR2 Ram.

The Problem

Everything on the server works perfectly, the hardware is a little over a year old and has been running great. In-fact everything works and has been working great up until 6 days ago. Basically, when the user on the database application prints an invoice, and the query is run to fetch the invoice information, the machine will Hang for 30 seconds and have the Error [Microsoft][ODBC SQL Server Driver]Timeout expired. This is intermitent however as sometimes it can print within 20 seconds and print the invoice fine. 9/10 attempts however will see the error and a blank invoice printed.

Everything else works with the application including some more complex and demanding queries regarding a delivery scheduler. I can access the server directly and log in fine and when i test the query in the query browser it runs in 0.329 seconds. So why the timeout / problem?!

What have i tried

-Checked Even Viewer for any issues. None found.
-Increasing SQL priority
-Checking the RAID5 Integrity
-Upgrading the RAM
-Restarting the services / server / router / switch
-Trying with only one connected user (and not the busy 12 connected users during the day).
-Increasing Remote query timeout value (its now set to 0, hence unlimited).
-Increasing the Remote Login Timeout to 60 seconds.
-Increasing the Connection-string timeout for designer to 60 seconds.
-Restarting the server / sql server again


Any ideas? This is giving me a massive headake not only from users pestering me but from trying to find a solution.

Thanks,

Rob1988

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-30 : 13:49:16
Have you looked at the database interaction.
When it succeeds is it taking 20 secs to get a result from the server or is most of the time printing.

It sounds like it could be that it sometimes takes over 30 secs to get a result and times out. Maybe you just have more data and it's taking longer and has now reached a threshold where it fails sometimes - will probably get worse.
30 secs is a long time and sounds like someoptimisation is needed.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Rob1988
Starting Member

5 Posts

Posted - 2010-10-30 : 13:56:13
The thing is invoices printed instantly 6 days ago and it went from instant to 20-30 seconds or timeout pritty much overnight. All users now have to wait a minimum of 15 seconds to execute a printed invoice where before it was instant. This is a big change from a gradulad increase in data.

The query (which i cant post for copyright i beleive as the software is a third party who is blaming the server and not them surprisingly) executes on the server in less than 0.4 seconds.
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-30 : 21:32:50
First, Rob, thanks for posting the server specs as this eliminates a LOT of questions we'll ask you.

Whne was the lasty time you rebuilt indexes? That's my first guess. Often times, when indexes get fragmented and statistics out of date, a query that takes a few hundred milliseconds instantly goes to many seconds/minutes with no in-between.

Have a look at the queries that are executed when invoices are printed (using sp_who2 and DBCC INPUT BUFFER(@spid) or by using profiler.

Then check the execution plans on those queries. Look for table scans and index scans in particular.

SET STATISTICS IO ON and execute the queries in Management Studio, Look for tables with a high # of reads.

Your database is very small, so it will be easy to rebuild all indexesm assuming you have a maintenance window.

This is a pretty common issue with 3rd party products. They build something that works great, but requires maintenance. Sometimes, you just rebuild their indexes and come out a hero.

Let us know if you need further assistance!
Go to Top of Page

Rob1988
Starting Member

5 Posts

Posted - 2010-10-31 : 12:51:58
The indexes were re-built about 16 months ago. Although i tried re-creating the statistics on a few of the key tables last night to no effect.

Im not at work at the moment but ill try this tomorrow night and see if it helps anything and will post the results.

Thanks,
Rob1988
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-31 : 13:51:03
You say that
"The query ... executes on the server in less than 0.4 seconds."
Therefore it can't be anything to do with indexes or table structure or statistics.

You need to print an invoice and find out where the time is taken.
Profiler will show when the query (I hope there is only one) is delivered to the server and when the result is returned to the aplication. From that you should be able to tell whether the application is taking a long time to deliver or respond to the query or whether something is causing an issue on the server.
Fortunately it is always taking a long time to pront so should be easy to diagnose.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Rob1988
Starting Member

5 Posts

Posted - 2010-11-08 : 13:27:52
Right ok, an update for everyone.

I actually fixed the problem for 5 days. How? Well i disabled anti-virus completely.

5 days later, the problem is back exactly the same?! I have now completely removed the anti-virus in an attempt to fix it and no joy.

I switched to the backup XP server running SQL Express and printing works fine meaning there is somthing wrong with this machine. I would switch everything to the backup server but its XP and can only handle 10 connections. A re-install is not practicle.

Ideas?
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-08 : 14:44:52
rebuild the indexes first
Go to Top of Page

Rob1988
Starting Member

5 Posts

Posted - 2010-11-14 : 10:14:14
I did not mention that i did this first and it made no difference. I re-built them again today just in case and again the same problem is happening.
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-15 : 10:32:35
look for blocking.

can you post the execution plan?
Go to Top of Page
   

- Advertisement -