SqlServer日志增长过快应对策略

日志增长过快可能的几个原因

数据库是完整模式，但是并没有定期的进行日志备份。日志备份可以截断事务，可以使得空间重用。
解决这个问题，只需做好日志定时备份的计划作业就行。
注意，日志备份只备份已提交的事务，只有日志备份才能截断日志，使得日志空间可以重用
有事务长时间没有提交
由于开发人员的粗心大意，没有把已经运行完成的事务提交，日志一直在记录，导致很大。
解决这个问题，查找出已经运行完成但没有提交的事务，kill掉此事务即可。
有很大的事务正在运行
这个事务很大，一直不停的在记录大量的日志，导致日志增大。
解决这个问题，看看在语句和业务逻辑上看看能否优化的余地，运行很大的事务能否分事务运行。
当声明式事务出现异常时，没有显示的回滚。
解决方法就是当事务出现异常时一定要显式回滚。
线程池中存在没有关闭的事务。

sqlserver的三种模式

Simple 简单恢复模式
在Simple模式下，SQL Server会在每次checkpoint或backup之后自动截断log，也就是丢弃所有的inactive log records，仅保留用于实例启动时自动发生的instance recovery所需的少量log，这样做的好处是log文件非常小，不需要DBA去维护、备份log，但坏处也是显而易见的，就是一旦数据库出现异常，需要恢复时，最多只能恢复到上一次的备份，无法恢复到最近可用状态，因为log丢失了。
如果需要压缩数据库日志（Shrink语句），将数据库模式切换到简单恢复模式后压缩率才是最高的，如果你的数据库在完整恢复模式或大容量日志恢复模式下采用日志压缩，压缩后的日志大小并不会很理想。
Full 完整恢复模式
和Simple模式相反，Full模式下SQL Server不主动截断log，只有备份log之后，才可以截断log，否则log文件会一直增大，直到撑爆硬盘，因此需要部署一个job定时备份log。Full的好处是可以做point-in-time恢复，最大限度的保证数据不丢失，一般用于critical的业务环境里。缺点就是DBA需要维护log，增加人员成本。
Bulk-logged 大容量日志恢复
Bulk-logged模式和full模式类似，唯一的不同是针对以下Bulk操作，会产生尽量少的log：

1) Bulk load operations (bcp and BULK INSERT). 
2) SELECT INTO. 
3) Create/drop/rebuild index

众所周知，通常bulk操作会产生大量的log，对SQL Server的性能有较大影响，bulk-logged模式的作用就在于降低这种性能影响，并防止log文件过分增长，但是它的问题是无法point-in-time恢复到包含bulk-logged record的这段时间。 Bulk-logged模式的最佳实践方案是在做bulk操作之前切换到bulk-logged，在bulk操作结束之后马上切换回full模式。

三种模式的简单补充

简单恢复：无日志备份。自动回收日志空间以减少空间需求，实际上不再需要管理事务日志空间。最新备份之后的更改不受保护。在发生灾难时，这些更改必须重做。只能恢复到备份的结尾。
完整恢复：需要日志备份。数据文件丢失或损坏不会导致丢失工作。可以恢复到任意时点（例如应用程序或用户错误之前）。
大容量日志恢复：需要日志备份。是完整恢复模式的附加模式，允许执行高性能的大容量复制操作。通过使用最小方式记录大多数大容量操作，减少日志空间使用量。
如果在最新日志备份后发生日志损坏或执行大容量日志记录操作，则必须重做自该上次备份之后所做的更改。否则不丢失任何工作。可以恢复到任何备份的结尾。不支持时点恢复。

日志的截断和收缩

SQL Server中事务日志的作用：持续记录数据库所有的事务和这些事务对数据库所做的修改;一旦数据库出现灾难事件，就需要事务日志来进行近期数据的恢复操作。但是，在此条件下受益的同时也要付出相应的代价：在活动数据库中，事务日志会消耗大量的存储空间，假如你不做任何的干预，事务逻辑日志将会一直增长，直到塞满存储这些日志文件的所有可用空间。下面我们来详细介绍SQL Server提供的两个用来平衡事务日志对空间巨大需求的操作：收缩事务日志和截断事务日志。

日志截断
截断事务日志操作就是清除事务日志文件中的非活动记录。在一般的情况下，SQL Server能够自动执行截断操作，不需要人工干预管理。截断的频率取决于数据库的使用程度。你每进行一次完整恢复模式或大容量日志恢复模式的数据库备份，SQL Server就会截断一次事务日志。如果是在简单恢复模式下(不能还原事务日志)，SQL Server会在每个检查点之后截断事务日志。
你也可以通过间接的方式手动驱使SQL Server执行事务日志截断操作，需要运行备份操作，不过你可以给SQL Server下指示，表明你只想执行事务日志的备份操作，不做执行其他任何操作。通过以下的T-SQL命令仍然可以达到上述效果：
BACKUP LOG WITH TRUNCATE_ONLY;
日志收缩
截断日志虽然确实从日志文件中清除了事务，但它并不会真正的减小物理日志文件的大小。SQL Server希望事务日志最终会扩展到其截断前的大小，所以截断不会释放已经分配给日志的硬盘空间。如果你的日志在某一时刻人为地扩展到某个大小，却再也无法恢复到这个大小的话可就麻烦大了。
在这种情况下，要释放硬盘物理空间做其他用途，就要手动进行事务日志文件收缩操作。你可以使用下面的T-SQL命令实现日志文件的收缩：
DBCC SHRINKFILE(,)
上面命令中的desired_shrink_size指的是你想要回收的硬盘空间大小(以MB为单位)。你可以在执行完事务日志截断操作之后立即回收大部分的磁盘空间。

总结-解决方案

针对几种不同的情况，可以采取不同的方式对日志进行操作：
1. 修改sqlserver日志恢复模式为简单模式：
找到你想修改的数据库右键 > 属性 > 左侧选项既可看到。

修改日志恢复模式

注意，简单模式无日志备份，最新备份之后的更改不受保护。在发生灾难时，这些更改必须重做。只能恢复到备份的结尾。
2. 定时收缩日志

SQL语句

USE [master]
GO
ALTER DATABASE [数据库名称] SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE [数据库名称] SET RECOVERY SIMPLE --简单模式
GO

USE [数据库名称]
GO
DBCC SHRINKFILE (N'LXCX_log' , 1, TRUNCATEONLY) 　　--日志文件逻辑名称，可点击数据库->属性->文件查看
GO

USE [master]
GO
ALTER DATABASE [数据库名称] SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE [数据库名称] SET RECOVERY FULL --还原为完全模式
GO

DBCC SHRINKFILE语句语法参照https://www.cnblogs.com/gered/p/9366256.html

设置定时任务
https://www.cnblogs.com/xihong2014/p/4126485.html

3. 日志的备份
必要性：参照 https://docs.microsoft.com/zh-cn/sql/relational-databases/backup-restore/transaction-log-backups-sql-server?view=sql-server-ver15
如何执行：参照 https://docs.microsoft.com/zh-cn/sql/relational-databases/backup-restore/back-up-a-transaction-log-sql-server?view=sql-server-ver15
也可以设置定时任务进行定时数据库事务日志备份，每次备份后数据库会自动截断日志。日志截断后再收缩日志。

4. 检查事务状态

其它

解决事务日志已满的问题
 sql 日志文件(.ldf) 增长太快,请问什么原因? 有什么办法?
导致日志截断延迟的因素
 SqlServer性能检测和优化工具使用详细