SQL 2008 R2企业版的数据库连接问题

Question

安装平台是win2008 R2 企业版，安装的是SQL 2008 server R2 打了sp1补丁 ，做了windows故障转移群集，SQL群集。

现在遇到如下问题：客户端用程序连接SQL数据库会断开，查询时会SQL数据库断开，前置服务器上打开上面的网页，显示数据库连接有问题。连接不上是经常性的，平均下1小时可能会发生一次，这里先排除网络问题，因为在SQL数据库断开的时候，从客户端到服务器，客户端到数据库，服务器到数据库网络都是通的，而且在客户端以及数据库节点上，telnet到数据库集群的IP 1433，端口，是OK的。

在数据库日志里：有很多sort warnings ，hash warnings，还有个错误日志：The client was unable to reuse a session with SPID 75, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message

还有：
SQL 2008错误: 18056，严重性: 20，状态: 29 解决

 

 

 

 

Answer 1

Sounds client side issue, maybe connection pool doesn't have free connection left. By the way, does sql2k8 r2 have sp1?

Answer 2

Sounds client side issue, maybe connection pool doesn't have free connection left. By the way, does sql2k8 r2 have sp1?

YES 打了SP1补丁了。还发现1个问题：因为做的是群集，节点1上，发现C盘，只剩下134M空间，然后，我把SQL 切换到了节点2上，过了一会节点1上的C盘剩余空间变成了35G，节点2上依然是35G。顺便问下，如是connection pool doesn't have free connection，我改怎么设置呢？

我这里还有些发生错误的截图，以及logs。该怎么传到论坛上？感谢

Answer 3

没用过集群，如果需要上传图片，可以先用live账号传到skydrive下，再引用地址即可。

http://skydrive.live.com/

---

助人等于自助。[e-mail]

Answer 4

没用过集群，如果需要上传图片，可以先用live账号传到skydrive下，再引用地址即可。

http://skydrive.live.com/

---

助人等于自助。[e-mail]

3Q

Answer 5

Sounds client side issue, maybe connection pool doesn't have free connection left. By the way, does sql2k8 r2 have sp1?

YES 打了SP1补丁了。还发现1个问题：因为做的是群集，节点1上，发现C盘，只剩下134M空间，然后，我把SQL 切换到了节点2上，过了一会节点1上的C盘剩余空间变成了35G，节点2上依然是35G。顺便问下，如是connection pool doesn't have free connection，我改怎么设置呢？

我这里还有些发生错误的截图，以及logs。该怎么传到论坛上？感谢

You mean Cumulative Update package 1? There's no sp1 yet. Do you use any temp files on c:\? Clustered sql shouldn't use it. Regarding connection pool, you need change it on client side.

Answer 6

Sounds client side issue, maybe connection pool doesn't have free connection left. By the way, does sql2k8 r2 have sp1?

问题还是没解决，我先把errorlog 贴出来，请求分析下：                                                                                                                     2010-08-16 09:22:46.86 spid78      错误: 18056，严重性: 20，状态: 29。
2010-08-16 09:22:46.86 spid78      The client was unable to reuse a session with SPID 78, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
2010-08-16 09:24:17.57 spid108     错误: 18056，严重性: 20，状态: 29。
2010-08-16 09:24:17.57 spid108     The client was unable to reuse a session with SPID 108, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
2010-08-16 09:26:50.93 spid60      Configuration option 'remote query timeout (s)' changed from 600 to 0. Run the RECONFIGURE statement to install.
2010-08-16 09:26:50.94 spid60      FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
2010-08-16 09:26:51.00 spid60      SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2010-08-16 09:26:53.71 spid60      SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2010-08-16 09:26:53.72 spid60      SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2010-08-16 10:21:38.96 spid90      错误: 18056，严重性: 20，状态: 29。
2010-08-16 10:21:38.96 spid90      The client was unable to reuse a session with SPID 90, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
2010-08-16 10:26:56.96 spid56      错误: 18056，严重性: 20，状态: 29。
2010-08-16 10:26:56.96 spid56      The client was unable to reuse a session with SPID 56, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
2010-08-16 10:36:17.32 登录          错误: 18456，严重性: 14，状态: 5。
2010-08-16 10:36:17.32 登录          Login failed for user 'sp.gov'. 原因: 找不到与所提供的名称相匹配的登录名。 [客户端: 190.117.1.12]

Answer 7

Spid 29 is system process, find out details of it in sysprocesses. And ensure server has sql login for 'sp.gov'.

Answer 8

不知道这个连接问题诊断工具是否能帮上忙SQL Server Authentication Troubleshooter

A new tool to help investigate ‘Login Failed’ errors in SQL Server has been recently implemented and published on CodePlex: http://ssat.codeplex.com/

The tool is implemented in C# and uses xEvents to capture “Login Failed” errors. It also uses security ring buffer information (from sys.dm_os_ring_buffers) to retrieve error related information and do the analysis. More details are @ http://ssat.codeplex.com/

---

Mark as Answer if it helps. This posting is provided "AS IS" with no warranties, confers no rights.

Answer 9

Thank you.问题已经解决了，经过各种测试，是SQL语句中的delete 触发了 IPS设备的规则库，把SQL连接断开了。多谢各位。