---先备份数据库(含日志文件)

 

usemyhis

 

go

 

backupdatabase myhis to disk='d:\myhis_rzbak'

 

go

 

 

 

 

 

---设为简单恢复模式

 

use[master]

 

go

 

alterdatabase myhis set recovery simple with no_wait

 

go

 

alterdatabase myhis set recovery simple

 

go

 

 

 

 

 

---收缩数据库日志文件为8M

 

usemyhis

 

go

 

dbccshrinkfile(myhis_log,8)

 

go

 

 

 

---重新设为完整恢复模式

 

usemaster

 

go

 

alterdatabase myhis set recovery full with no_wait

 

go

 

alterdatabase myhis set recovery full

 

go

 

 

 

 

 

SQL Server 2008 事务日志物理文件尺寸无法减小的解决办法(含日志收缩(shrink)技巧)

 

作者:宋林

 

发现有的数据库日志文件太大,无论如何收缩执行几次SQL语句都不行。事务日志达30+G,而且使用常规的截断、收缩方法均无法减小日志物理文件的尺寸,经过一番寻找,终于找到了解决方法。

 

查看日志信息 

 

在查询分析器中执行如下代码来查看日志信息:

 

DBCC LOGINFO('数据库名称') 

 

 我们看到status=0的日志,代表已经备份到磁盘的日志文件;而status=2的日志还没有备份。当我们收缩日志文件时,收缩掉的空间其实就是status=0的空间,如果日志物理文件无法减小,这里一定能看到非常多status=2的记录。接下来分析为什么会有这么多status=2的记录

 

查看日志截断延迟原因

 

活跃(active)的日志无法通过收缩来截断,有各种原因会使日志截断延迟,具体表现就是事务日志的物理文件无法通过截断、收缩来减小,通过下面的代码可以看到实例上每个数据库的日志截断延迟原因:

 

1 USE [master]
2 SELECT [name] ,[database_id] ,[log_reuse_wait] ,[log_reuse_wait_desc] FROM [sys].[databases]

 

各种原因及解释如下:

 

log_reuse_wait_desc

说明

NOTHING

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

CHECKPOINT

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

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

LOG_BACKUP

需要日志备份,以将日志的头部前移(仅适用于完整恢复模式或大容量日志恢复模式)。

注意:日志备份不会妨碍截断。完成日志备份后,日志的头部将前移,一些日志空间可能变为可重复使用。

ACTIVE_BACKUP_OR_RESTORE

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

数据备份与活动事务的运行方式相同。数据备份在运行时,将阻止截断。有关详细信息,请参阅本主题后面的“数据备份操作与还原操作”部分。

ACTIVE_TRANSACTION

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

  • 一个长时间运行的事务可能存在于日志备份的开头。在这种情况下,可能需要进行另一个日志备份才能释放空间。有关详细信息,请参阅本主题后面的“长时间运行的活动事务”部分。
  • 事务被延迟(仅适用于 SQL Server 2005 Enterprise Edition 及更高版本)。“延迟的事务”是有效的活动事务,因为某些资源不可用,其回滚受阻。有关导致事务延迟的原因以及如何使它们摆脱延迟状态的信息,请参阅延迟的事务

DATABASE_MIRRORING

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

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

REPLICATION

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

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

DATABASE_SNAPSHOT_CREATION

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

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

LOG_SCAN

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

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

 

针对延迟日志截断原因的部分解决方案

 

  • LOG_BACKUP
    备份日志后再执行收缩即可backup log [database] with nolog
  • REPLICATION
    这是我遇到的情况,但我根本没有启用过REPLICATION,据查,这好像是SQLSERVER2008的一个BUG,解决方法是给标有“REPLICATION”的数据库任意一个表创建数据库事务复制(TRANSACTION REPLICATION),然后再删除,执行数据库与日志备份后,就可以收缩了。

 

·         收缩日志小技巧

 

  • 一般收缩日志的代码中都要求指定日志的文件名称,下面的代码则可以自动获取日志文件名称:
  • 1 USE [数据库名称]
    2 DECLARE@LogFileLogicalName sysname
    3 SELECT@LogFileLogicalName=Name FROM sys.database_files WHERE Type=1
    4 PRINT@LogFileLogicalName
    5 DBCC SHRINKFILE (@LogFileLogicalName, 1);

 

然后再收缩数据库

 

DBCC SHRINKDATABASE(库名)