大家在工作中可能会遇到这样的场景：

壁橱里的小王：哎呀！存放数据库的磁盘满了/为什么数据文件才100M，日志文件已经暴涨到了200G/数据库插入操作hang住了！！！ 大佬，怎么肥四？

隔壁老王：日志太大，快做日志收缩。

壁橱里的小王：老王，我做了啊，但是日志没有变小啊。

隔壁老王：哦，那你做了日志截断吗？

壁橱里的小王：。。。。啥意思

如果大家也和壁橱里的小王一样，一脸懵逼，截断&收缩傻傻分不清楚，那么，请往下看吧。

目录

一 日志

二 日志备份

三 日志还原

四 常用工具

五 工作中苦逼的例子

一 日志

1 日志备份的意义：

• 为防止数据损坏而提供的保障。
• 数据归档
• 数据迁移

2 日志的本质 —— VLF

一个数据库可以有多个日志文件，但是，逻辑上他们可以看成一个。

SQL对日志文件的管理，是将逻辑上的这一个ldf，分成多个逻辑上的虚拟日志文件(virtual log files,简称VLF)

日志以整个VLF作为单位进行增长和收缩，所以

——日志文件是顺序读取，不像data page并行访问，所以日志文件分成多个不会有性能上的提升。

3 VLF状态

活动： 日志活动部分(未commit的活动事务)——只要VLF中包含了活动日志，就认 为是活动VLF。            
可恢复： 老LSN之前的日志 作用就是保存之前记录，以帮助回滚恢复到之前的某一状态——还未截断
可复用：老LSN之前的日志没用了，就可以复用了。——已经截断(truncated)
未用：VLF还未被使用复制代码

4 优化指北

指定适当的日志文件大小和增长，是减少日志碎片的关键。

一个日志物理文件的VLF用完了，才会使用下一个日志文件。——适合的日志大小和自我截断，可以只使用一个日志文件

当所有日志文件都用完(所有VLF都在用，status=2)，增加日志大小的时候，DB要遍历所有日志物理文件增加VLF（比如增加2m，每个日志物理文件都要增加2m）复制代码

二 日志备份

1 备份mode和恢复mode的区别

2 日志截断

• 截断不等于收缩。先截断 后收缩
• 截断：日志中MinLsn之前的状态为可重用(MinLsn之前的日志失效)

截断方法：

1 恢复Mode——简单Mode【自动截断】（事务一旦提交或回滚，该日志这部分就可以重新使用）
checkpoint会遍历VLF，检查是否有日志可以截断。
如果有inactive的VLF时，CheckPoint都会将可截断部分进行截断，并将MinLSN向后推.

2 恢复Mode——完整Mode（CheckPoint不会截断日志，需手动截断）
a.完整备份——sql认为需要保存老LSN之前的LSN，一旦再有活动事务就会增长VLF
——不会自动截断——需要手动截断
b.日志备份，选择日志截断复制代码

三 日志还原

1 还原的概念

日志恢复数据要求从近一次完整或差异备份到所恢复的时间点之间的日志链是连续的

1复制数据：从完整备份和差异备份中将数据、索引页和日志复制到被恢复数据库文件.
2Redo重做：日志中事务在被恢复数据库中重做一遍。——数据库不可使用
3Undo撤销：Recovery，参考活动事务表，未提交事务回滚。——数据库可用。不允许再恢复后续备份.复制代码

2 数据库写操作整体流程

3 Check Point检查点机制

1触发：
* DPT||日志 达到70% 
* 周期写入
2机制：
* DPT中LSN 小于等于 log外存的末尾LSN，符合条件的写入外存E
3用处：
* 减少服务器恢复时间。检查点这个LSN之前的日志都落磁盘了。复制代码

4 常见恢复数据库做法

还原完整备份log 
还原log
还原后一个log，选择Reconvery复制代码

四 常用工具

/*
* Author: jaki wang
* 所有数据库： 全备，日志备份，收缩日志
*/
CREATE PROC [dbo].[SP_BACKUP_Shrinklog]
AS
BEGIN
DECLARE cur CURSOR FOR 
SELECT NAME FROM Master..SysDatabases 
WHERE name<>'master' 
and name<>'model' 
and name<>'msdb'  
and name<>'tempdb'  

DECLARE @tb SYSNAME     --DB name
DECLARE @full VARCHAR(200)  --DB BACKUP PATH
DECLARE @log VARCHAR(200)  --DB BACKUP LOG PATH

OPEN cur 
   
FETCH NEXT FROM cur INTO @tb  
  
WHILE @@fetch_status=0    
BEGIN    
	--backup mode: full
	SELECT @full='c:\zlog\'+@tb+'_full' + REPLACE(CONVERT(nvarchar(20),GETDATE(),120),':','-') + '.bak'
	BACKUP DATABASE @tb TO DISK=@full WITH INIT

	--backup mode : log
	SELECT @log='c:\zlog\'+@tb+'_log' + REPLACE(CONVERT(nvarchar(20),GETDATE(),120),':','-') + '.log'
	BACKUP log @tb TO DISK=@log WITH INIT 
	 
	---shrinklog for database
	DBCC SHRINKDATABASE(@tb)

	FETCH NEXT FROM cur INTO @tb   
	
	 
END    
CLOSE cur    
DEALLOCATE cur  




END
GO
复制代码

五 工作中苦逼的例子

1 mdf数据文件错删

1日志尾巴 备份 ——如果我们还能做这种后的备份的话
2文件夹有3个日志备份文件：
全备
事务日志备份
日志尾巴备份
3数据库还原：
完全备份 norecovery
日志备份 norecovery
tail备份 recovery复制代码

2 sqlserver 实例崩溃——无法通过t-sql备份结尾日志

1我们手上有的文件：数据库ldf，备份的full bak和日志备份log bak
2把文件copy到其他拥有sql实例的机子上
3创建和原database同名的数据库，设为脱机
4删除新建database的mdf文件
5用崩溃机子上的ldf替换新建database的ldf
6备份结尾日志
7原有sql实例还原full，log和结尾日志复制代码