SQL Server connection timeout - but no audit logs generated












0















We use SQL Server 2012 in our production system. We are getting this issue since past 48 hours and we have no explanation to why it's happening. It all started after our server admins restarted the windows server as part of the weekly restart schedule.



We have tried multiple things including restarting the server, SQL service, shrinking logs, etc. But the timeouts keep occurring. We are only having around 150 connections as per sysprocesses. The server is a very powerful one with 32 GB RAM and the maximum connections parameter is set to unlimited (0 = unlimited).



Even after that, we are still randomly getting timeouts at the time of connection. The PING to the server is healthy.



Here is a screenshot of the error thrown:



here.



Logs are not appearing in the login Log in the server. But in the security setting, we have enabled logging of both success and failure login attempts.



Please guide.










share|improve this question
















bumped to the homepage by Community 46 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
















  • If it doesn't show up in the SQL Server Errorlog, then the connection isn't even getting to SQL Server. Run a network trace to figure out what's going on.

    – Sean Gallardy
    Aug 15 '16 at 11:53











  • Do you have any login triggers? Also, I had a similar problem and it turned out that there were missing DNS suffixes. If you run ipconfig /all then review, you will see DNC Suffix Search List.

    – Michael Keleher
    Aug 15 '16 at 13:12











  • We ran wireshark to see the network IO.. Wireshark detected something called a "Spurious Retransmission" before the client decided to timeout. See here (imgur.com/a/oG4MQ)

    – Srivatsan Mohan
    Aug 15 '16 at 14:12













  • "If you’re seeing spurious retransmissions it means that the sender thought packets were lost and sent them again" blog.packet-foo.com/2013/06/spurious-retransmissions Looks like a networking issue.

    – Sean Gallardy
    Aug 15 '16 at 14:17






  • 2





    Likely a firewall closed TCP port 1433 after the restart. This is not a SQL Server issue.

    – Randolph West
    Aug 16 '16 at 2:36
















0















We use SQL Server 2012 in our production system. We are getting this issue since past 48 hours and we have no explanation to why it's happening. It all started after our server admins restarted the windows server as part of the weekly restart schedule.



We have tried multiple things including restarting the server, SQL service, shrinking logs, etc. But the timeouts keep occurring. We are only having around 150 connections as per sysprocesses. The server is a very powerful one with 32 GB RAM and the maximum connections parameter is set to unlimited (0 = unlimited).



Even after that, we are still randomly getting timeouts at the time of connection. The PING to the server is healthy.



Here is a screenshot of the error thrown:



here.



Logs are not appearing in the login Log in the server. But in the security setting, we have enabled logging of both success and failure login attempts.



Please guide.










share|improve this question
















bumped to the homepage by Community 46 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
















  • If it doesn't show up in the SQL Server Errorlog, then the connection isn't even getting to SQL Server. Run a network trace to figure out what's going on.

    – Sean Gallardy
    Aug 15 '16 at 11:53











  • Do you have any login triggers? Also, I had a similar problem and it turned out that there were missing DNS suffixes. If you run ipconfig /all then review, you will see DNC Suffix Search List.

    – Michael Keleher
    Aug 15 '16 at 13:12











  • We ran wireshark to see the network IO.. Wireshark detected something called a "Spurious Retransmission" before the client decided to timeout. See here (imgur.com/a/oG4MQ)

    – Srivatsan Mohan
    Aug 15 '16 at 14:12













  • "If you’re seeing spurious retransmissions it means that the sender thought packets were lost and sent them again" blog.packet-foo.com/2013/06/spurious-retransmissions Looks like a networking issue.

    – Sean Gallardy
    Aug 15 '16 at 14:17






  • 2





    Likely a firewall closed TCP port 1433 after the restart. This is not a SQL Server issue.

    – Randolph West
    Aug 16 '16 at 2:36














0












0








0








We use SQL Server 2012 in our production system. We are getting this issue since past 48 hours and we have no explanation to why it's happening. It all started after our server admins restarted the windows server as part of the weekly restart schedule.



We have tried multiple things including restarting the server, SQL service, shrinking logs, etc. But the timeouts keep occurring. We are only having around 150 connections as per sysprocesses. The server is a very powerful one with 32 GB RAM and the maximum connections parameter is set to unlimited (0 = unlimited).



Even after that, we are still randomly getting timeouts at the time of connection. The PING to the server is healthy.



Here is a screenshot of the error thrown:



here.



Logs are not appearing in the login Log in the server. But in the security setting, we have enabled logging of both success and failure login attempts.



Please guide.










share|improve this question
















We use SQL Server 2012 in our production system. We are getting this issue since past 48 hours and we have no explanation to why it's happening. It all started after our server admins restarted the windows server as part of the weekly restart schedule.



We have tried multiple things including restarting the server, SQL service, shrinking logs, etc. But the timeouts keep occurring. We are only having around 150 connections as per sysprocesses. The server is a very powerful one with 32 GB RAM and the maximum connections parameter is set to unlimited (0 = unlimited).



Even after that, we are still randomly getting timeouts at the time of connection. The PING to the server is healthy.



Here is a screenshot of the error thrown:



here.



Logs are not appearing in the login Log in the server. But in the security setting, we have enabled logging of both success and failure login attempts.



Please guide.







sql-server sql-server-2012 odbc






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Sep 11 '17 at 19:15









MDCCL

6,70731744




6,70731744










asked Aug 15 '16 at 10:13









Srivatsan MohanSrivatsan Mohan

1




1





bumped to the homepage by Community 46 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.







bumped to the homepage by Community 46 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.















  • If it doesn't show up in the SQL Server Errorlog, then the connection isn't even getting to SQL Server. Run a network trace to figure out what's going on.

    – Sean Gallardy
    Aug 15 '16 at 11:53











  • Do you have any login triggers? Also, I had a similar problem and it turned out that there were missing DNS suffixes. If you run ipconfig /all then review, you will see DNC Suffix Search List.

    – Michael Keleher
    Aug 15 '16 at 13:12











  • We ran wireshark to see the network IO.. Wireshark detected something called a "Spurious Retransmission" before the client decided to timeout. See here (imgur.com/a/oG4MQ)

    – Srivatsan Mohan
    Aug 15 '16 at 14:12













  • "If you’re seeing spurious retransmissions it means that the sender thought packets were lost and sent them again" blog.packet-foo.com/2013/06/spurious-retransmissions Looks like a networking issue.

    – Sean Gallardy
    Aug 15 '16 at 14:17






  • 2





    Likely a firewall closed TCP port 1433 after the restart. This is not a SQL Server issue.

    – Randolph West
    Aug 16 '16 at 2:36



















  • If it doesn't show up in the SQL Server Errorlog, then the connection isn't even getting to SQL Server. Run a network trace to figure out what's going on.

    – Sean Gallardy
    Aug 15 '16 at 11:53











  • Do you have any login triggers? Also, I had a similar problem and it turned out that there were missing DNS suffixes. If you run ipconfig /all then review, you will see DNC Suffix Search List.

    – Michael Keleher
    Aug 15 '16 at 13:12











  • We ran wireshark to see the network IO.. Wireshark detected something called a "Spurious Retransmission" before the client decided to timeout. See here (imgur.com/a/oG4MQ)

    – Srivatsan Mohan
    Aug 15 '16 at 14:12













  • "If you’re seeing spurious retransmissions it means that the sender thought packets were lost and sent them again" blog.packet-foo.com/2013/06/spurious-retransmissions Looks like a networking issue.

    – Sean Gallardy
    Aug 15 '16 at 14:17






  • 2





    Likely a firewall closed TCP port 1433 after the restart. This is not a SQL Server issue.

    – Randolph West
    Aug 16 '16 at 2:36

















If it doesn't show up in the SQL Server Errorlog, then the connection isn't even getting to SQL Server. Run a network trace to figure out what's going on.

– Sean Gallardy
Aug 15 '16 at 11:53





If it doesn't show up in the SQL Server Errorlog, then the connection isn't even getting to SQL Server. Run a network trace to figure out what's going on.

– Sean Gallardy
Aug 15 '16 at 11:53













Do you have any login triggers? Also, I had a similar problem and it turned out that there were missing DNS suffixes. If you run ipconfig /all then review, you will see DNC Suffix Search List.

– Michael Keleher
Aug 15 '16 at 13:12





Do you have any login triggers? Also, I had a similar problem and it turned out that there were missing DNS suffixes. If you run ipconfig /all then review, you will see DNC Suffix Search List.

– Michael Keleher
Aug 15 '16 at 13:12













We ran wireshark to see the network IO.. Wireshark detected something called a "Spurious Retransmission" before the client decided to timeout. See here (imgur.com/a/oG4MQ)

– Srivatsan Mohan
Aug 15 '16 at 14:12







We ran wireshark to see the network IO.. Wireshark detected something called a "Spurious Retransmission" before the client decided to timeout. See here (imgur.com/a/oG4MQ)

– Srivatsan Mohan
Aug 15 '16 at 14:12















"If you’re seeing spurious retransmissions it means that the sender thought packets were lost and sent them again" blog.packet-foo.com/2013/06/spurious-retransmissions Looks like a networking issue.

– Sean Gallardy
Aug 15 '16 at 14:17





"If you’re seeing spurious retransmissions it means that the sender thought packets were lost and sent them again" blog.packet-foo.com/2013/06/spurious-retransmissions Looks like a networking issue.

– Sean Gallardy
Aug 15 '16 at 14:17




2




2





Likely a firewall closed TCP port 1433 after the restart. This is not a SQL Server issue.

– Randolph West
Aug 16 '16 at 2:36





Likely a firewall closed TCP port 1433 after the restart. This is not a SQL Server issue.

– Randolph West
Aug 16 '16 at 2:36










2 Answers
2






active

oldest

votes


















0














Did the server admins install any service packs and/or CU's? Something had to have affected SQL Server. Has this happened before on the weekly restarts? Have you tried recreating another DSN to see if the same error occurs? How are you connecting in the DSN, Windows NT or SQL login? Since you are getting some successful connections, this might be difficult to diagnose.






share|improve this answer































    0














    I doubt this is a SQL issue. I'd start with checking Windows logs on the server (System, Application, and Security). You could have a faulty driver that finally just calls it quits after erroring for x amount of time. If those checkout as clean, then do a network trace to see what happens when someone attempts to make a connection. Check out network logs (routers, switches). Could be a flaky switch port that's taking lots of errors. Bottom line, need to check more than just SQL in this situation. And by the way, I would not consider a SQL Server very powerful with 32gb of ram, my laptop has 32gb of ram... :-)






    share|improve this answer

























      Your Answer








      StackExchange.ready(function() {
      var channelOptions = {
      tags: "".split(" "),
      id: "182"
      };
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function() {
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled) {
      StackExchange.using("snippets", function() {
      createEditor();
      });
      }
      else {
      createEditor();
      }
      });

      function createEditor() {
      StackExchange.prepareEditor({
      heartbeatType: 'answer',
      autoActivateHeartbeat: false,
      convertImagesToLinks: false,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: null,
      bindNavPrevention: true,
      postfix: "",
      imageUploader: {
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      },
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      });


      }
      });














      draft saved

      draft discarded


















      StackExchange.ready(
      function () {
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f146809%2fsql-server-connection-timeout-but-no-audit-logs-generated%23new-answer', 'question_page');
      }
      );

      Post as a guest















      Required, but never shown

























      2 Answers
      2






      active

      oldest

      votes








      2 Answers
      2






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









      0














      Did the server admins install any service packs and/or CU's? Something had to have affected SQL Server. Has this happened before on the weekly restarts? Have you tried recreating another DSN to see if the same error occurs? How are you connecting in the DSN, Windows NT or SQL login? Since you are getting some successful connections, this might be difficult to diagnose.






      share|improve this answer




























        0














        Did the server admins install any service packs and/or CU's? Something had to have affected SQL Server. Has this happened before on the weekly restarts? Have you tried recreating another DSN to see if the same error occurs? How are you connecting in the DSN, Windows NT or SQL login? Since you are getting some successful connections, this might be difficult to diagnose.






        share|improve this answer


























          0












          0








          0







          Did the server admins install any service packs and/or CU's? Something had to have affected SQL Server. Has this happened before on the weekly restarts? Have you tried recreating another DSN to see if the same error occurs? How are you connecting in the DSN, Windows NT or SQL login? Since you are getting some successful connections, this might be difficult to diagnose.






          share|improve this answer













          Did the server admins install any service packs and/or CU's? Something had to have affected SQL Server. Has this happened before on the weekly restarts? Have you tried recreating another DSN to see if the same error occurs? How are you connecting in the DSN, Windows NT or SQL login? Since you are getting some successful connections, this might be difficult to diagnose.







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Aug 15 '16 at 11:59









          WEI_DBAWEI_DBA

          38316




          38316

























              0














              I doubt this is a SQL issue. I'd start with checking Windows logs on the server (System, Application, and Security). You could have a faulty driver that finally just calls it quits after erroring for x amount of time. If those checkout as clean, then do a network trace to see what happens when someone attempts to make a connection. Check out network logs (routers, switches). Could be a flaky switch port that's taking lots of errors. Bottom line, need to check more than just SQL in this situation. And by the way, I would not consider a SQL Server very powerful with 32gb of ram, my laptop has 32gb of ram... :-)






              share|improve this answer






























                0














                I doubt this is a SQL issue. I'd start with checking Windows logs on the server (System, Application, and Security). You could have a faulty driver that finally just calls it quits after erroring for x amount of time. If those checkout as clean, then do a network trace to see what happens when someone attempts to make a connection. Check out network logs (routers, switches). Could be a flaky switch port that's taking lots of errors. Bottom line, need to check more than just SQL in this situation. And by the way, I would not consider a SQL Server very powerful with 32gb of ram, my laptop has 32gb of ram... :-)






                share|improve this answer




























                  0












                  0








                  0







                  I doubt this is a SQL issue. I'd start with checking Windows logs on the server (System, Application, and Security). You could have a faulty driver that finally just calls it quits after erroring for x amount of time. If those checkout as clean, then do a network trace to see what happens when someone attempts to make a connection. Check out network logs (routers, switches). Could be a flaky switch port that's taking lots of errors. Bottom line, need to check more than just SQL in this situation. And by the way, I would not consider a SQL Server very powerful with 32gb of ram, my laptop has 32gb of ram... :-)






                  share|improve this answer















                  I doubt this is a SQL issue. I'd start with checking Windows logs on the server (System, Application, and Security). You could have a faulty driver that finally just calls it quits after erroring for x amount of time. If those checkout as clean, then do a network trace to see what happens when someone attempts to make a connection. Check out network logs (routers, switches). Could be a flaky switch port that's taking lots of errors. Bottom line, need to check more than just SQL in this situation. And by the way, I would not consider a SQL Server very powerful with 32gb of ram, my laptop has 32gb of ram... :-)







                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited Aug 15 '16 at 13:27

























                  answered Aug 15 '16 at 13:15









                  Jason B.Jason B.

                  54739




                  54739






























                      draft saved

                      draft discarded




















































                      Thanks for contributing an answer to Database Administrators Stack Exchange!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid



                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.


                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function () {
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f146809%2fsql-server-connection-timeout-but-no-audit-logs-generated%23new-answer', 'question_page');
                      }
                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      Popular posts from this blog

                      Liste der Baudenkmale in Friedland (Mecklenburg)

                      Single-Malt-Whisky

                      Czorneboh