人人都是 DBA(VI)SQL Server 事务日志

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
日志服务 SLS,月写入数据量 50GB 1个月
简介:

SQL Server 的数据库引擎通过事务服务(Transaction Services)提供事务的 ACID 属性支持。ACID 属性包括:

  • 原子性(Atomicity)
  • 一致性(Consistency)
  • 隔离性(Isolation)
  • 持久性(Durability)

事务日志(Transaction Log)

事务日志(Transaction Log)存储的是对数据库所做的更改信息,让 SQL Server 有机会恢复数据库。而恢复(Recovery)的过程就是使数据文件与日志保持一致的过程。任何在日志中指示已经提交的数据更改必须出现在数据文件中,任何未标记为提交的更改不能出现在数据文件中。

预写日志(Write-ahead Logging)功能确保在真正发生变化的数据页写入磁盘前,始终先在磁盘中写入日志记录,使得任务回滚成为可能。写入事务日志(Transaction Log)是同步的,即 SQL Server 必须等它完成。但写入数据页可以是异步的,所以可以在缓存中组织需要写入的数据页进行批量写入,以提高写入性能。

事务日志用于保证 SQL Server 在语句或系统出现故障时的可恢复性,并允许将备份的日志应用到数据库上。但事务日志并没有提供很好的可读性,实际上读取事务日志通常也不会获取到太多有用信息。更推荐的跟踪记录机制是使用 SQL Server Profiler 等工具,以筛选和捕获有用的信息。

比如,我们使用下面的 SQL 来创建一张简单的 Table,来尝试观察事务日志的变化。

CREATE TABLE [dbo].[Customer](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](256) NOT NULL,
    [Address] [nvarchar](max) NULL,
    [Phone] [nvarchar](256) NULL
) ON [PRIMARY]

插入一条记录。

复制代码
INSERT INTO [dbo].[Customer]
           ([Name]
           ,[Address]
           ,[Phone])
     VALUES
           ('Dennis Gao'
           ,'Beijing Haidian'
           ,'88888888')
复制代码

使用 DBCC LOG 命名可以先观察产生的序列。

DBCC LOG([TEST])

使用系统提供的函数 sys.fn_dblog 来查看当前的事务日志记录,可以列出很多详细信息,这里只显示了几个常用的列。

复制代码
SELECT [Current LSN]
    ,[Operation]
    ,[Context]
    ,[Transaction ID]
    ,[Log Record Length]
    ,[Previous LSN]
    ,[AllocUnitId]
    ,[AllocUnitName]
    ,[Page ID]
    ,[Slot ID]
    ,[Xact ID]
FROM sys.fn_dblog(NULL, NULL)
复制代码

事务日志总是连续的并且是顺序的,按照 LSN(Log Sequence Number)的顺序排列。从查询的尾部可以查看 AllocUnitName 操作的数据表名称。

对应的 Operation 是 LOP_INSERT_ROWS,Context 是 LCX_HEAP,也就是插入数据到堆表。同时发现 Page ID 是 0001:00000078,也就是十进制的 120 号页面。

可以使用 DBCC PAGE 命令查看 Page 页信息。

dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])
DBCC TRACEON(3604, -1)
GO

DBCC PAGE([TEST], 1, 120, 3)  
GO
复制代码
PAGE: (1:120)


BUFFER:


BUF @0x000000027D15AC80

bpage = 0x000000026B6BA000          bhash = 0x0000000000000000          bpageno = (1:120)
bdbid = 7                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 8595                        bstat = 0x10b
blog = 0x1215accc                   bnext = 0x0000000000000000          

PAGE HEADER:


Page @0x000000026B6BA000

m_pageId = (1:120)                  m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 87    m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594043629568                                
Metadata: PartitionId = 72057594039107584                                Metadata: IndexId = 0
Metadata: ObjectId = 565577053      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 12                        m_slotCnt = 1                       m_freeCnt = 8005
m_freeData = 185                    m_reservedCnt = 0                   m_lsn = (33:460:24)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = ALLOCATED              
PFS (1:1) = 0x61 MIXED_EXT ALLOCATED  50_PCT_FULL                        DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED           

Slot 0 Offset 0x60 Length 89

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 89                    
Memory Dump @0x000000000BE5A060

0000000000000000:   30000c00 01000000 00000000 04000003 002b0049  0................+.I
0000000000000014:   00590044 0065006e 006e0069 00730020 00470061  .Y.D.e.n.n.i.s. .G.a
0000000000000028:   006f0042 00650069 006a0069 006e0067 00200048  .o.B.e.i.j.i.n.g. .H
000000000000003C:   00610069 00640069 0061006e 00380038 00380038  .a.i.d.i.a.n.8.8.8.8
0000000000000050:   00380038 00380038 00                          .8.8.8.8.

Slot 0 Column 1 Offset 0x4 Length 8 Length (physical) 8

Id = 1                              

Slot 0 Column 2 Offset 0x17 Length 20 Length (physical) 20

Name = Dennis Gao                   

Address = [BLOB Inline Data] Slot 0 Column 3 Offset 0x2b Length 30 Length (physical) 30

000000000BE4FC70:   42006500 69006a00 69006e00 67002000 48006100  B.e.i.j.i.n.g. .H.a.
000000000BE4FC84:   69006400 69006100 6e00                        i.d.i.a.n.

Slot 0 Column 4 Offset 0x49 Length 16 Length (physical) 16

Phone = 88888888   
复制代码

可以看出上面的 SQL 语句 Insert 了数据 Id = 1, Name = Dennis Gao, Phone = 88888888。

虚拟日志文件(VLF:Virtual Log File)

不管为事务日志定义多少个物理文件,SQL Server 总是把日志当成连续流(Contiguous Stream)来对待。当 DBCC SHRINKDATABASE 命令确认日志可以缩小多少时,它不是单独考虑每个日志文件,而是根据整个日志来确定可压缩大小。

SQL Server 数据库的事务日志是通过虚拟日志文件(VLF:Virtual Log File)来管理的,VLF 的大小由 SQL Server 根据日志的总大小和日志增量大小来决定,不能通过配置指定。如果 VLF 数量变多会导致数据库性能下降,所以需要指定合理的日志文件初始大小和增长步长,防止过多的 VLF 的产生。

SQL Server 会根据如下规则来判断 VLF 的数量:

 日志大小 

 VLF 数量 

Size <= 1MB 

 将日志文件大小除以最小 VLF 大小(31KB*8KB)确定个数 

1MB < Size <= 64MB

 4 个

 64MB < Size <= 1GB 

 8 个

Size > 1GB

 16 个

当日志持续增长时,会使用相同的方式确定新添加的 VLF 的数量。日志总是以整个 VLF 为单位增长,而且缩小也只能到 VLF 的边界为止。

VLF 可以处于以下 4 种状态之一。

  • Active:日志的活动部分,从未提交事务的最小 LSN 开始,结束于最后一个写入的 LSN。
  • Recoverable:在最早的活动事务之前的那部分日志。
  • Reusable:如果日志已经被备份,则不需要最早活动事务之前的 VLF,可重用这些空间。日志截断或备份会将 Recoverable VLF 转换成 Reusable VLF。
  • Unused:未使用的部分。

可以使用下面的 SQL 查询 VLF 的数量。

复制代码
CREATE TABLE #VLFInfo (
    RecoveryUnitID INT
    ,FileID INT
    ,FileSize BIGINT
    ,StartOffset BIGINT
    ,FSeqNo BIGINT
    ,[Status] BIGINT
    ,Parity BIGINT
    ,CreateLSN NUMERIC(38)
    );

CREATE TABLE #VLFCountResults (
    DatabaseName SYSNAME
    ,VLFCount INT
    );

EXEC sp_MSforeachdb N'Use [?]; 

                INSERT INTO #VLFInfo 
                EXEC sp_executesql N''DBCC LOGINFO([?])''; 
     
                INSERT INTO #VLFCountResults 
                SELECT DB_NAME(), COUNT(*) 
                FROM #VLFInfo; 

                TRUNCATE TABLE #VLFInfo;'

SELECT DatabaseName
    ,VLFCount
FROM #VLFCountResults
ORDER BY VLFCount DESC;

DROP TABLE #VLFInfo;

DROP TABLE #VLFCountResults;
复制代码

可以使用 DBCC LOGINFO 命令进一步观察 VLF 的相关属相。

DBCC LOGINFO

SQL Server 可以配置多个物理日志文件当做一个序列流来对待。如果管理良好,定期备份或截断日志,可能永远都不会使用除第一个文件之外的其他日志文件。当需要新的 VLF 时,多个物理文件中都没有可用 VLF,则会以循环的方式把新的 VLF 添加到每个物理日志文件中。

自动截断模式(Auto Truncate Model)

如果 SQL Server 设置了如下情况,则认为没有维护日志备份:

  • 设置 SIMPLE 恢复模型,数据库会定期截断日志。
  • 从未进行过完全数据库备份。

以上任何一种情况下,SQL Server 会处于自动截断模式(Auto Truncate Model)中,当数据库事务日志满时就会进行截断。这里的 "满" 指的是日志记录的数量比在系统启动过程中、在合理的时间内能够重做的数量多。

判断数据库是否在自动截断模式的最简单的方法是查询 sys.database_recovery_status 目录视图,如果 last_log_backup_lsn 列为空,则数据库就是处于自动截断模式。

SELECT * FROM sys.database_recovery_status;

可以通过 DBCC SQLPERF 命名来查看日志文件大小。

DBCC SQLPERF('logspace')

当然,也可以通过系统提供的目录视图来查看。

复制代码
SELECT instance_name AS [Database]
    ,cntr_value AS [LogFull(%)]
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Percent Log Used%'
    AND instance_name NOT IN (
        '_Total'
        ,'mssqlsystemresource'
        )
    AND cntr_value > 0
ORDER BY [LogFull(%)] DESC;
复制代码

可以使用 DBCC 命令来压缩事务日志文件,下面是 DBCC SHRINKDATABASE 和 DBCC SHRINKFILE 的语法。

复制代码
DBCC SHRINKDATABASE 
( database_name | database_id | 0 
     [ , target_percent ] 
     [ , { NOTRUNCATE | TRUNCATEONLY } ] 
)
[ WITH NO_INFOMSGS ]

DBCC SHRINKFILE 
(
    { file_name | file_id } 
    { [ , EMPTYFILE ] 
    | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
    }
)
[ WITH NO_INFOMSGS ]
复制代码

 

《人人都是 DBA》系列文章索引:

 序号 

 名称 

1

 人人都是 DBA(I)SQL Server 体系结构

2

 人人都是 DBA(II)SQL Server 元数据

3

 人人都是 DBA(III)SQL Server 调度器

4

 人人都是 DBA(IV)SQL Server 内存管理

5

 人人都是 DBA(V)SQL Server 数据库文件

6

 人人都是 DBA(VI)SQL Server 事务日志

7

 人人都是 DBA(VII)B 树和 B+ 树

8

 人人都是 DBA(VIII)SQL Server 页存储结构

9

 人人都是 DBA(IX)服务器信息收集脚本汇编

10

 人人都是 DBA(X)资源信息收集脚本汇编

11

 人人都是 DBA(XI)I/O 信息收集脚本汇编

12

 人人都是 DBA(XII)查询信息收集脚本汇编

13

 人人都是 DBA(XIII)索引信息收集脚本汇编

14

 人人都是 DBA(XIV)存储过程信息收集脚本汇编 

15

 人人都是 DBA(XV)锁信息收集脚本汇编







本文转自匠心十年博客园博客,原文链接:http://www.cnblogs.com/gaochundong/p/everyone_is_a_dba_sqlserver_transaction_logs.html,如需转载请自行联系原作者

相关实践学习
日志服务之使用Nginx模式采集日志
本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
目录
相关文章
|
2月前
|
SQL 安全 UED
通义灵码在DBA日常SQL优化中的使用分享
通义灵码在DBA日常SQL优化中的使用分享
146 1
通义灵码在DBA日常SQL优化中的使用分享
|
13天前
|
SQL 关系型数据库 MySQL
MySQL事务日志-Undo Log工作原理分析
事务的持久性是交由Redo Log来保证,原子性则是交由Undo Log来保证。如果事务中的SQL执行到一半出现错误,需要把前面已经执行过的SQL撤销以达到原子性的目的,这个过程也叫做"回滚",所以Undo Log也叫回滚日志。
MySQL事务日志-Undo Log工作原理分析
|
23天前
|
SQL 存储 缓存
日志服务 SQL 引擎全新升级
SQL 作为 SLS 基础功能,每天承载了用户大量日志数据的分析请求,既有小数据量的快速查询(如告警、即席查询等);也有上万亿数据规模的报表级分析。SLS 作为 Serverless 服务,除了要满足不同用户的各类需求,还要兼顾性能、隔离性、稳定性等要求。过去一年多的时间,SLS SQL 团队做了大量的工作,对 SQL 引擎进行了全新升级,SQL 的执行性能、隔离性等方面都有了大幅的提升。
|
1月前
|
SQL 存储 缓存
MySQL进阶突击系列(02)一条更新SQL执行过程 | 讲透undoLog、redoLog、binLog日志三宝
本文详细介绍了MySQL中update SQL执行过程涉及的undoLog、redoLog和binLog三种日志的作用及其工作原理,包括它们如何确保数据的一致性和完整性,以及在事务提交过程中各自的角色。同时,文章还探讨了这些日志在故障恢复中的重要性,强调了合理配置相关参数对于提高系统稳定性的必要性。
|
3月前
|
存储 缓存 关系型数据库
MySQL事务日志-Redo Log工作原理分析
事务的隔离性和原子性分别通过锁和事务日志实现,而持久性则依赖于事务日志中的`Redo Log`。在MySQL中,`Redo Log`确保已提交事务的数据能持久保存,即使系统崩溃也能通过重做日志恢复数据。其工作原理是记录数据在内存中的更改,待事务提交时写入磁盘。此外,`Redo Log`采用简单的物理日志格式和高效的顺序IO,确保快速提交。通过不同的落盘策略,可在性能和安全性之间做出权衡。
1773 14
MySQL事务日志-Redo Log工作原理分析
|
2月前
|
监控 安全 网络安全
使用EventLog Analyzer日志分析工具监测 Windows Server 安全威胁
Windows服务器面临多重威胁,包括勒索软件、DoS攻击、内部威胁、恶意软件感染、网络钓鱼、暴力破解、漏洞利用、Web应用攻击及配置错误等。这些威胁严重威胁服务器安全与业务连续性。EventLog Analyzer通过日志管理和威胁分析,有效检测并应对上述威胁,提升服务器安全性,确保服务稳定运行。
|
3月前
|
SQL 数据库
为什么 SQL 日志文件很大,我应该如何处理?
为什么 SQL 日志文件很大,我应该如何处理?
|
4月前
|
SQL 存储 缓存
高基数 GroupBy 在 SLS SQL 中的查询加速
本文详细介绍了SLS中的高基数GroupBy查询加速技术。
151 21
|
2月前
|
SQL Oracle 关系型数据库
[SQL]事务
本文介绍了事务处理的基本概念,包括事务的四大特性(原子性、一致性、隔离性、持久性)及生命周期。文章还详细解释了事务的保存点、四种事务隔离级别及其异常读现象,并提供了设置事务隔离级别的方法。最后,作者建议读者深入学习相关理论以更好地理解事务隔离级别。
62 0
|
3月前
|
SQL 数据库
为什么SQL日志文件很大,该如何处理?
为什么SQL日志文件很大,该如何处理?
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等