解决SQLServer事物日志过大的方法

在平时的运维中，DBA会经常遇到SQLServer日志文件过大导致磁盘空间不足的情况，这时候就需要把日志文件收缩一下来释放磁盘空间，下面介绍了收缩日志文件的方法一

日志文件过大导致磁盘空间不足的情况，这时候就需要把日志文件收缩一下来释放磁盘空间，下面介绍了收缩日志文件的方法

 

一．要进行日志文件收缩首先要把事物日志截断。

正常情况下SQL Server的事物日志自动截断方法分以下两种情况

1.简单恢复模式下，在检查点之后发生。

2.在完整恢复模式或大容量日志恢复模式下，如果自上一次备份后生成检查点，则在日志备份后进行截断（除非是仅复制日志备份）。

查看恢复模式的方法如下

在命令下查看

recovery_model_desc

   FROM sys.databases

数据库名' ;

查看效果如图所示

 

 

 

（1）如果是在完整或者大容量日志的恢复模式下，那我们就要查看一下近期是否做过完整的日志以确定日志是否被自动截断过

（2）关于数据库的检查点，网站空间，检查点会根据系统的恢复间隔自动生成检查点

我们可以使用命令来查看截断被延迟的原因

log_reuse_wait,log_reuse_wait_desc

   FROM sys.databases

数据库名';

 

的值来查找原因

 

的值的解释 此表来源于微软知识库：(v=sql.90).aspx

log_reuse_wait 值

log_reuse_wait_desc 值

 

说明

0

NOTHING

 

当前有一个或多个可重用的虚拟日志文件。

1

CHECKPOINT

 

自上次日志截断之后，尚未出现检查点，或者日志头部尚未跨一个虚拟日志文件移动（所有恢复模式）。

这是日志截断延迟的常见原因。有关详细信息，请参阅检查点和日志的活动部分。

2

LOG_BACKUP

 

要求日志备份将日志标头前移（仅适用于完整恢复模式或大容量日志恢复模式）。

日志备份不会阻止截断。

日志备份完成后，日志标头将前移，并且一些日志空间可能会变为可重新使用。

3

ACTIVE_BACKUP_OR_RESTORE

 

数据备份或还原正在进行（所有恢复模式）。

部分。

4

ACTIVE_TRANSACTION

 

事务处于活动状态（所有恢复模式）。

部分。

延迟的事务.

5

DATABASE_MIRRORING

 

数据库镜像暂停，服务器空间，或者在高性能模式下，镜像数据库明显滞后于主体数据库（仅限于完整恢复模式）。

部分。

6

REPLICATION

 

在事务复制过程中，与发布相关的事务仍未传递到分发数据库（仅限于完整恢复模式）。

部分。

7

DATABASE_SNAPSHOT_CREATION

 

正在创建数据库快照（所有恢复模式）。

这是日志截断延迟的常见原因，通常也是主要原因。

8

LOG_SCAN

 

正在进行日志扫描（所有恢复模式）。

这是日志截断延迟的常见原因，免备案空间，通常也是主要原因。

9

OTHER_TRANSIENT

 

此值当前未使用。

   

二．收缩日志文件

在事物日志进行截断之后，那我们就可以把日志文件进行收缩

可以使用DBCC SHRINKFILE使用命令进行收缩

。

 

执行完命令后查看日志文件是否缩小，日志文件收缩后不会比虚拟日志文件小

 

 

 

命令

更改数据库恢复模式的命令如下

 

更改为完全模式

bulk_logged ; --更改为大容量日志模式

更改为简单模式

 

执行效果如图所示

 

注意事项：如果更改为简单模式，那以后将无需事物日志备份，但是一定要确保的定期执行数据库备份来确保数据的完整性。

如果更改为完整模式或者大容量日志模式，那一定首先进行一次完整的数据库备份以启动日志链，并且设置任务计划来定期的日志备份

本文出自 “不明飞行物” 博客，请务必保留此出处