SQL Server 的日志文件

浅浅的花香味﹌ 2022-08-14 01:54 221阅读 0赞

SQL Server 2005--正确认识日志文件

1、正确认识日志文件。

一个Sql Server数据库最少要包含一个mdf数据文件和一个ldf日志文件。mdf文件中包含了所有的数据库对象和数据，例如表、存储过程、用户信息等等。 ldf文件中包含(或者曾经包含)了数据库的所有事务日志，但是不要指望仅仅通过ldf日志文件来恢复数据库。例如，想通过ldf文件和以前的完全备份将数据恢复到某个时间点，或者由于某些原因只剩下了ldf日志文件，想通过ldf日志文还原出mdf文件等等，这些都是行不通的。当然，必须承认通过一些三方工具或特殊的手段的确可能可以通过ldf文件恢复部分的数据。即便是这样，一般情况下通过ldf文件恢复出你想要的数据的情况也是少之又少。

ldf日志文件的真正作用应该是以下几个：

做为事务日志的一个物理存储位置，让我们可以把日志从ldf文件中备份成日志备份，我们可以通过日志备份来还原数据库。

Sql Server在每次启动时，也会从ldf文件中读取日志，以对之前没有提交的事务进行回滚或者对一些已提交但是没有写入数据文件的事务进行前滚，保证事务的完整性。

数据库的一个完全备份包含了数据库的全部数据，但是并不会包含全部的日志，完全备份只包含了当前数据库的所有数据页和日志尾部，日志尾部包含了数据库的活动日志（后面会介绍活动日志），在从一个完全备份还原数据库时，Sql Server会根据完全备份文件中的日志来对数据库中之前没有提交的事务进行回滚或者对一些已提交但是没有写入数据文件的事务进行前滚。

2、关于活动日志

活动日志就是指当前正在运行中的事务的日志记录。举个例子，例如下面的一断代码：

\-->BEGIN TRAN  
DELET FROM TABLE1 WHERE ID='00001'  
DELET FROM TABLE1 WHERE ID='00002'  
DELET FROM TABLE1 WHERE ID='00003'  
DELET FROM TABLE1 WHERE ID='00004'

上面的代码开始了一个事务，然后进行了一些删除操作，但是还没有提交或者回滚。这个未提交事务的日志就是活动日志的一部分，它包含这个事务的开始和这些删除操作。活动日志不能被截断的，因为活动的事务对数据的修改没有最终反映到数据文件上，所以如果此时出现宕机、用户的显示回滚或者其它异常情况，则需要通过日志来对数据修改进行回滚或者重做，因此未提交事务的日志不能被截断。

3、关于虚拟日志

虚拟日志是日志存放的逻辑单位，Sql Server会把ldf日志文件空间逻辑上划分为几个块，每一个块就是一个虚拟日志。我们可以通过DBCC LogInfo命令来查看数据库的虚拟日志的个数和大小。我们可以创建一个新的数据库来查看一下：

\-->Create DataBase Test

Use Test  
DBCC loginfo

在我的虚机的Sql Server 2000下，显示的结果如下：

下面的语句查看我机器上Master数据库的虚拟日志情况：

\-->Use Master

DBCC loginfo

显示结果如下：

新创建的Test数据库有两个虚拟日志，而master数据库一共有9个虚拟日志。虚拟日志的个数不是固定的，ldf日志文件增大时虚拟日志文件的个数会增加，对数据库进行收缩操作时，虚拟日志的个数可能会减少，这里不详细做讨论了。

显示结果表中的FileSize字段就是指的虚拟日志的大小，StartOffset指的是虚拟日志的起始位置相对于ldf日志文件的起始位置的偏移量。注意第一个虚拟日志文件的StartOffset的大小是8192字节，正好是一个页的大小，这个页也就是ldf日志文件的日志信息页，包含了一些诸如ldf日志文件的大小等信息。

这里重点注意一下Status字段的含义：

当Status为0时表示该虚拟日志可以被使用，这个可以被使用包括两种情况，一种情况是指这个虚拟日志一直都没有被使用过，另一种意思是说这个虚拟日志空间已经满了，后来由于被截断，它的空间又可以重新被利用。

当Status为2时表示该虚拟日志已经在使用了，也包括两种情况，一种情况是说新的日志目前就是写到这个虚拟日志上，另一种情况是指这个虚拟日志的空间已经被日志填满，但是由于没有被截断，所以不能重新被利用，也相当于一种正在使用中的状态。

最后用图来描述一下活动日志和虚拟日志。

下面图中的每个方块代表了一条事务日志，下面描述了两个事务，日志LSN141中记录了Tran1开始启动，并在LSN146中提交，由于事务Tran1已经提交，那么它的日志就不是活动日志了。在图中还有两条叫做“检查点”的日志，这里简单解释一下，每到一个检查点，Sql Server会把内存中的数据页和日志页写入磁盘。

注意看Tran2，LSN142记录了它的启动日志，并在LSN148中Tran2有做一些操作，但是还没有Tran2提交的日志，只要这个事务不提交，它的日志就是活动日志，包含有活动日志的虚拟日志不能被截断。

再看虚拟日志中的情况，如果Tran2启动的日志记录在虚拟日志3中，如果它不提交，那么虚拟日志3以及它以后的所有被使用的虚拟日志都不能被截断。在下图中，即使是虚拟日志4中没有Tran2的日志信息，但是为了保证事务的连续性，在虚拟日志4的空间使用完后，只要Tran2不提交，Sql Server就不会截断虚拟日志4。

4、 日志截断

简单一点的理解就是删除以前的日志，仅仅只是截断日志并不会缩小ldf日志文件的物理空间，因为截断仅仅只是把以前日志所占用的空间标记为可重用。要把日志截断说清楚，要先解释下两个概念——活动日志和虚拟日志：

5、几种恢复模式和备份方式。

数据库有三种恢复模式，简单恢复模式、完全恢复模式，大批量操作恢复模式，我们在创建数据时默认使用的就是完全恢复模式。这里简单说下几个恢复模式的区别，重点要说的完全恢复模式，因为这个最常用也最适用于大部分情况。

简单恢复模式：可以理解为在这种模式下Sql Server不记录日志（并不是真的不记录任何日志）。在这种模式下Sql Server会截断非活动日志，并且无法备份日志也不允许备份日志。因此在这种恢复模式下就不可能任意的将数据库还原到某个时间点了。只能做完全备份、增量备份来备份某个时刻的数据。

完全恢复模式：在完全恢复模式下，必须要有一个完全备份和一系列的日志备份来进行时点还原，要不然就失去完全恢复模式的意义了。

大批量恢复模式：和完全模式基本相同，但是优化了部分大批量操作的日志记录，但是需要在做备份时付出相应的代价，在这里不做讨论。

这里再对完整恢复模式做一下补充。一般情况，在完整恢复下Sql Server 不会去自动截断日志，如果日志空间满了并且有指定了日志文件自动增长，那么日志文件会自动增大。但是如果在数据库创建后没有做过任何的完整备份，那么在日志空间满了后，Sql Server 会截断日志来重新利用日志空间，而不会增长日志文件。并且如果自数据库创建以来没有做过任何的完全备份，如果直接备份事务日志的话还是会对日志进行截断，但是备份出来的日志备份文件不含有任何日志信息，即使此时ldf文件的确是有日志信息的。之所以这样，估计是因为没有一个完全备份，所以即使是备份了日志也没有任何用途可言，因为没有完全备份的话仅有日志备份也无法恢复数据。

我做了一下试验，创建一个新的数据库，向里面逐渐插入数据，然后查看数据库日志空间和虚拟日志的变化。可以用DBCC SqlPerf('LogSpace')来查看日志文件空间的使用情况，用DBCC LogInfo来查看虚拟日志的情况，DBCC LogInfo需要在当前数据库下使用。

\-->--先创建数据库  
Create DataBase Test

use Test  
Create Table TestTable  
(  
ID int identity(1,1) primary key,  
Name VarChar(30)  
)  
  
\--向TestTable表插入记录  
Declare @Num Int  
Set @Num = 100  
Declare @i Int  
Set @i=@Num  
While @i<@Num  
Begin  
Insert TestTable (\[Name\]) Values ('sssssssssssssss')  
Set @i=@i+1  
End

下面是用上面的脚本向TestTable表里面分别插入100条～10000后，日志空间和虚拟日志的变化情况。

▲ 刚创建好数据和表的时候，日志文件的初始状态如下，日志大小为0.48437M，空间利用率为41％左右（这里说明一下，为什么刚创建日志空间就有41％已经被用掉了，因为Sql Server 会保留200K～300K的预留空间，这部分空间一直都会被标记为已经被使用的状态，如果对这部分预留空间有兴趣的可以参考下这篇[http://support.microsoft.com/kb/281879][http_support.microsoft.com_kb_281879]）。

有两个虚拟日志文件，一个status为2，说明正在使用中。一个FSeqNo为0，根本就还没有使用过，是一个还没有使用过的虚拟日志。

注意，在下面的一系列图片中，上面部分的数据是日志空间的使用情况，下面的是虚拟日志的情况。

▲ 接下来用前面的脚本插入100条记录，此时由于数据量不大，FSeqNo为5的虚拟日志文件还有空间，所以第二个虚拟日志文件仍然是空闲，此时很明显日志空间的利用率开始有所增加，达到了52%左右。

▲ 接下来再插入200条，此时总记录数为300了。日志的使用率已经很高了，为81％左右，也就是已经使用了大概0.39M。这里强调一下：在完全恢复模式下，如果没有进行过完整备份并且数据库的日志空间使用率高于70％，那么Sql Server只要有可能就会进行日志截断。

但是这里的情况很特殊，因为虽然日志空间的使用率已经高于了70％，Sql Server却无法进行日志截断来让日志的使用率降低，因为现在只有两个虚拟日志文件，一个正在使用，一个还没被使用。必须有一个虚拟日志的状态为使用中，所以当前被使用的虚拟日志是不能被截断的，而另一个虚拟日志文件本身就是可用的。导致这个情况的主要原因就是Sql Server 保留的200K～300K预留空间，这个空间在虚拟日志中无法体现出来，但是在Sql Server在计算日志空间的使用率的时候确算上了这一部分的空间。如果继续写入日志，日志的使用率继续增加，而又无法截断虚拟日志来让空间使用率降低，这个时候Sql Server就不得不增加日志文件的大小了。

▲ 继续插入100条，总记录数为400。正入上面所说的，Sql Server 不得不增加ldf日志文件的物理空间了。这个时候日志已经增大为0.7421875，使用率为62.63158%，使用的空间大小为0.464844。

这里的情况是非常特殊的，如果我们在创建数据库时把日志文件的大小指定大一些，例如5M，这样Sql Server所预留的这部分空间所占比例就小了，这样Sql Server会先使用第一个虚拟日志文件，第一个使用完之后再使用第二个，同时截断第一个虚拟日志让第一个虚拟日志的空间可以重新被利用，不会受到这部分预留空间的影响导致需要增大ldf日志文件的物理大小。

最后的的那个虚拟日志就是新增加的空间，它当前就正在使用中，状态Status理所当然为2。

注意看此时的状态，由于日志空间的利用率低于70％，此时Sql Server还不会去截断日志。所以虽然第一个虚拟日志（StartOffset为8192的那个）的空间已经满了，但是他的Status还是为2。直到日志空间的利用率高于70％，Sql Server才会对它进行截断，它的Status会变为0。

▲ 这里继续插入100条记录，总记录数为500条。注意观察，有上面400条记录的时候，日志空间使用率已经达到62%左右，如果再插入100条的话日志空间的使用率就会超过70％，所以Sql Server就会对日志进行截断，并重新计算日志空间的使用率。对日志进行截断后，第一个虚拟日志状态变为0，被认为可以重新利用，因此日志空间的使用率就又降低为了46.97％。

▲ 再插入500条，总记录数为1000。注意，虽然一下子插入了这么多记录，但是日志空间的大小没有任何变化，因为每当日志空间使用率超过70％的时候 Sql Server都会截断虚拟日志来重新利用日志空间。另外可以看到FSeqNo的变化，每次当一个虚拟日志重新被使用（包括第一次被使用的情况），他的 FSeqNo变为当前最大FSeqNo加上1。

▲ 再插入2000条，总记录数为3000。可以看到日志空间仍然没有变化，但是FSeqNo在逐渐增加，因为每次一个虚拟日志重新被使用，它的FSeqNo都会在当前最大FSeqNo的基础上加1，所以FSeqNo会不断增长。

▲ 再插入2000条，总记录数为5000。和上面一样，日志空间没有任何变化，FSeqNo会有一些增加。

▲ 这里我们做一件事情，因为从我们创建数据库以来一直都没有对数据库做过任何的完整备份。可以看到在日志文件增大为0.74MB后日志文件就不再增大，一直都是在循环使用三个虚拟日志空间。这里我们来对数据库做一次完整备份，然后再插入3000条。

**在做过一次完整备份后，Sql Server就会认为你以后会进行相应的日志备份来进行数据恢复，因此它就不会对日志进行截断，它会一直保留这些日志（除非你显示的告诉Sql Server去截断日志，例如备份日志之类的操作），当日志文件快用完时，Sql Server会增加日志文件的大小，当然虚拟日志的个数也就会随之增加。**

下面是在对数据库做了一次完整备份后，再插入3000条记录后的结果。日志文件大小显著增加，虚拟日志文件的个数自然也增加了。因为Sql Server不会主动去截断日志，所以会发现绝大多数的虚拟日志的Status都是2，不能被重新使用。

▲ 接着再插入2000条，总共 10000条记录。可以看到和上面类似，日志文件在继续增大。虚拟日志文件的个数也在增加。

▲ 现在我们备份一下日志，因为默认的日志备份选项是会截断日志的，我们可以看到，在备份日志后，日志文件的大小没有任何变化。但是由于截断，当前使用的虚拟日志也就是FSeqNo为26的那个虚拟日志之前并且空间已经用完的虚拟日志都被截断，它们的状态Status都由2变为0，如下图：

4、总结

数据库的日志文件会记录所有的事务日志，但是如果在简单恢复模式下，或者不在简单恢复模式下但是没有对数据库做过完全备份，那么Sql Server不会长久的保存日志，Sql Server会对日志进行截断，也就是说丢弃以前的日志记录，但是会保留活动日志。

Sql Server的日志会被划分为一些虚拟日志，当一个虚拟日志使用完后再使用下一个虚拟日志。Sql Server在截断日志时会以虚拟日志为单位来进行截断，被截断的日志可以被重新使用。但是截断日志不会减少日志的物理空间，只会把已满的虚拟日志重新标记为可用状态。

在完整恢复模式（已做过完全备份）下，如果不进行任何日志备份，那么日志文件会一直增长，Sql Server不会去主动截断日志。我们可以定期备份日志，从而让日志空间得以重复里面，避免日志增长过大。

[http_support.microsoft.com_kb_281879]: http://support.microsoft.com/kb/281879