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

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

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

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

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

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

查看恢复模式的方法如下

在命令下查看

语法：SELECTname, recovery_model_desc

FROM sys.databases

WHERE name = '数据库名';

查看效果如图所示

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

(2)关于数据库的检查点，检查点会根据系统的恢复间隔自动生成检查点

(3)有时候因素导致事物日志截断会被延迟,我们可以使用命令来查看截断被延迟的原因

命令语法：SELECTname,log_reuse_wait,log_reuse_wait_desc

FROM sys.databases

WHERE name = '数据库名';

我们可以根据结果中输出的log_reuse_wait和log_reuse_wait_desc的值来查找原因

下表为log_reuse_wait和log_reuse_wait_desc的值的解释此表来源于微软知识库：http://technet.microsoft.com/zh-cn/library/ms345414(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

事务处于活动状态(所有恢复模式)。

·在日志备份开始时，可能存在长时间运行的事务。在这种情况下，释放空间可能需要进行其他日志备份。有关详细信息，请参阅本主题后面的“长时间运行的活动事务”部分。

·事务将延迟(仅适用于SQL Server 2005 Enterprise Edition及更高版本)。“延迟的事务”实际上是其回滚由于某些资源不可用而受阻的活动事务。有关导致事务延迟的原因以及如何使它们摆脱被延迟状态的信息，请参阅.

5

DATABASE_MIRRORING

数据库镜像暂停，或者在高性能模式下，镜像数据库明显滞后于主体数据库(仅限于完整恢复模式)。

有关详细信息，请参阅本主题后面的“数据库镜像与事务日志”部分。

6

REPLICATION

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

有关详细信息，请参阅本主题后面的“事务复制与事务日志”部分。

7

DATABASE_SNAPSHOT_CREATION

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

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

8

LOG_SCAN

正在进行日志扫描(所有恢复模式)。

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

9

OTHER_TRANSIENT

此值当前未使用。

二．收缩日志文件

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

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

下图是把以下示例将test用户数据库中名为test_log的数据文件的大小收缩到10 MB。

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

通常情况下，日志没有被收缩都是因为日志文件未被截断造成的。还有一个办法是把将数据库恢复模式设置为“SIMPLE”模式后再次运行DBCC SHRINKFILE命令

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

ALTERDATABASE数据库名SETRECOVERYFULL;--更改为完全模式

ALTERDATABASE数据库名SETRECOVERYbulk_logged ;--更改为大容量日志模式

ALTERDATABASE数据库名SETRECOVERY;--更改为简单模式

执行效果如图所示

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

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