Announcement

Collapse
No announcement yet.

Database timeout

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Database timeout

    SQL server 2000 on Server 2003
    Small (~300mb) database with access 2003 front end.
    Everything was running great until about a week ago. Suddenly we will get an error
    Code:
    'ODBC insert on a linked table 'Vessel' failed. 
    [Microsoft][ODBC SQL Server Driver] query timeout expired (#0)
    This will occur at random. Sometimes not until lunchtime. When it does it will last for about a minute and then the form will submit fine. Sometimes it will be a few more hours until it happens again. More often it happens every few minutes once it starts. It is always a timeout on this table. Everything else seems to continue to work fine

    Nothing has changed. I don't see anything in the event logs that corresponds with when this error occurs. To rule out server hardware, I converted the machine to a VM so I could runit on a newer piece do hardware. The problem persists.

    I have no idea where to go from here. Any direction would be much appreciated.

    Thanks

  • #2
    Re: Database timeout

    Originally posted by swkent View Post
    SQL server 2000 on Server 2003
    Small (~300mb) database with access 2003 front end.
    Everything was running great until about a week ago. Suddenly we will get an error
    Code:
    'ODBC insert on a linked table 'Vessel' failed. 
    [Microsoft][ODBC SQL Server Driver] query timeout expired (#0)
    This will occur at random. Sometimes not until lunchtime. When it does it will last for about a minute and then the form will submit fine. Sometimes it will be a few more hours until it happens again. More often it happens every few minutes once it starts. It is always a timeout on this table. Everything else seems to continue to work fine

    Nothing has changed. I don't see anything in the event logs that corresponds with when this error occurs. To rule out server hardware, I converted the machine to a VM so I could runit on a newer piece do hardware. The problem persists.

    I have no idea where to go from here. Any direction would be much appreciated.

    Thanks
    AV Scanner?

    Comment


    • #3
      Re: Database timeout

      Something dodgy on the physical network? (intermittent fault, or just an overloaded device)
      Tom Jones
      MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
      PhD, MSc, FIAP, MIITT
      IT Trainer / Consultant
      Ossian Ltd
      Scotland

      ** Remember to give credit where credit is due and leave reputation points where appropriate **

      Comment


      • #4
        Re: Database timeout

        No AV scanner.

        So I thought perhaps dodgy network card on the server. That is why I switched to a VM and shut the physical server down. Network is small. 12 workstations and ip phones. Handful of printers and two Netgear gigabit switches.

        It's a small company so there aren't any fancy network scanners/analysers to play with. Any suggestions where to start?

        Thanks

        Comment


        • #5
          Re: Database timeout

          Is it multiple clients (therefore a server issue) or just one client?
          Tom Jones
          MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
          PhD, MSc, FIAP, MIITT
          IT Trainer / Consultant
          Ossian Ltd
          Scotland

          ** Remember to give credit where credit is due and leave reputation points where appropriate **

          Comment


          • #6
            Re: Database timeout

            It happens to all users simultaneously. It happened at 10:04 EST today. There are no event log messages on the sever that coincide with that timing.

            I should also add that there are two users who have an updated Access 2013 front-end that they use. This has been working without a hitch for 6 months. On the machines which have Access 2013, I am seeing an Event log error under "Microsoft Office Alerts" which is the same as the error in the first post.
            Last edited by swkent; 9th September 2013, 21:24.

            Comment


            • #7
              Re: Database timeout

              Originally posted by swkent View Post
              It happens to all users simultaneously. It happened at 10:04 EST today. There are no event log messages on the sever that coincide with that timing.

              I should also add that there are two users who have an updated Access 2013 front-end that they use. This has been working without a hitch for 6 months. On the machines which have Access 2013, I am seeing an Event log error under "Microsoft Office Alerts" which is the same as the error in the first post.
              This seems to suggest increasing the timeout values on the server

              http://www.sqlservercentral.com/Foru...012-131-1.aspx

              Maybe a query that is being executed is taking longer than expected?

              Hopefully this helps with the timeout increase

              http://sqlserver.itags.org/q_sql-server_129844.html

              If possible get everyone to take note of exactly what they were doing at the time of the failure. It is possible that someone is running something, reports etc, that could be causing this.



              BTW I am by no means any good on SQL so Googling is pretty much where i'm at.

              Comment


              • #8
                Re: Database timeout

                SQL is not in my realm of knowledge either. That being said, Thanks for the links. The second link is not working however.

                Comment


                • #9
                  Re: Database timeout

                  Originally posted by swkent View Post
                  SQL is not in my realm of knowledge either. That being said, Thanks for the links. The second link is not working however.
                  Works fine for me.

                  1. Launch EM
                  2. Right-click your server and select Properties.
                  3. Connections Tab
                  4. Look at your query & concurrent connection timeout settings.

                  Same process on the SQL Management Studio.

                  Comment


                  • #10
                    Re: Database timeout

                    Modified the timeout value. Changed it to 0. It was set at 600 (10 mins). Will let you know what happens.

                    Comment


                    • #11
                      Re: Database timeout

                      OK. Changing the timeout to 0 appears to have stopped the freezing during that function. However, it cause an issue where one of the invoicing functions would not work properly. I went back and set it to 1200 (it was originally 600) and now everything seems to be ok.

                      I have a question though. If there is no timeout limit and a feature of the application stops working, does that indicate a problem with a query or view related to that feature?

                      Comment


                      • #12
                        Re: Database timeout

                        So we are at two days without the query timeout issue. Thanks to wullieb1 and ossian for your help.

                        I have a few questions though.
                        1. What would make this timeout suddenly start to occur? This database has been in use for 10+ years with no issues like this ever. Further, why would it happen sometimes but not every time?
                        2. The timeout was originally set for 600 (10 mins). When I changed it to 0 (no timeout) there is an invoice creation area of the application that stopped functioning properly. Basically it would not display all of the data from the database until clicking the create invoice button. This made it impossible for them to verify the accuracy of the invoice prior to creation. I changed the timeout to 1200 (20 mins) and this problem disappeared. I'm having a hard time wrapping my head around why a 30-60 second process worked with the timeout set at 600 (which it was clearly not running into) but did not work with no timeout....

                        Thanks again for your help and any other insight you can provide.

                        SK

                        Comment

                        Working...
                        X