SQL Server connection timeout - but no audit logs generated
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:
.
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
edited Sep 11 '17 at 19:15
MDCCL
6,70731744
asked Aug 15 '16 at 10:13
Srivatsan MohanSrivatsan Mohan
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.
|
show 1 more comment
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:
.
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
edited Sep 11 '17 at 19:15
MDCCL
6,70731744
asked Aug 15 '16 at 10:13
Srivatsan MohanSrivatsan Mohan
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.
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
|
show 1 more comment
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:
.
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
edited Sep 11 '17 at 19:15
MDCCL
6,70731744
asked Aug 15 '16 at 10:13
Srivatsan MohanSrivatsan Mohan
1
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:
.
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
sql-server sql-server-2012 odbc
edited Sep 11 '17 at 19:15
MDCCL
6,70731744
asked Aug 15 '16 at 10:13
Srivatsan MohanSrivatsan Mohan
1
edited Sep 11 '17 at 19:15
MDCCL
6,70731744
asked Aug 15 '16 at 10:13
Srivatsan MohanSrivatsan Mohan
1
edited Sep 11 '17 at 19:15
MDCCL
6,70731744
edited Sep 11 '17 at 19:15
MDCCL
6,70731744
edited Sep 11 '17 at 19:15
MDCCL
6,70731744
6,70731744
asked Aug 15 '16 at 10:13
Srivatsan MohanSrivatsan Mohan
1
asked Aug 15 '16 at 10:13
Srivatsan MohanSrivatsan Mohan
1
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
|
show 1 more comment
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
|
show 1 more comment
2 Answers
2
active
oldest
votes
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.
answered Aug 15 '16 at 11:59
WEI_DBAWEI_DBA
38316
add a comment |
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... :-)
answered Aug 15 '16 at 13:15
Jason B.Jason B.
54739
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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.
answered Aug 15 '16 at 11:59
WEI_DBAWEI_DBA
38316
add a comment |
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.
answered Aug 15 '16 at 11:59
WEI_DBAWEI_DBA
38316
add a comment |
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.
answered Aug 15 '16 at 11:59
WEI_DBAWEI_DBA
38316
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.
answered Aug 15 '16 at 11:59
WEI_DBAWEI_DBA
38316
answered Aug 15 '16 at 11:59
WEI_DBAWEI_DBA
38316
answered Aug 15 '16 at 11:59
WEI_DBAWEI_DBA
38316
answered Aug 15 '16 at 11:59
WEI_DBAWEI_DBA
38316
38316
add a comment |
add a comment |
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... :-)
answered Aug 15 '16 at 13:15
Jason B.Jason B.
54739
add a comment |
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... :-)
answered Aug 15 '16 at 13:15
Jason B.Jason B.
54739
add a comment |
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... :-)
answered Aug 15 '16 at 13:15
Jason B.Jason B.
54739
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... :-)
answered Aug 15 '16 at 13:15
Jason B.Jason B.
54739
edited Aug 15 '16 at 13:27
answered Aug 15 '16 at 13:15
Jason B.Jason B.
54739
answered Aug 15 '16 at 13:15
Jason B.Jason B.
54739
answered Aug 15 '16 at 13:15
Jason B.Jason B.
54739
54739
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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