MS SQL 6.5无法连接。 [英] MS SQL 6.5 Unable to connect.
问题描述
首先我要说的是，是的，我们知道v6.5已不再支持
Microsoft和迁移到SQL2000可能会解决我们的问题。
其实迁移到SQL2000是1到2个月，现在是QA
阶段。不幸的是，我们现在需要解决当前的6.5问题
，因为它会严重影响公司的主要收入来源，我们遇到的每一次
停电。
问题：
至少每24小时一次SQL Server开始禁止新用户
连接。当发生这种情况时，2个CPU开始抖动。大约5
分钟后错误日志开始打印出以下错误
消息：无法连接。 500配置的用户
连接的最大数量已经连接。系统管理员可以使用sp_configure将
配置为更高的值。无法与
服务器建立新的连接;然而，现有的连接继续正常运行。
我们从未看到系统中任何接近500个用户连接的任何地方（平均约为350个连接的平均值为b $ b）。如果我们开始断开用户连接，
服务器会继续报告最大用户数是
已连接。最终通过打开的连接运行查询将挂起
，我们不得不求助于服务器的硬重启，因为SQL
Server既不会关闭也不会关闭操作系统。
服务器：
Compaq Proliant DL380，双863 MHz。处理器（x86 Family 6型号
8 Stepping 3 GenuineIntel），917,020 KB物理内存，Microsoft
Windows 2000 Server（版本5.0.2195 Service Pack 4 Build 2195），总计
虚拟内存3,138,688 KB，页面文件空间2,221,668 KB
SQL Server：
Microsoft SQL Server 6.50 - 6.50.479 （英特尔X86）。一些相关的
配置设置：内存 - 244100（以2K为单位），用户连接
？ 500，RA工作者线程？ 3，最大工作线程？ 255
我们在Microsoft
知识库中找到了对上述错误消息的一个引用，这是指服务器有2GB的情况
或更多物理内存，1.5GB分配给SQL Server。这与
不符合我们的情况。你有没有遇到过这个
的问题？
我很感激你的见解。
***已发送通过开发人员指南 http://www.developersdex.com ***
不要只是参加USENET ......获得奖励！
Let me start by saying that, yes we know v6.5 is no longer supported by
Microsoft and that moving to SQL2000 might resolve our problem.
Actually the migration to SQL2000 is 1 to 2 months out and in the QA
phase now. Unfortunately we need to resolve the current 6.5 issue now
as it heavily impacts a major revenue stream for the company with every
outage we encounter.
The Problem:
At least once every 24 hours the SQL Server begins disallowing new user
connections. When this happens the 2 CPUs begin to thrash. About 5
minutes later the error log begins to print out the following error
message: "Unable to connect. The maximum number of ''500'' configured user
connections are already connected. System Administrator can configure to
a higher value with sp_configure." No new connections can be made to the
server; however, the existing connections continue to function normally.
We never see anywhere near 500 user connections in the system (it tends
to average around 350 connections). If we begin to disconnect users the
server continues reporting that the maximum number of users is
connected. Eventually running queries thru an open connection will hang
and we have to resort to a hard reboot of the server as neither SQL
Server will not shutdown nor will the operating system.
The Server:
Compaq Proliant DL380 with dual 863 MHz. processors (x86 Family 6 Model
8 Stepping 3 GenuineIntel), 917,020 KB of physical memory, Microsoft
Windows 2000 Server (Version 5.0.2195 Service Pack 4 Build 2195), Total
Virtual Memory 3,138,688 KB, Page File Space 2,221,668 KB
SQL Server:
Microsoft SQL Server 6.50 - 6.50.479 (Intel X86). Some pertinent
configuration settings: memory - 244100 (in 2K units), user connections
? 500, RA worker threads ? 3, max worker threads ? 255
We found one reference to the above error message in the Microsoft
Knowledge Base and that refers to a condition where the server has 2GB
or more of physical memory with 1.5GB assigned to SQL Server. This does
not pertain to our situation. Have any of you ever encountered this
problem?
I appreciate your insights.
*** Sent via Developersdex http://www.developersdex.com ***
Don''t just participate in USENET...get rewarded for it!
推荐答案
InProcess（在******* @中） devdex.com）写道：
InProcess (in*******@devdex.com) writes:
让我首先说，是的，我们知道微软不再支持v6.5，而迁移到SQL2000可能会解决我们的问题。
实际上，迁移到SQL2000的时间是1到2个月，现在是QA
阶段。不幸的是，我们现在需要解决当前的6.5问题，因为它会严重影响我们遇到的每次中断时公司的主要收入来源。
鉴于
目前的情况，可能有充分的理由加速SQL2000迁移。听起来你是靠借来的时间生活的。
至少每24小时一次，SQL Server开始禁止新用户连接。当发生这种情况时，2个CPU开始抖动。大约5分钟后，错误日志开始打印出以下错误
消息：无法连接。 500配置的用户
连接的最大数量已经连接。系统管理员可以使用sp_configure配置为更高的值。无法与
服务器建立新的连接;然而，现有的连接继续正常运行。
我们从未在系统中看到任何接近500个用户连接（平均约350个连接）。如果我们开始断开用户连接，
服务器会继续报告已连接的最大用户数。最终通过打开的连接运行查询将挂起
我们不得不求助于服务器的硬重启，因为SQL
服务器既不会关闭也不会关闭操作系统。
当然很难说距离很远，但这里有一些我喜欢的东西：
br />
o如果你在这种情况下运行sp_lock，你看到任何有趣的spid
（这是否定的？）你也可以使用我的aba_lockinfo看看是否
有任何锁似乎与
spids无关。 （参见 http://www.sommarskog.se/sqlutil/aba_lockinfo.html 。）
o在
无法连接旁边的SQL Server错误日志中是否有任何有趣的内容？消息？ （堆栈转储，其他错误消息等）
o您有什么样的应用程序？ （基于Web /自定义，客户端库
等）
o您是否检查了CPU发生疯狂的状态是否与任何特定相关的
功能/查询？可能是SQL Trace
可以用来查找。
o你有没有用DBCC NEWALLOC和
CHECKDB检查数据库的损坏？
SQL Server：
Microsoft SQL Server 6.50 - 6.50.479（Intel X86）。一些相关的配置设置：内存 - 244100（以2K为单位），用户连接
- 500，RA工作线程 - 3，最大工作线程 - 255
Let me start by saying that, yes we know v6.5 is no longer supported by
Microsoft and that moving to SQL2000 might resolve our problem.
Actually the migration to SQL2000 is 1 to 2 months out and in the QA
phase now. Unfortunately we need to resolve the current 6.5 issue now
as it heavily impacts a major revenue stream for the company with every
outage we encounter.
There may be all reason to speed up that SQL2000 migration, given the
current situation. Sounds like you are living on borrowed time.
At least once every 24 hours the SQL Server begins disallowing new user
connections. When this happens the 2 CPUs begin to thrash. About 5
minutes later the error log begins to print out the following error
message: "Unable to connect. The maximum number of ''500'' configured user
connections are already connected. System Administrator can configure to
a higher value with sp_configure." No new connections can be made to the
server; however, the existing connections continue to function normally.
We never see anywhere near 500 user connections in the system (it tends
to average around 350 connections). If we begin to disconnect users the
server continues reporting that the maximum number of users is
connected. Eventually running queries thru an open connection will hang
and we have to resort to a hard reboot of the server as neither SQL
Server will not shutdown nor will the operating system.
It''s of course difficult to say that much on a distance, but here are
some things I like to know:
o If you run an sp_lock in such a situation, do you see any funny spids
(that is negative?) You could also use my aba_lockinfo to see if
there are any locks that does not really seem to correlate with the
spids. (See http://www.sommarskog.se/sqlutil/aba_lockinfo.html.)
o Is there anything interesting in the SQL Server error log beside the
"Unable to connect" messages? (Stack dumps, other error messages etc.)
o What sort of application do you have? (Web/custombased, client library
etc.)
o Have you examined whether the state where the CPUs goes mad are
correlated with any particular function/query? Possibly SQL Trace
could be used to find out.
o Have you checked the databases for corruption with DBCC NEWALLOC and
CHECKDB?
SQL Server:
Microsoft SQL Server 6.50 - 6.50.479 (Intel X86). Some pertinent
configuration settings: memory - 244100 (in 2K units), user connections
- 500, RA worker threads - 3, max worker threads - 255
在我看来，你可以将内存数量增加50％。如果你有'b
过程缓存在默认设置为30时，将其减少到5-10。
（该数字是总内存的百分比。）如果你有tempdb在
RAM或者有固定表，停止这样做。
并不是说我相信SQL Server的更多内存真的会有所帮助。但
可能会延迟坏状态的发生。
-
Erland Sommarskog，SQL Server MVP， es **** @ sommarskog.se
SQL Server SP3联机丛书
http://www.microsoft.com/sql/techinf。 ..2000 / books.asp
感谢您的回复，Erland。以下是您的
问题的答案：
我没有看到
$ b中锁定方面发生任何异常情况当我们遇到所描述的症状时，$ b数据库。还有其他
不寻常的症状，例如服务器报告说tran日志已经过了几分钟，然后几秒钟就会注册为
几乎是空的。我认为这只是由于服务器恐慌而产生的另一个错误消息
。
除了最大连接用户数之外错误中的消息
log，当我们尝试停止SQL
Server时，我们确实看到了多个堆栈转储。转储在特定地址报告异常地址，并且
发生访问冲突读同一个地址。堆栈转储
继续运行直到我们强制服务器通过关闭电源重新启动。
自上次发生以来我一直在运行SQLTrace
我最怀疑的应用程序。它是一个运行24 X 7
电子商务网站的网络应用程序。该应用程序在BEA weblogic服务器上运行。在
特别是我担心jnet驱动程序做了奇怪的事情，比如
打开的交易永远不会提交，除非它们从
$ b $终止b服务器端。我们必须经常这样做，以便转换日志可以是
转储。
我正在运行DBCC checkdb和newalloc的热备份数据库
现在。他们还没有完成但是checkdb报告了一个
损坏的表到目前为止错误2506.你认为这可能是我们所说的
症状表格损坏可能导致体验？
***通过Developersdex发送 http://www.developersdex.com ***
不要只是参加USENET ......获得奖励！
Thanks for your response, Erland. Here are the answers to your
questions:
I don''t see anything unusual going on in terms of locks within the
database when we experience the symptoms described. There are other
unusual symptoms such as the server reporting that the tran log is
nearly full one moment and then seconds later it registers as being
almost empty. I take this to be just another erroneous message created
as a result of the server panicking.
Besides the "maximum number of users connected" message in the error
log, we do see multiple stack dumps created when we try to stop the SQL
Server. The dump reports an Exception Address at a specific address and
"Access Violation occurred" reading the same address. The stack dumps
go on and on until we force the server to reboot by powering down.
Since the last occurrence I have been running a SQLTrace on the
application that I most suspect. It is a web app running a 24 X 7
e-commerce site. The application runs on BEA weblogic servers. In
particular I''m concerned by the jnet drivers that do strange things like
open transactions that never commit unless they are terminated from the
server side. We have to do that often so that the tran log can be
dumped.
I''m running DBCC checkdb and newalloc on a warm backup of the database
now. They haven''t completed yet but the checkdb has reported one
corrupt table so far with error 2506. Do you think it''s likely that the
symptoms we are experiencing could be caused by table corruption?
*** Sent via Developersdex http://www.developersdex.com ***
Don''t just participate in USENET...get rewarded for it!
T Dubya（ti*********@bigfoot.com）写道：
T Dubya (ti*********@bigfoot.com) writes:
我没有看到任何关于内部锁定的异常情况当我们遇到描述的症状时，数据库。还有其他一些不寻常的症状，比如服务器报告说tran日志几乎已经满了一会儿，然后几秒钟就会注册为几乎是空的。我认为这只是由于服务器恐慌而产生的另一个错误信息。
服务器并不是一个人性的开始，只是因为在
压力下。这些消息很可能确实表明了某些内容，并且
是你信息中的一条线索。
我想如果日志从完全变为空，是因为交易
日志被定期倾倒。我的意思是，你没有在检查点上使用截断日志
运行，是吗？
除了最大连接用户数之外错误
日志中的消息，我们确实看到当我们尝试停止SQL
服务器时创建的多个堆栈转储。转储在特定地址报告异常地址，并且发生访问冲突。读同一个地址。堆栈转储继续，直到我们通过关闭强制服务器重新启动。
那时事情已经失控了，我想。
自上次发生以来我一直在
应用程序上运行SQLTrace我最怀疑的。它是一个运行24 X 7
电子商务网站的网络应用程序。该应用程序在BEA weblogic服务器上运行。特别是我很担心jnet驱动程序会做一些奇怪的事情，比如
打开的事务，除非从服务器端终止，否则永远不会提交。我们必须经常这样做，以便转储日志。
啊，这是件好事。那些未提交的交易很可能是痛苦的一部分。有一个可怕的问题：大多数客户端库
的默认超时为30秒。假设客户端调用开始事务的存储的
过程。然后这个过程被阻止（或者b / b $ b）只是遇到困难的查询，并且超时到期。在这种情况下，
交易不会被回滚。 SQL Server不知道
知道超时，它只知道客户端取消了
操作。在这种情况下，客户必须断开连接，或提交
aIF @@ trancount> 0 ROLLBACK TRANSACTION"。 （后者是可取的，
因为连接池可能会导致断开连接。）
这些开放式交易也可能是translog的原因
填满。 translog永远不会被截断超过最早的开放
交易。
我现在正在数据库的热备份上运行DBCC checkdb和newalloc。他们还没有完成但是checkdb到目前为止报告了一个损坏的表，错误2506.您认为我们遇到的症状很可能是由表损坏引起的吗？
I don''t see anything unusual going on in terms of locks within the
database when we experience the symptoms described. There are other
unusual symptoms such as the server reporting that the tran log is
nearly full one moment and then seconds later it registers as being
almost empty. I take this to be just another erroneous message created
as a result of the server panicking.
A server is not a human that start to things whimsly, just because under
pressure. Those messages very likely does indicate something, and there
is a clue further down in your message.
I guess that if the log goes from full to empty, is because the transaction
log is dumped regularly. I mean, you are not running with "truncate log
on checkpoint", are you?
Besides the "maximum number of users connected" message in the error
log, we do see multiple stack dumps created when we try to stop the SQL
Server. The dump reports an Exception Address at a specific address and
"Access Violation occurred" reading the same address. The stack dumps
go on and on until we force the server to reboot by powering down.
By then things have already gone out of control, I guess.
Since the last occurrence I have been running a SQLTrace on the
application that I most suspect. It is a web app running a 24 X 7
e-commerce site. The application runs on BEA weblogic servers. In
particular I''m concerned by the jnet drivers that do strange things like
open transactions that never commit unless they are terminated from the
server side. We have to do that often so that the tran log can be
dumped.
Ah, here is something. Those uncommitted transactions can very well be
part of the misery. There is a terrible gotcha: most client libraries
has a default timeout of 30 seconds. Say that a client call a stored
procedure that begins a transaction. Then this process gets blocked (or
just get stuck with a difficult query), and the timeout expires. The
transaction is *not* rolled back in this situation. SQL Server does not
know about the timeout, it only knows that the client cancelled the
operation. The client must in this situation either disconnect, or submit
a "IF @@trancount > 0 ROLLBACK TRANSACTION". (The latter is preferrable,
since disconnection could be delayed due to connection pooling.)
These open transactions are also likely to be the reason for the translog
filling up. The translog can never be truncated past the oldest open
transaction.
I''m running DBCC checkdb and newalloc on a warm backup of the database
now. They haven''t completed yet but the checkdb has reported one
corrupt table so far with error 2506. Do you think it''s likely that the
symptoms we are experiencing could be caused by table corruption?
并非一切都难以置信。特别是，如果这是突然出现的事情。
。我无法在
工作中访问6.5。在SQL 2000文档中，2506被称为无法在数据库''％。* ls''中找到一个表或
对象名''％。* ls''。 ，这看起来不是很好
对我好。您可以在联机丛书中搜索2506，看看是否有描述如何处理此错误的
a主题。
最后，腐败很可能源于硬件问题。
-
Erland Sommarskog，SQL Server MVP， es **** @ sommarskog.se
SQL Server SP3联机丛书 http://www.microsoft.com/sql /techinf...2000/books.asp
It is not all implausible. Particularly, if this is something that
started to appear all of a sudden. I don''t have access to the 6.5 at
work. In the SQL 2000 docs, 2506 is said to be "Could not find a table or
object name ''%.*ls'' in database ''%.*ls''.", which does not look very
good to me. You can search for 2506 in Books Online, to see if there is
a topic that describes how to handle this error.
And, in the end, corruption can very well have its origin in hardware
problems.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
这篇关于MS SQL 6.5无法连接。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
