【生产问题】sql server还原一个很小的BAK文件，但却花了很长时间，分析过程

【生产问题】还原一个很小的BAK文件，但却花了很长时间？

关键词：备份时事务日志太大会发生什么？还原时，事务日志太大会怎么办？

1、前提

【1.1】原库数据已经丢失，只有这个bak了

【1.2】bak文件只有1.8G大小

2、遇见问题

【2.1】1.8G的bak文件，还原了2个小时还没成功。排查为什么这么慢

　　（1）排查请求进度：　查看 sys.dm_exec_requests（查看进度与阻塞）

select status,start_time,command,percent_complete,wait_type,text,
session_id,blocking_session_id
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) s

　　发现进度已经是100

　　（2）在（1）中找到了我的还原操作，发现其进程的wait_type字段为

　　　　BACKUPTHREAD：　当某任务正在等待备份任务完成时出现。等待时间可能较长，从几分钟到几个小时。如果被等待的任务正处于 I/O 进程中，则该类型不指示发生问题。

　　（3）根据（2）中推测可能是因为事务日志太大的原因，导致还原重新写LDF到磁盘非常慢，且还有一个redo、undo的过程。

　　（4）然后进行（3）中推断的验证，查看备份文件中的信息（利用RESTORE FILELISTONLY，参考联机丛书）

--代码演示
declare @bak_path varchar(1000)
set @bak_path = 'E:\Db_Tank_Back\Db_Tank_Copy1.bak'
EXECUTE('RESTORE FILELISTONLY FROM DISK = ''' + @bak_path + '''');    

　　结果如下图：　

　　联机丛书中描述，这里的size是以B为单位，所以除以2^30次方，得出以G为单位的大小值。

　　结果，MDF1.78 G，LDF 107G！！！！！似乎找到原因了，为什么还原这么慢，是因为一个107G的文件，要从备份文件获取大小，然后在本地生成出来，又没有即使文件初始化之类的，需要的时间可想而知。

3、分析整个事件情况

【3.1】为什么MDF+LDF这么大，备份出来的文件只有1.8G?

　　因为，全备只会复制有效数据，即已经持久化的MDF以及尚在活跃中的ldf段（可以粗略理解成还未提交的事务/已提交但还未持久化的事务）。

　　所以，别看ldf那么大，实际上很多都是已经持久化了的，备份文件根本不会去拷贝那些数据（相对而言是无效或者说已经失去时效性的数据）。

　　具体可以参考备份还原原理

【3.2】为什么备份文件只有1.8G，还原出来却把整个MDF1.78 G，LDF 107G 都还原出来了？？

　　这是因为，虽然有效（活跃的事务日志数据）的日志数据不多，大概几百M，但是依然会把整个ldf所占空间给标识出来。

　　这个是在全备时，MSSQL对原数据文件大小信息写入了备份文件文件头，还原时也是会申请这么大，虽然可能实际上使用的数据空间并没有这么大。

　　数据文件和日志文件都会这样。

　　终于，历史2个小时20分钟，还原好了。大致估算，为什么是这么一个值？

　　假如：10M/s的复制速度的话，一个1G就要100秒，100G就需要1W秒，综合就是2个多小时，可能复制速度会更快一点吧？

【4】MSSQL的 MDF很大，为什么备份出来很小？

（0）前提，通ldf一样，mdf也会有这样的问题，或者说他们一并拥有这样的问题。

　　比如典型的例子就是高水位，数据库MDF用了10G空间，数据库delete之后，有效数据只有5G了。

　　但数据库MDF文件仍然是10G，不会主动释放空间给OS，下次再有插入更新等操作就会根据情况优先使用这10G文件里的那5G空闲空间。

　　delete后的文件空间具体情况参考：sql server 测试delete后数据空间情况

（1）解密为什么

　　mssql 会只拿那5G有效数据塞到备份文件 bak中去。但备份文件的文件头会记录下来 Mdf 的预留空间是多大（就是当前MDF有多大，就是上面说的10G），以便还原的时候给MDF 申请这么多空间。

　　由此，bak备份文件因为只存了5G有效数据，它只有5G大小。

　　但还原的时候，因为bak备份文件的文件头记录要申请10G空间，所以还原出来的库有10G大小。

（2）LDF也是由此同理

总结

　　【1】备份全备时，如果事务日志过大，务必进行截断、收缩。

　　【2】日常备份作业中，理应有事务日志备份存在，以便时间点还原以及对事务日志进行截断（截断后即可循环利用空间，除非特殊情况否则不会增长太多，甚至不增长）

       【3】无论备份文件多小，但实际还原的时候，系统会申请备份时间点 那一刻那个库文件的空间来进行还原。