Question

maaaaaaaa8 on Tue, 28 Nov 2017 12:28:50


I want to know reasons why error [SQL Server does not exist or access denied] occur.

Here is my production environment information.
Version: SQLServer 2008 R2(SP2)

I have some web service using SQLServer. Usually the error doesn't occur.
But few days ago, when user connection was over 25000 I got a lot of 500 errors [SQL Server does not exist or access denied] from my web servers. (applications on web servers attempted to execute some SQL Query on the DB Server)

I investigate maximum connection on SQLServer and it may be 32,767.
And even if current connection exceed limit, I may receive a different error message(I also investigated).
So I think there is any other reason.

When many errors occurred, many PAGELACH_SH/PAGELACH_EX waits existed.
I hope this will be some hint...


I would appreciate it if anyone tell me the reason or any possibility why the errors occur under high load.
If more information need, I will glad to show you.


Replies

Tom Phillips on Tue, 28 Nov 2017 12:42:16


That error is a generic "cannot connect to server" message.  It does not help diagnose anything.

If you are using up 32,000 connections you have a connection leak in your code.  You need to close() your connections when you are done with them.

Shanky_621 on Tue, 28 Nov 2017 12:46:47


There could be quite a few reason behind that message. As per this Support article( Read it) this may be related to application connection string and what interface you are using.

The "SQL Server does not exist or access denied" message does not indicate the following:
  • The logon process to SQL Server failed.
  • SQL Server does not have the correct permissions to process the query.
  • You cannot use SQL Server authentication because only Windows authentication is permitted.

Can you upload or paste here(if not too big) SQL Server erorrlog at time when you faced this issue. Log must include texts which came half hour before this message, this will help me identify if something is causing this. 

My hunch someone was trying to connect to SQL Server from machine which may be blocked by firewall

maaaaaaaa8 on Wed, 29 Nov 2017 07:18:10


Thank you for your reply.

I close() every time when I am done with them.
But probably there are some code without close() so I will check my codes.

My web site have always high traffic (30000-50000 users / sec)
And When I got errors, traffic was over 100000 / sec. (Google Analytics Showed. Maybe 10000sessions / sec?)
So Maybe I need to consider architecture.
But first I want to know the reason in detail if possible.

maaaaaaaa8 on Wed, 29 Nov 2017 07:53:13


Thank you for your reply.

here is a part of SQLServer error log.

------------------------------------------------------------
11/15/2017 15:21:21,spid580,unknown,Using 'dbghelp.dll' version '4.0.5'
11/15/2017 15:21:18,,エラー,[165] ODBC エラー: 0、名前付きパイプのプロバイダー : SQL Server への接続を開けませんでした [2]. [SQLSTATE 08001]
(English translated by me: ODBC error: 0, a provider with named pipe : can not open connection to SQLServer [2]. [SQLSTATE 08001])
11/15/2017 15:19:42,spid580,unknown,Using 'dbghelp.dll' version '4.0.5'
11/15/2017 15:19:18,spid580,unknown,Using 'dbghelp.dll' version '4.0.5'
11/15/2017 15:19:17,spid580,unknown,Using 'dbghelp.dll' version '4.0.5'
11/15/2017 15:18:18,spid580,unknown,Using 'dbghelp.dll' version '4.0.5'
11/15/2017 15:18:17,spid580,unknown,Using 'dbghelp.dll' version '4.0.5'
11/15/2017 15:17:18,spid580,unknown,Using 'dbghelp.dll' version '4.0.5'
11/15/2017 15:17:16,spid580,unknown,Using 'dbghelp.dll' version '4.0.5'
11/15/2017 15:16:18,spid580,unknown,Using 'dbghelp.dll' version '4.0.5'
11/15/2017 15:16:15,spid580,unknown,Using 'dbghelp.dll' version '4.0.5'
11/15/2017 15:16:03,,エラー,[165] ODBC エラー: 0、名前付きパイプのプロバイダー : SQL Server への接続を開けませんでした [2]. [SQLSTATE 08001]
(English translated by me: ODBC error: 0, a provider with named pipe : can not open connection to SQLServer [2]. [SQLSTATE 08001])
11/15/2017 15:15:18,spid580,unknown,Using 'dbghelp.dll' version '4.0.5'
11/15/2017 15:15:15,spid580,unknown,Using 'dbghelp.dll' version '4.0.5'
11/15/2017 15:14:42,spid580,unknown,Using 'dbghelp.dll' version '4.0.5'
~ same log continues
11/15/2017 14:53:45,spid580,unknown,Using 'dbghelp.dll' version '4.0.5'
11/15/2017 14:53:44,spid580,unknown,Using 'dbghelp.dll' version '4.0.5'
11/15/2017 14:52:45,spid580,unknown,Using 'dbghelp.dll' version '4.0.5'
11/15/2017 14:52:44,spid580,unknown,Using 'dbghelp.dll' version '4.0.5'
------------------------------------------------------------


>My hunch someone was trying to connect to SQL Server from machine which may be blocked by firewall
Thank you for your suggestion. But There is no firewall between the DB and web servers.

>There could be quite a few reason behind that message.
I agree. And I'm sorry about can not giving you sufficient information.
I'm welcome any other suggestion.


Tom Phillips on Wed, 29 Nov 2017 12:14:09


With connection pooling, you normally do not get anywhere near 1,000 SQL Server connections even with that kind of load, unless you have long running SQL commands. 

When the connection is "closed", it is actually just released back to the connection pool for reuse by the next SQL command run.  

The error you are getting appears to be a logon error.  That would indicate it is trying to open another connection because the connection pool does not have a connection for reuse.  I would closely look at your code and verify you are closing your connections.

It is also possible your SQL Server or network simply cannot handle that much traffic and is delaying the connection until it times out.

 

Shanky_621 on Wed, 29 Nov 2017 12:56:27


I would first suggest to apply Sp3 ASAP, you are running completely unsupported SQl Server. After this is done let us check again if such messages come

maaaaaaaa8 on Thu, 30 Nov 2017 00:45:21


Thank you. I will consider it.
Thank you for a seriously.

maaaaaaaa8 on Thu, 30 Nov 2017 00:55:24


I thought that several thousands of connections cannot be helped.
So your suggestion is new for me. I don't have long running SQL commands. So I will check my codes closely.

Thank you for your very helpful explanation.