经常, 我们会被过快增长的数据库事务日志Transaction Log而困扰, 如果我们没有正确及时的处理, 可能会造成数据库交易无法进行, 服务器磁盘空间占光等问题.
在SQL Server的使用过程中, 我经常帮助用户和数据库的维护人员处理日志Transaction Log太大后造成的系统瘫痪的问题. 其实这个问题很容易避免. 今天我给大家分享下, 是什么造成了日志增长过大的问题. 和如何避免这种问题再次发生.

该文章的语句适用于SQL Server 2015 及其以后的版本
每一个数据库至少有两个文件: 一个是数据文件(Data file), 一个是事务日志文件(Transaction Log file). 数据文件存储系统和用户的数据, 而事务日志问题存储所有的交易和由这些交易产生的数据库的变更. 随着时间的推移, 越来越多的数据库交易日志被保留下来.

如果你的数据的恢复模式 (Recovery Model) 设为Simple, 那么当交易完成, 系统生成检查点(Checkpoint) 后, 之前的事务日志会被标记为无效, 从而被数据库自动截断.

事务日志备份 Transaction Log Backups
如果你的数据库恢复模式 (Recovery Model) 设为 Full 或 Bulk-Logged (通常数据库的默认恢复模式时 Full), 所以你必须创建日志备 (Log Backup)来控制的事务日志 (注意, 日志备份之前一定要先做全备(Full Backup)).

你可以用一下语句查看你数据库的恢复模式

SELECT name, recovery_model_desc
FROM sys.databases
如果你没有建立日志备份, 用完并失效的日志所占用的空间不会被重用, 那就意味着你的事务日志文件随着交易的发生会越累越大. 你可以通过 database maintenance plan来建立一个定期的日志备份. 一旦有了日志的备份, 占用的日志空间就可以释放出来被下一个交易利用, 这样日志文件就会在控制在一个一个合理的范围.

如果事务日志备份出了问题, 也可能导致事务日志过大, 你可以通过一下语句来查看当前备份的情况.

SELECT name, log_reuse_wait_desc
FROM sys.databases
长时间的活动交易 Long-Running Active Transactions
长时间的持续运行的交易也会阻止事务日志的释放. 有些交易可能需要等待其他用户输入或者其他的资源, 导致交易(Transaction)一直打开, 你可以通过一下语句查看打开的交易.

DBCC OPENTRAN
返回的结果有该交易的会话ID, SPID (Session_id), 你可以 通过一下语句查看该会话执行的详细信息.

EXECUTE sp_who2 spid
如果你想知道更详细的语句, 可以再通过DBCC INPUTBUFFER()来看到完整的执行语句.

DBCC INPUTBUFFER(spid)
日志复制 Transactional Replication
如果你开启的日志复制功能, 基于日志复制的原理, 主Database事务日志在被成功复制到 distributor之前也是不能被截断的. 如果 distributor 端有问题, 导致QQ账号买卖事务日志复制不成功.

你可以通过之前的 DBCC OPENTRAN 检查是否有未完成的交易没有复制成功, 或被打开很长时间.

数据库镜像 Database Mirroring
数据库镜像和日志复制有相似的问题, 如果事务日志不能提交的从数据库 Mirror端, 主数据库Principal的日志也是不是被释放的. 如果那样的话, 你可能需要断掉Mirror, 截断主数据的日志, 然后重做Mirror.

磁盘空间 Disk Space
如果你的服务器磁盘空间不足, 导致事务日志有问题, 但是截断或操作日志有需要额外的空间, 你可能***的的办法在磁盘空间足够的驱动器上新建一个日志文件, 然后进行后续的截断操作.

ALTER DATABASE your_db_name Add LOG FILE
收缩日志文件 Shrinking the File
一旦你定位了日志增长的问题, 并解决后, 该是收拾"烂摊子", 把日志重新收回到合理值.

你可以用SQL Server Management Studio 右键你的数据库 --> Tasks --> Shrink --> Select Log Files. 或者你用下面的TSQL

SELECT name
FROM sys.database_files
WHERE type_desc = 'LOG'
找到Log File之后, 可以用一下DBCC命令收缩日志到制定的大小, 本例为1G

DBCC SHRINKFILE ('db_log_file_name', 1000) -- Target 1G
写在最后
我列出来一些常见的问题导致事务日志过大的问题, 和常用的解决办法. 除了上述方法, 你***对的关键数据库的磁盘等关键指标加上阈值报警, 这样才有时间在事情来变得更糟之前解决这些问题.

希望以上能够帮到你.

Make work and life simple.