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

  • 问题

  • 安装平台是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 解决

     

     

     

     

    2010年8月10日 3:25

答案

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

    • 已标记为答案 Mog Liang 2010年9月7日 1:42
    2010年8月20日 1:18

全部回复

  • Sounds client side issue, maybe connection pool doesn't have free connection left. By the way, does sql2k8 r2 have sp1?
    2010年8月10日 3:43
  • 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。该怎么传到论坛上?感谢

    2010年8月10日 5:20
  • 没用过集群,如果需要上传图片,可以先用live账号传到skydrive下,再引用地址即可。

    http://skydrive.live.com/


    助人等于自助。3w@live.cn
    2010年8月10日 6:25
  • 没用过集群,如果需要上传图片,可以先用live账号传到skydrive下,再引用地址即可。

    http://skydrive.live.com/


    助人等于自助。3w@live.cn

    3Q
    2010年8月10日 7:02
  • 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.
    2010年8月10日 14:29
  • 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]
    2010年8月16日 3:11
  • Spid 29 is system process, find out details of it in sysprocesses. And ensure server has sql login for 'sp.gov'.
    2010年8月16日 3:15
  • 不知道这个连接问题诊断工具是否能帮上忙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.
    2010年8月16日 9:00
  • Thank you.问题已经解决了,经过各种测试,是SQL语句中的delete 触发了 IPS设备的规则库,把SQL连接断开了。多谢各位。

    • 已标记为答案 Mog Liang 2010年9月7日 1:42
    2010年8月20日 1:18