解释一下SQLSERVER事务日志记录
大家知道在完整恢复模式下，SQLSERVER会记录每个事务所做的操作，这些记录会存储在事务日志里，有些软件会利用事务日志来读取
操作记录恢复数据，例如：log explorer
那么事务日志记录怎麽查看，里面都记录了些什么？
打开可以利用下面SQL语句来查看所在数据库的事务日志记录
1 USE [GPOSDB] --要查看事务日志记录的数据库2 GO3 SELECT * FROM [sys].[fn_dblog](NULL,NULL)
事务日志记录里很多东西可以看的，里面记录了非常详细的数据库活动信息
我这里只介绍一些重要的需要知道的字段，其他字段由于本人能力有限而且觉得其他字段不是很重要就不介绍了
CurrentLSN：当前LSN号，事务日志中的每个记录都由一个唯一的日志序列号 (LSN) 标识。LSN 是这样排序的：如果 LSN2 大于 LSN1，
则 LSN2 所标识的日志记录描述的更改发生在日志记录 LSN1 描述的更改之后
MSDN解释：http://msdn.microsoft.com/zh-cn/library/ms190411(v=SQL.90).aspx
Operation：当前LSN所做的操作
Context：操作的上下文
TransactoinID：事务ID号
Log Record Fixed Length：LSN记录的所占虚拟日志文件的固定长度
Previous LSN：前一个LSN号
--------------------------------------------------------------------------------------------------------------
AllocUnitID：修改的那条数据所属分配单元ID
AllocUnitName：修改了数据的表名
Page ID：0001:00000121 转换成十进制：289 所以查看pageid为289页 DBCC PAGE([pratice],1,289,3)
Slot ID：数据所在数据页面的第几条记录
PartitionID：数据所在数据页面的所在分区ID
如上图，修改数据的表名是Insert_Test，Page ID是0001:00000121 转换为十进制为289 Slot ID是6（即数据页的第6条记录）
通过下面SQL语句就可以查看页面所在数据
1 USE [pratice] 2 GO 3 DBCC TRACEON(3604,-1) 4 GO 5 6 DBCC PAGE([pratice],1,289,3) 7 GO
1 Slot 6 Offset 0x552 Length 211 2 3 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 4 Memory Dump @0x0A2AC552 5 6 00000000: 1000d000 3f080000 61616120 20202020 †....?...aaa 7 00000010: 20202020 20202020 20202020 20202020 † 8 00000020: 20202020 20202020 20202020 20202020 † 9 00000030: 20202020 20202020 20202020 20202020 † 10 00000040: 20202020 20202020 20202020 20202020 † 11 00000050: 20202020 20202020 20202020 20202020 † 12 00000060: 20202020 20202020 20202020 20202020 † 13 00000070: 20202020 20202020 20202020 20202020 † 14 00000080: 20202020 20202020 20202020 20202020 † 15 00000090: 20202020 20202020 20202020 20202020 † 16 000000A0: 20202020 20202020 20202020 20202020 † 17 000000B0: 20202020 20202020 20202020 20202020 † 18 000000C0: 20202020 20202020 20202020 20202020 † 19 000000D0: 0200fc†††††††††††††††††††††††††††††††... 20 21 Slot 6 Column 0 Offset 0x4 Length 4 22 23 id = 2111 24 25 Slot 6 Column 1 Offset 0x8 Length 200 26 27 name = aaa
这个表只有两个字段，我们看一下表数据
--------------------------------------------------------------------------------------------------------
Checkpoint Begin：Checkpoint开始时间
Checkpoint Begin DB Version：当前数据库版本 SQL2005是611 SQL2012是706
Checkpoint End：checkpoint的结束时间，这个时间肯定在Checkpoint Begin的下一条事务日志记录的位置
Minimum LSN: 这个第一个日志记录的日志序列号 (LSN)，称为最小恢复 LSN (MinLSN)
Dirty Pages：脏的数据页
Oldest Replicated Begin LSN：如果数据库配置复制的话，那么最老的复制起始LSN
Next Replicated End LSN:下一个复制结尾LSN
Last Distributed End LSN：最新的分发结尾LSN
SPID:执行当前操作的进程ID
Beginlog Status:开始记录事务日志的状态，这个状态表示现时能够正常记录事务日志
Begin Time：事务开始时间
Transaction Name：事务名称
End Time：事务结束时间
Transaction Begin：记录这个事务的begin transaction的时候的cureent LSN
Master DBID:显示当前master数据库的DBID
Preplog Begin LSN:启动数据库前的前一个事务日志LSN
Prepare Time:准备启动数据库的时间
New Split Page：哪个数据页产生了页拆分
Rows Deleted：数据页有多少行被删除了
Description：描述这个事务是干什么的，有时候事务名称不一定就是他所做的操作名称，
比如这里碰巧事务名和描述都是CREATE TABLE 如果你为这个事务命名的话，那么只能看Description列看这个事务是做什么的
现在解释一下 一些常见operation和context，一些不常见的我也不知道 ，呵呵o(∩_∩)o
Operation：当前LSN所做的操作
Context：操作的上下文
Operation | Context | 解释 |
LOP_SET_BITS | LCX_DIFF_MAP | 设置位图，资料： 差异(Differential)备份:只备份上次完整备份后，做修改的部分。备份单位是区(Extent)。意味着某个区内即使只有一页做了变动，则在差异备份里会被体现.差异备份依靠一个BitMap进行维护，一个Bit对应一个区，自上次完整备份后，被修改的区会被置为1，而BitMap中被置为1对应的区会被差异备份所备份。而到下一次完整备份后，BitMap中所有的Bit都会被重置为0 而这个BitMap在数据库第7页： DCM页 差异变更(Differential Changed Map，DCM)页面他跟踪一个文件中的哪一个区在最新一次完整数据库备份之后被修改过。SQLSERVER用在增量备份时只对已发生数据变更的分区进行增量备份即可 |
LOP_BEGIN_XACT |
| 事务开始 |
LOP_MODIFY_ROW | LCX_HEAP | 修改堆表中的某一行记录 |
LOP_PREP_XACT |
| 准备启动数据库 |
LOP_COMMIT_XACT |
| 提交事务 |
LOP_MODIFY_ROW | LCX_BOOT_PAGE | 修改数据库启动页 |
LOP_MODIFY_HEADER | LCX_PFS | 修改PFS页的页头部信息 |
LOP_INSERT_ROWS | LCX_CLUSTERED | 插入数据到聚集索引的索引页 |
LOP_INSERT_ROWS | LCX_INDEX_LEAF | 插入数据到索引的叶子节点即数据页 |
LOP_FORMAT_PAGE | LCX_CLUSTERED | 重新组织聚集索引 |
LOP_DELETE_SPLIT | LCX_CLUSTERED | 删除聚集索引表的一行记录引起页拆分 |
LOP_MODIFY_HEADER | LCX_HEAP | 修改堆表的某页的页头信息 |
LOP_BEGIN_CKPT | LCX_NULL | 检查点开始 |
LOP_END_CKPT | LCX_NULL | 检查点结束 |
LOP_SET_FREE_SPACE | LCX_PFS | 修改PFS页设置那个数据页是空闲的 |
LOP_ROOT_CHANGE | LCX_CLUSTERED | 聚集索引的根节点改变 |
LOP_INSERT_ROWS | LCX_HEAP | 插入数据到堆表 |
LOP_FORMAT_PAGE | LCX_HEAP | 格式化堆里的数据页 |
Operation | Lock Information | 解释 |
LOP_LOCK_XACT | HoBt 0:ACQUIRE_LOCK_SCH_M METADATA: database_id = 14 STATS(object_id = 7, stats_id = 11) | 在事务里获取锁 |
-------------------------------------------------------华丽的分割线---------------------------------------------------
在大容量日志恢复模式下，在事务日志记录里你会看不到对数据页的操作，当你使用bcp ，bulk inert， select into大容量操作语句的时候
像下图的那样修改数据和插入数据的记录你在事务日志记录里找不到的
所以大容量日志恢复模式时，ldf文件才这麽小，插入速度才这麽快
下面引用MSDN:
可以尽量减少日志量的操作
“最小日志记录”是指只记录在不支持时间点恢复的情况下恢复事务所需的信息。 本主题介绍在大容量日志恢复模式下（以及简单恢复模式下）按最小方式记录、但在运行备份时例外的操作。
注意
在完整恢复模式下，所有大容量操作都将被完整地记录下来。 但是，可以通过将数据库暂时切换到用于大容量操作的大容量日志恢复模式，最小化一组大容量操作的日志记录。 最小日志记录比完整日志记录更为有效，并在大容量事务期间，降低了大规模大容量操作填满可用的事务日志空间的可能性。 不过，如果在最小日志记录生效时数据库损坏或丢失，则无法将数据库恢复到故障点。
下列操作在完整恢复模式下执行完整日志记录，而在简单和大容量日志恢复模式下按最小方式记录：
大容量导入操作（bcp、BULK INSERT 和 INSERT...SELECT）。 有关在何时对大容量导入表按最小方式进行记录的详细信息，请参阅在大容量导入中按最小方式记录日志的前提条件。
注意
启用事务复制时，将完全记录 BULK INSERT 操作，即使处于大容量日志恢复模式下。
SELECT INTO 操作。
注意
启用事务复制时，将完全记录 SELECT INTO 操作，即使处于大容量日志恢复模式下。
插入或追加新数据时，使用 UPDATE 语句中的 .WRITE 子句部分更新到大型值数据类型。 注意，在更新现有值时没有使用最小日志记录。 有关大型值数据类型的详细信息，请参阅数据类型 (Transact-SQL)。
在 text、ntext 和 image 数据类型列中插入或追加新数据时的 WRITETEXT 和 UPDATETEXT 语句。 注意，在更新现有值时没有使用最小日志记录。
注意
不推荐使用 WRITETEXT 语句和 UPDATETEXT 语句，因此应该避免在新的应用程序中使用这些语句。
如果数据库设置为简单或大容量日志恢复模式，则无论是脱机还是联机执行操作，都会按最小方式记录一些索引 DDL 操作。 按最小方式记录的索引操作如下：
CREATE INDEX 操作（包括索引视图）。
ALTER INDEX REBUILD 或 DBCC DBREINDEX 操作。
注意
不推荐使用 DBCC DBREINDEX 语句，因此应该避免在新的应用程序中使用该语句。
DROP INDEX 新堆重新生成（如果适用）。
注意
DROP INDEX 操作期间将始终完整记录索引页的释放操作。
随时随地看视频
