[SQL Server] 数据库日志文件自动增长导致连接超时的分析

豆包ko

1、现象、问题描述
       客户反映某客户端登陆不了，客户端程序日志显示“连接数据库超时”；检查对应的数据库服务器，日志显示“Autogrow of file '某数据库日志文件' in database '某数据库' was cancelled by user or timed out after 2391 milliseconds.  Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.”

2、关键过程分析
根据数据库日志显示，是数据库事务日志文件已满，在自动增长的时候，连接会话已主动超时断开。
1.         SQL Server需要保留以下几种类型的日志
a)         所有没有经过"检查点"的日志记录；（数据库默认1分钟左右做一次检查点）
b)        所有没有提交的事务所产生的日志记录，以及在它们之后的所有日志记录(占用日志较大空间的操作有：基于事务日志的数据库恢复；创建/重建索引；操作大量数据；程序打开了游标却未取走数据)；
c)        所有要做备份的日志记录；（非“简单”模式下，日志都被认为是需要备份的）
d)        有其他需要读取日志的数据库功能模块. (事务型复制和数据库镜像，在它们没有读取日志之前，所有日志均保留)；

2.         事务日志文件在自动增长空间时，需要将新增加的空间全部“置0”（数据文件默认不需要），期间会产生大量闩锁并使得响应速度变慢。(设置有“超时退出”的客户端程序，此时一般就无法正常连接；数据库程序默认未设“超时”，所以一般会耐心等待日志文件增长完成。)

3、结论、解决方案及效果
结论：问题发生前，事务日志文件已经达25G，在自动增加10%空间(2.5G)的时候，游戏程序连接时等待超时。
解决方法为：
定期清空不需要备份的事务日志(建议在“完全备份/差异备份”执行之前清空日志)；
BACKUP LOG 数据库名 WITH WITH NO_LOG
或者将数据库选项设为“简单模式”(SQL 2008仅支持该方法，不支持手动清空日志)
4、经验总结、预防措施和规范建议
数据库的自动增长，建议设定为“**MB”，减少使用“**%”；
对不需要备份日志的数据库，在备份数据库前清空日志，或定期清空；
将每周“日志增长大小”监控，改为每日“日志当前大小及使用率”监控，超过(“5GB和80%时报警”)；
　　5、备注
参考 http://support.microsoft.com/default.aspx?scid=kb;en-us;2091024&sd=rss&spid=2855
http://msdn.microsoft.com/en-us/library/ms175935.aspx