分享今天在客户那里遇到的SQLSERVER连接超时以及我的解决办法

分享今天在客户那里遇到的SQLSERVER连接超时以及我的解决办法 客户的环境：SQLSERVER2005，WINDOWS2003 SP2 32位 这次发生连接超时的时间是2013-8-5 21:34分 在 2013-8-5 19:25分左右也发生过一次 下面的SQL ERRORLOG截取的是 19:18分~22:08分时间段之内的关

分享今天在客户那里遇到的SQLSERVER连接超时以及我的解决办法

客户的环境：SQLSERVER2005，WINDOWS2003 SP2 32位

这次发生连接超时的时间是2013-8-5 21:34分

在2013-8-5 19:25分左右也发生过一次

下面的SQL ERRORLOG截取的是19:18分~22:08分时间段之内的关键日志！！！

这个问题昨天出现了一次，今天出现了两次，起初没有认真对待，认为重启一下机器就没事了

我认为是CPU占用高或者内存占用高或者磁盘读写高，因为客户的机器安装了360安全卫士，

当时客户反映360报“磁盘读写高”

因为我当时看了任务管理器，windows日志，各项信息都很正常，CPU占用在30%左右，虚拟主机，内存在800MB左右

360那里也没有看出磁盘读写特别高（注意：当时我是在客户重启了机器之后看的!!!!!!!!!!!!）

-------------------------------------华丽的分割线-----------------------------

入手

谁知道今晚又出现第三次，使得我要从酒店又跑回客户那里

我去到客户那里第一时间先看windows日志和sql errorlog

由于在SQL ERRORLOG里看到很多想尝试登陆数据库而失败的日志，

而且看到Windows eventlog里有好几次SQL服务意外关闭

的记录，美国空间，但是SQLSERVER是没有shutdown 电脑的SQL语句的，所以肯定不是外人所为

而且连接超时也不是经常出现，所以排除了黑客捣蛋的可能性（客户的电脑设置了防火墙，sa用户禁用，路由器只开了1433端口）

电脑也做了一些安全设置的

----------------------------------华丽的分割线-------------------------------

再仔细分析

由于我们的应用系统其中一个是用delphi写的，而且用ODBC来连接SQLSERVER，而且这个应用

是一打开就马上open connection，直到退出这个应用为止，不像C# 执行完毕就close（）

所以如果这个应用断开与数据库的连接就会影响到客户的业务运行，当时操作系统弹出“超时对话框”的时候

点击我们的系统很慢几乎没有反应，香港虚拟主机，响应很迟钝，我重启电脑，看一下windows日志

我在客户的机器里截了一些图片

再看一下SQL ERRORLOG

下面这个是重启电脑之后，SQLSERVER启动的SQLERRORLOG

:.5000.00 (Intel X86) : Microsoft Corporation 4 Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2) :18:09.31 Server (c) 2005 Microsoft Corporation. :18:09.31 Server All rights reserved. :18:09.31 Server Server process ID is 2744. :18:09.33 Server Authentication mode is MIXED. :. :::17:27 (UTC). This is an informational message only; no user action is required. :18:09.33 Server Registry startup parameters: :18:09.33 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf :18:09.33 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG :18:09.33 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf :18:09.36 服务器 SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required. :18:09.36 服务器 Detected 2 CPUs. This is an informational message; no user action is required. :Lock Owner blocks per node. This is an informational message only. No user action is required. :18:10.05 服务器 Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required. : functionality, please start this service. :18:10.46 服务器 Database mirroring has been enabled on this instance of SQL Server. :. :(1). This is an informational message only. No user action is required. :(1). This is an informational message only. No user action is required. :(1). This is an informational message only. No user action is required. :. :18:10.92 spid5s The resource database build version is 9.00.5000. This is an informational message only. No user action is required. :. This is an informational message only. No user action is required. :. :18:11.42 spid8s Clearing tempdb database. :. :18:12.34 spid11s The Service Broker protocol transport is disabled or not configured. :18:12.34 spid11s The Database Mirroring protocol transport is disabled or not configured. :18:12.38 spid11s Service Broker manager has started. :18:12.60 服务器 A self-generated certificate was successfully loaded for encryption. :. :. :. :. :18:12.64 服务器 Dedicated admin connection support was established for listening remotely on port 1434. :18:12.70 服务器 The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies. :18:12.70 服务器 SQL Server is now ready for client connections. This is an informational message; no user action is required. :. :. :(4). This is an informational message only. No user action is required. :(4). This is an informational message only. No user action is required. :(4). This is an informational message only. No user action is required. :(complete (approximately 4 seconds remain). This is an informational message only. No user action is required. :(complete (approximately 0 seconds remain). This is an informational message only. No user action is required. :(complete (approximately 0 seconds remain). This is an informational message only. No user action is required. :(complete (approximately . This is an informational message only. No user action is required. :(complete (approximately . This is an informational message only. No user action is required. :(complete (approximately . This is an informational message only. No user action is required. :(5). This is an informational message only. No user action is required. :(complete (approximately . This is an informational message only. No user action is required. :(complete (approximately . This is an informational message only. No user action is required. :(5). This is an informational message only. No user action is required. :(5). This is an informational message only. No user action is required. :18:30.73 spid5s Recovery is complete. This is an informational message only. No user action is required. :version extended stored . This is an informational message only; no user action is required.