22,209
社区成员
发帖
与我相关
我的任务
分享关于SQL server 2008 R2 数据库日志收缩的疑问
最近在进行SQL server 2008 数据库数据的还原。。发现日志文件太大很影响操作。
由于2008版本无法沿用2005模式的还原方法。。所以特地从百度当中找寻了一些数据库日志的收缩方法。我有按照上面的方式去测试过。。确实可以达到效果。。但是我心里还是不踏实。。。不知道这些方法会不会导致以后有什么不良后果：如下：
(SQL2008):
在SQL2008中清除日志就必须在简单模式下进行，等清除动作完毕再调回到完全模式。
USE [master]
GO
ALTER DATABASE hr_sbs SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE hr_sbs SET RECOVERY SIMPLE --简单模式
GO
USE hr_sbs
GO
DBCC SHRINKFILE (N'hr_sbs_Log' , 11, TRUNCATEONLY)
GO
USE [hr_sbs]
GO
ALTER DATABASE hr_sbs SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE hr_sbs SET RECOVERY FULL --还原为完全模式
GO
--USE [hr_ldx_cs]
--DECLARE @LogFileLogicalName sysname
--SELECT @LogFileLogicalName=Name FROM sys.database_files WHERE Type=1
--PRINT @LogFileLogicalName
--DBCC SHRINKFILE (@LogFileLogicalName, 1);
--DBCC LOGINFO(hr_ldx_cs)
优点：此清除日志所运行消耗的时间短，90GB的日志在分钟左右即可清除完毕，做完之后做个完全备份在分钟内
即可完成。
缺点： 不过此动作最好不要经常使用，因为它的运行会带来系统碎片。普通状态下LOG和DIFF的备份即可截断日志。
此语句使用的恰当环境：当系统的日志文件异常增大或者备份LOG时间太长可能影响生产的情况下使用。
查看日志截断延迟原因
活跃(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
备份日志后再执行收缩即可
• REPLICATION
这是我遇到的情况，但我根本没有启用过REPLICATION，据查，这好像是SQLSERVER2008的一个BUG，解决方法是给标有“REPLICATION”的数据库任意一个表创建数据库事务复制（TRANSACTION REPLICATION），然后再删除，执行数据库与日志备份后，就可以收缩了
• --USE [hr_ldx_cs]
• --DECLARE @LogFileLogicalName sysname
• --SELECT @LogFileLogicalName=Name FROM sys.database_files WHERE Type=1
• --PRINT @LogFileLogicalName
• --DBCC SHRINKFILE (@LogFileLogicalName, 1);
• --DBCC LOGINFO(hr_ldx_cs)
解决方法下载地址如下 ：http://www.chinavalue.net/BlogFeeds/7394_120232.aspx
由于2008版本无法沿用2005模式的还原方法。。所以特地从百度当中找寻了一些数据库日志的收缩方法。我有按照上面的方式去测试过。。确实可以达到效果。。但是我心里还是不踏实。。。不知道这些方法会不会导致以后有什么不良后果：如下：
(SQL2008):
在SQL2008中清除日志就必须在简单模式下进行，等清除动作完毕再调回到完全模式。
USE [master]
GO
ALTER DATABASE hr_sbs SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE hr_sbs SET RECOVERY SIMPLE --简单模式
GO
USE hr_sbs
GO
DBCC SHRINKFILE (N'hr_sbs_Log' , 11, TRUNCATEONLY)
GO
USE [hr_sbs]
GO
ALTER DATABASE hr_sbs SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE hr_sbs SET RECOVERY FULL --还原为完全模式
GO
--USE [hr_ldx_cs]
--DECLARE @LogFileLogicalName sysname
--SELECT @LogFileLogicalName=Name FROM sys.database_files WHERE Type=1
--PRINT @LogFileLogicalName
--DBCC SHRINKFILE (@LogFileLogicalName, 1);
--DBCC LOGINFO(hr_ldx_cs)
优点：此清除日志所运行消耗的时间短，90GB的日志在分钟左右即可清除完毕，做完之后做个完全备份在分钟内
即可完成。
缺点： 不过此动作最好不要经常使用，因为它的运行会带来系统碎片。普通状态下LOG和DIFF的备份即可截断日志。
此语句使用的恰当环境：当系统的日志文件异常增大或者备份LOG时间太长可能影响生产的情况下使用。
查看日志截断延迟原因
活跃(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
备份日志后再执行收缩即可
• REPLICATION
这是我遇到的情况，但我根本没有启用过REPLICATION，据查，这好像是SQLSERVER2008的一个BUG，解决方法是给标有“REPLICATION”的数据库任意一个表创建数据库事务复制（TRANSACTION REPLICATION），然后再删除，执行数据库与日志备份后，就可以收缩了
• --USE [hr_ldx_cs]
• --DECLARE @LogFileLogicalName sysname
• --SELECT @LogFileLogicalName=Name FROM sys.database_files WHERE Type=1
• --PRINT @LogFileLogicalName
• --DBCC SHRINKFILE (@LogFileLogicalName, 1);
• --DBCC LOGINFO(hr_ldx_cs)
解决方法下载地址如下 ：http://www.chinavalue.net/BlogFeeds/7394_120232.aspx
...全文
请发表友善的回复…
发表回复
lzq2011 2014-05-09
- 打赏
- 举报
很有用处啊，谢谢楼主
KevinLiu 2012-10-19
- 打赏
- 举报
If you are uing full recovery model and log file grow quickly, you can adjust log backup frequency.
If simple mode, sql server will automaticlly truncate the log when necessary.
If simple mode, sql server will automaticlly truncate the log when necessary.
發糞塗牆 2012-10-19
- 打赏
- 举报
这里特指日志备份，因为日志备份不仅仅是为了保存，而是为了截断日志记录，这样ldf文件里面就有了很多可重用的空间（如果都是没提交的事务，就截断不了）。截断了，再做收缩会有效的多。
zhangyu119301 2012-10-19
- 打赏
- 举报
备份是必须的。。主要是按照上诉方法测试过，没有发现有什么异样。
可是本人并非专业的数据库管理员，不知道这样的方法会导致什么样的后果
可是本人并非专业的数据库管理员，不知道这样的方法会导致什么样的后果
發糞塗牆 2012-10-19
- 打赏
- 举报
其实我觉得无论做什么操作，首先做一次日志备份是必须的，做完，你再收缩。
