系统数据库，Server系统视图sys
master数据库
最近发现在SQL Server数据库（目前测试过SQL Server 2008, 2012，2014，2016各个版本）中，即使数据库处于脱机（OFFLINE）状态，但是sys.master_files中依然显示是联机状态。本文测试环境为Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) 。具体测试过程如下所示：
数据库记录 SQL Server 系统的所有系统级信息。这包括实例范围的元数据（例如登录帐户）、端点、链接服务器和系统配置设置。此外，master 数据库还记录了所有其他数据库的存在、数据库文件的位置以及 SQL Server 的初始化信息。因此，如果 master 数据库不可用，则 SQL Server 无法启动。在 SQL Server 中，系统对象不再存储在 master 数据库中，而是存储在 Resource 数据库.aspx)中。
本文是对SQL Server事务日志的总结，文章有一些内容和知识来源于官方文档或一些技术博客，本文对引用部分的出处都有标注。
不能在 master 数据库中执行下列操作：
USE master;
GO
ALTER DATABASE TEST SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO
SELECT name ,
physical_name ,
state ,
state_desc
FROM sys.master_files
WHERE database_id = DB_ID('test');
SELECT name ,
state ,
state_desc
FROM sys.databases
WHERE name = 'test';
添加文件或文件组。
更改排序规则。默认排序规则为服务器排序规则。
更改数据库所有者。master 归 dbo 所有。
创建全文目录或全文索引。
在数据库的系统表上创建触发器。
删除数据库。
从数据库中删除 guest 用户。
启用变更数据捕获。
参与数据库镜像。
删除主文件组、主数据文件或日志文件。
重命名数据库或主文件组。
将数据库设置为 OFFLINE。
将数据库或主文件组设置为 READ_ONLY。
model 数据库
事务日志介绍
model 数据库用作在 SQL Server 实例上创建的所有数据库的模板。因为每次启动 SQL Server 时都会创建 tempdb，所以 model 数据库必须始终存在于 SQL Server 系统中。
不能在 model 数据库中执行下列操作：
添加文件或文件组。
更改排序规则。默认排序规则为服务器排序规则。
更改数据库所有者。model 归 dbo 所有。
删除数据库。
从数据库中删除 guest 用户。
启用变更数据捕获。
参与数据库镜像。
删除主文件组、主数据文件或日志文件。
重命名数据库或主文件组。
将数据库设置为 OFFLINE。
将数据库或主文件组设置为 READ_ONLY。
使用 WITH ENCRYPTION 选项创建过程、视图或触发器。加密密钥与在其中创建对象的数据库绑定在一起。在 model 数据库中创建的加密对象只能用于 model 中。
在SQL Server中，事务日志是数据库的重要组件，如果系统出现故障，则可能需要使用事务日志将数据库恢复到一致状态。每个SQL Server数据库都拥有自己的事务日志，用于记录所有事务以及每个事务对数据库所做的修改。那么数据库的哪些操作会记录在事务日志中呢？具体一点的说，这些操作包括：
如上所示，sys.databases系统视图正确的显示数据库处于脱机状态(OFFLINE)，但是系统视图sys.master_files显示的依然是联机（ONLINE），我们可以获取系统视图sys.master_files的定义，如下所示（至于如何获取视图定义，如果你不清楚，可以参考我的博客SQL Server查看视图定义总结），
msdb 数据库
msdb 数据库由 SQL Server 代理用于计划警报和作业，也可以由其他功能（如 Service Broker 和数据库邮件）使用。
不能在 msdb 数据库中执行下列操作：
· 每个事务的开始和结束。
更改排序规则。默认排序规则为服务器排序规则。
删除数据库。
从数据库中删除 guest 用户。
启用变更数据捕获。
参与数据库镜像。
删除主文件组、主数据文件或日志文件。
重命名数据库或主文件组。
将数据库设置为 OFFLINE。
将主文件组设置为 READ_ONLY。
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE VIEW sys.master_files AS
SELECT
database_id = f.dbid,
file_id = f.fileid,
file_guid = f.fileguid,
type = f.filetype,
type_desc = ft.name,
data_space_id = f.grpid,
name = f.lname,
physical_name = f.pname,
state = convert(tinyint, case f.filestate -- Map enum EMDFileState to AvailablityStates
when 0 then 0 when 10 then 0 -- ONLINE
when 4 then 7 -- DEFUNCT
when 5 then 3 when 9 then 3 -- RECOVERY_PENDING
when 7 then 1 when 8 then 1 when 11 then 1 -- RESTORING
when 12 then 4 -- SUSPECT
else 6 end), -- OFFLINE
state_desc = st.name,
f.size,
max_size = f.maxsize,
f.growth,
is_media_read_only = sysconv(bit, f.status & 8), -- FIL_READONLY_MEDIA
is_read_only = sysconv(bit, f.status & 16), -- FIL_READONLY
is_sparse = sysconv(bit, f.status & 256), -- FIL_SPARSE_FILE
is_percent_growth = sysconv(bit, f.status & 32), -- FIL_PERCENT_GROWTH
is_name_reserved = sysconv(bit, case f.filestate when 3 then 1 else 0 end), -- x_efs_DroppedReusePending
create_lsn = GetNumericLsn(f.createlsn),
drop_lsn = GetNumericLsn(f.droplsn),
read_only_lsn = GetNumericLsn(f.readonlylsn),
read_write_lsn = GetNumericLsn(f.readwritelsn),
differential_base_lsn = GetNumericLsn(f.diffbaselsn),
differential_base_guid = f.diffbaseguid,
differential_base_time = nullif(f.diffbasetime, 0),
redo_start_lsn = GetNumericLsn(f.redostartlsn),
redo_start_fork_guid = f.redostartforkguid,
redo_target_lsn = GetNumericLsn(f.redotargetlsn),
redo_target_fork_guid = f.forkguid,
backup_lsn = GetNumericLsn(f.backuplsn),
credential_id = cr.credential_id
FROM sys.sysbrickfiles f
LEFT JOIN sys.syspalvalues st ON st.class = 'DBFS' AND st.value = f.filestate
LEFT JOIN sys.syspalvalues ft ON ft.class = 'DBFT' AND ft.value = f.filetype
LEFT JOIN sys.credentials cr ON f.pname LIKE cr.name N'%' COLLATE database_default
WHERE f.dbid < 0x7fff -- consistent with sys.databases
AND f.pruid = 0
AND f.filestate NOT IN (1, 2) -- x_efs_Dummy, x_efs_Dropped
AND has_access('MF', 1) = 1
GO
Resource 数据库
· 每次数据修改（插入、更新或删除）。 这包括系统存储过程或数据定义语言 (DDL) 语句对包括系统表在内的任何表所做的更改。
Resource 数据库是只读数据库，它包含了 SQL Server 中的所有系统对象。SQL Server 系统对象（例如 sys.objects）在物理上持续存在于 Resource 数据库中，但在逻辑上，它们出现在每个数据库的 sys 架构中。Resource 数据库不包含用户数据或用户元数据。
tempdb 数据库
· 每次分配或释放区和页。
可以看出sys.master_files的state值来自于系统基表sys.sysbrickfiles的filestate字段，我们从DAC模式去查看，发现TEST数据库（dbid=21)的filestat为0，这个值应该为6才对，另外，还有一个让人意外的是，这个系统表里面关于TEST数据库有两个事务日志文件记录，实际上只有一个（其实这个是前阵子写这篇博客“MS SQL 事务日志管理小结”时，测试添加、删除数据事务日志文件遗留下来的记录，不清楚是Bug还是什么问题导致在系统基表还存在这样的一条记录）
tempdb 系统数据库是一个全局资源，可供连接到 SQL Server 实例的所有用户使用，并可用于保存下列各项：
显式创建的临时用户对象，例如全局或局部临时表、临时存储过程、表变量或游标。
SQL Server 数据库引擎创建的内部对象，例如，用于存储假脱机或排序的中间结果的工作表。
由使用已提交读（使用行版本控制隔离或快照隔离事务）的数据库中数据修改事务生成的行版本。
由数据修改事务为实现联机索引操作、多个活动的结果集 (MARS) 以及 AFTER 触发器等功能而生成的行版本。
· 创建或删除表或索引。
不能对 tempdb 数据库执行以下操作：
添加文件组。
备份或还原数据库。
更改排序规则。默认排序规则为服务器排序规则。
更改数据库所有者。tempdb 的所有者是 dbo。
创建数据库快照。
删除数据库。
从数据库中删除 guest 用户。
启用变更数据捕获。
参与数据库镜像。
删除主文件组、主数据文件或日志文件。
重命名数据库或主文件组。
运行 DBCC CHECKALLOC。
运行 DBCC CHECKCATALOG。
将数据库设置为 OFFLINE。
将数据库或主文件组设置为 READ_ONLY。
另外，像SELECT这样的操作是不会记录在事务日志当中的。如果你想对事务日志记录信息有一个直观的认识，那么你可以在测试环境做一些SELECT、INSERT、UPDATE、DDL等操作，然后使用ApexSQL Log这款工具查看具体的事务日志记录信息。
USE YourSQLDba;
那么我们接下来看看sys.sysbrickfiles的具体定义，如下所示：
GO
CREATE TABLE dbo.TEST(ID INT);
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE VIEW sys.databases AS
SELECT d.name, d.id AS database_id,
r.indepid AS source_database_id,
d.sid AS owner_sid,
d.crdate AS create_date,
d.cmptlevel AS compatibility_level,
-- coll.value = null means that a collation wasn't specified for the DB and the server default is used instead
convert(sysname, case when serverproperty('EngineEdition') = 5 AND d.id = 1 then serverproperty('collation')
else CollationPropertyFromID(convert(int, isnull(coll.value, p.cid)), 'name') end) AS collation_name,
p.user_access, ua.name AS user_access_desc,
sysconv(bit, d.status & 0x400) AS is_read_only, -- DBR_RDONLY
sysconv(bit, d.status & 1) AS is_auto_close_on, -- DBR_CLOSE_ON_EXIT
sysconv(bit, d.status & 0x400000) AS is_auto_shrink_on, -- DBR_AUTOSHRINK
case when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000020) = 1) then cast (1 as tinyint) -- RESTORING
when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000080) = 1) then cast (7 as tinyint) -- COPYING
when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000100) = 1) then cast (4 as tinyint) -- SUSPECT
else p.state
end AS state, -- 7 is COPYING and 4 is SUSPECT state for database copy (UNDO: Need to have a clean way to set states in dbtable for a user db)
case when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000020) = 1) then 'RESTORING'
when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000080) = 1) then 'COPYING'
when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000100) = 1) then 'SUSPECT'
else st.name
end AS state_desc,
sysconv(bit, d.status & 0x200000) AS is_in_standby, -- DBR_STANDBY
case when serverproperty('EngineEdition') = 5 then convert(bit, 0) else p.is_cleanly_shutdown end AS is_cleanly_shutdown,
sysconv(bit, d.status & 0x80000000) AS is_supplemental_logging_enabled, -- DBR_SUPPLEMENT_LOG
p.snapshot_isolation_state, si.name AS snapshot_isolation_state_desc,
sysconv(bit, d.status & 0x800000) AS is_read_committed_snapshot_on, -- DBR_READCOMMITTED_SNAPSHOT
p.recovery_model, ro.name AS recovery_model_desc,
p.page_verify_option, pv.name AS page_verify_option_desc,
sysconv(bit, d.status2 & 0x1000000) AS is_auto_create_stats_on, -- DBR_AUTOCRTSTATS
sysconv(bit, d.status2 & 0x00400000) AS is_auto_create_stats_incremental_on, -- DBR_AUTOCRTSTATSINC
sysconv(bit, d.status2 & 0x40000000) AS is_auto_update_stats_on, -- DBR_AUTOUPDSTATS
sysconv(bit, d.status2 & 0x80000000) AS is_auto_update_stats_async_on, -- DBR_AUTOUPDSTATSASYNC
sysconv(bit, d.status2 & 0x4000) AS is_ansi_null_default_on, -- DBR_ANSINULLDFLT
sysconv(bit, d.status2 & 0x4000000) AS is_ansi_nulls_on, -- DBR_ANSINULLS
sysconv(bit, d.status2 & 0x2000) AS is_ansi_padding_on, -- DBR_ANSIPADDING
sysconv(bit, d.status2 & 0x10000000) AS is_ansi_warnings_on, -- DBR_ANSIWARNINGS
sysconv(bit, d.status2 & 0x1000) AS is_arithabort_on, -- DBR_ARITHABORT
sysconv(bit, d.status2 & 0x10000) AS is_concat_null_yields_null_on, -- DBR_CATNULL
sysconv(bit, d.status2 & 0x800) AS is_numeric_roundabort_on, -- DBR_NUMEABORT
sysconv(bit, d.status2 & 0x800000) AS is_quoted_identifier_on, -- DBR_QUOTEDIDENT
sysconv(bit, d.status2 & 0x20000) AS is_recursive_triggers_on, -- DBR_RECURTRIG
sysconv(bit, d.status2 & 0x2000000) AS is_cursor_close_on_commit_on, -- DBR_CURSCLOSEONCOM
sysconv(bit, d.status2 & 0x100000) AS is_local_cursor_default, -- DBR_DEFLOCALCURS
sysconv(bit, d.status2 & 0x20000000) AS is_fulltext_enabled, -- DBR_FTENABLED
sysconv(bit, d.status2 & 0x200) AS is_trustworthy_on, -- DBR_TRUSTWORTHY
sysconv(bit, d.status2 & 0x400) AS is_db_chaining_on, -- DBR_DBCHAINING
sysconv(bit, d.status2 & 0x08000000) AS is_parameterization_forced, -- DBR_UNIVERSALAUTOPARAM
sysconv(bit, d.status2 & 64) AS is_master_key_encrypted_by_server, -- DBR_MASTKEY
sysconv(bit, d.status2 & 0x00000010) AS is_query_store_on, -- DBR_QDSENABLED
sysconv(bit, d.category & 1) AS is_published,
sysconv(bit, d.category & 2) AS is_subscribed,
sysconv(bit, d.category & 4) AS is_merge_published,
sysconv(bit, d.category & 16) AS is_distributor,
sysconv(bit, d.category & 32) AS is_sync_with_backup,
d.svcbrkrguid AS service_broker_guid,
sysconv(bit, case when d.scope = 0 then 1 else 0 end) AS is_broker_enabled,
p.log_reuse_wait, lr.name AS log_reuse_wait_desc,
sysconv(bit, d.status2 & 4) AS is_date_correlation_on, -- DBR_DATECORRELATIONOPT
sysconv(bit, d.category & 64) AS is_cdc_enabled,
sysconv(bit, d.status2 & 0x100) AS is_encrypted, -- DBR_ENCRYPTION
convert(bit, d.status2 & 0x8) AS is_honor_broker_priority_on, -- DBR_HONORBRKPRI
sgr.guid AS replica_id,
sgr2.guid AS group_database_id,
ssr.indepid AS resource_pool_id,
default_language_lcid = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(smallint, p.default_language) else null end,
default_language_name = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(sysname, sld.name) else null end,
default_fulltext_language_lcid = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(int, p.default_fulltext_language) else null end,
default_fulltext_language_name = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(sysname, slft.name) else null end,
is_nested_triggers_on = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(bit, p.allow_nested_triggers) else null end,
is_transform_noise_words_on = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(bit, p.transform_noise_words) else null end,
two_digit_year_cutoff = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(smallint, p.two_digit_year_cutoff) else null end,
containment = sysconv(tinyint, (d.status2 & 0x80000)/0x80000), -- DBR_IS_CDB
containment_desc = convert(nvarchar(60), cdb.name),
p.recovery_seconds AS target_recovery_time_in_seconds,
p.delayed_durability,
case when (p.delayed_durability = 0) then CAST('DISABLED' AS nvarchar(60)) -- LCOPT_DISABLED
when (p.delayed_durability = 1) then CAST('ALLOWED' AS nvarchar(60)) -- LCOPT_ALLOWED
when (p.delayed_durability = 2) then CAST('FORCED' AS nvarchar(60)) -- LCOPT_FORCED
else NULL
end AS delayed_durability_desc,
convert(bit, d.status2 & 0x80) AS is_memory_optimized_elevate_to_snapshot_on -- DBR_HKELEVATETOSNAPSHOT
FROM sys.sysdbreg d OUTER APPLY OpenRowset(TABLE DBPROP, (case when serverproperty('EngineEdition') = 5 then DB_ID() else d.id end)) p
LEFT JOIN sys.syssingleobjrefs r ON r.depid = d.id AND r.class = 96 AND r.depsubid = 0 -- SRC_VIEWPOINTDB
LEFT JOIN sys.syspalvalues st ON st.class = 'DBST' AND st.value = p.state
LEFT JOIN sys.syspalvalues ua ON ua.class = 'DBUA' AND ua.value = p.user_access
LEFT JOIN sys.syspalvalues si ON si.class = 'DBSI' AND si.value = p.snapshot_isolation_state
LEFT JOIN sys.syspalvalues ro ON ro.class = 'DBRO' AND ro.value = p.recovery_model
LEFT JOIN sys.syspalvalues pv ON pv.class = 'DBPV' AND pv.value = p.page_verify_option
LEFT JOIN sys.syspalvalues lr ON lr.class = 'LRWT' AND lr.value = p.log_reuse_wait
LEFT JOIN sys.syssingleobjrefs agdb ON agdb.depid = d.id AND agdb.class = 104 AND agdb.depsubid = 0 -- SRC_AVAILABILITYGROUP
LEFT JOIN master.sys.syssingleobjrefs ssr ON ssr.class = 108 AND ssr.depid = d.id -- SRC_RG_DB_TO_POOL
LEFT JOIN master.sys.sysclsobjs ag ON ag.id = agdb.indepid AND ag.class = 67 -- SOC_AVAILABILITY_GROUP
LEFT JOIN master.sys.sysguidrefs sgr ON sgr.class = 8 AND sgr.id = ag.id AND sgr.subid = 1 -- GRC_AGGUID / AGGUID_REPLICA_ID
LEFT JOIN master.sys.sysguidrefs sgr2 ON sgr2.class = 9 AND sgr2.id = ag.id AND sgr2.subid = d.id -- GRC_AGDBGUID
LEFT JOIN sys.syspalvalues cdb ON cdb.class = 'DCDB' AND cdb.value = CASE WHEN (d.status2 & 0x80000)=0x80000 THEN 1 ELSE 0 END
LEFT JOIN sys.syslanguages sld ON sld.lcid = p.default_language
LEFT JOIN sys.fulltext_languages slft ON slft.lcid = p.default_fulltext_language
LEFT JOIN sys.sysobjvalues coll ON coll.valclass = 102 AND coll.subobjid = 0 AND coll.objid = d.id -- SVC_DATACOLLATION
WHERE d.id < 0x7fff
AND has_access('DB', (case when serverproperty('EngineEdition') = 5 then DB_ID() else d.id end)) = 1
GO
GO
INSERT INTO dbo.TEST SELECT 100;
GO
SELECT * FROM dbo.TEST;
GO
EngineEdition |
服务器上安装的 数据库引擎 实例的 SQL Server版本。 |
UPDATE dbo.TEST SET ID=101;
GO
DELETE FROM dbo.TEST WHERE ID=101;
GO
因为当前数据库版本为标准版，所以stated的值来自OpenRowset(TABLE DBPROP, (case when serverproperty('EngineEdition') = 5 then DB_ID() else d.id end)) p
我们可以在单用户专用连接服务器模式下查看相关记录的值。如下截图所示：
如上所示，像DDL、DML操作都会记录在事务日志当中，但是SELECT是不会记录在事务日志当中（当然SELECT INTO除外，其实SELECT INTO在事务日志里面转化为了CREATE TABLE形式）。另外，需要注意: 事务日志并不是审计跟踪。也就是说事务日志并不能完全替代审计跟踪。它不提供对数据库做出改变的审计跟踪；它不保持对数据库已执行命令的记录，只有数据如何改变的结果。其实很多对事务日志了解不深入的人都以为事务日志可以代替审计跟踪（曾经有项目经理想让我从事务日志当中挖掘出谁误删了数据，其实事务日志只会记录那个账号删除了记录，并不会记录客户端信息，所以不能定位到谁删除了数据）。如下所示，我们多了一个DROP TABLE操作。你会看到跟上面不一样的结果。
USE YourSQLDba;
GO
CREATE TABLE dbo.TEST(ID INT);
GO
INSERT INTO dbo.TEST SELECT 100;
其实发现这个问题（其实我更愿意称其为一个bug）是一次查询时的意外发现。实验测试让我有点吃惊。居然这么多版本都是这种情况！ 这到底是一个“bug”还是数据库什么内部机制呢？
GO
SELECT * FROM dbo.TEST;
GO
UPDATE dbo.TEST SET ID=101;
GO
DELETE FROM dbo.TEST WHERE ID=101;
GO
DROP TABLE dbo.Test;
GO
这篇博客transactionlog中有一张图，描述了一个更新操作的流程中，事务日志在这个流程中的位置以及作用。想必看过这张图后，大家在大脑中会对事务日志的功能作用有一个初步的形象认识。
其实这张图还包含了很多隐藏的重要信息，下面我们一一来述说一下：
预写式日志(Write-Ahead Logging）
什么是预写式日志呢？ 其实其核心思想就是在变化的数据写入到数据库之前，将相关日志记录信息先写入到日志. SQL Server的预写式日志（Write-Ahead Logging）机制保证修改的描述（例如日志记录）会在数据本身修改写入数据文件前写入，会写入磁盘上的事务日志文件。它是SQL Server保证事务持久性（Durability）的基本机制。一个日志记录会包含已提交事务或未提交事务的详细信息，在数据被事务修改的不同情况下，可能已经写入数据文件或还没来得及写入数据文件，这取决于检查点是否已发生。
浅谈SQL Server中的事务日志(二)----事务日志在修改数据时的角色 这篇博客有深入浅出的介绍（如下所示）：
Write-Ahead Logging的核心思想是:在数据写入到数据库之前，先写入到日志.
因为对于数据的每笔修改都记录在日志中，所以将对于数据的修改实时写入到磁盘并没有太大意义，即使当SQL Server发生意外崩溃时，在恢复(recovery)过程中那些不该写入已经写入到磁盘的数据会被回滚(RollBack),而那些应该写入磁盘却没有写入的数据会被重做(Redo)。从而保证了持久性(Durability)。
但WAL不仅仅是保证了原子性和持久性。还会提高性能.
硬盘是通过旋转来读取数据,通过WAL技术，每次提交的修改数据的事务并不会马上反映到数据库中，而是先记录到日志.在随后的CheckPoint和Lazy Writer中一并提交,如果没有WAL技术则需要每次提交数据时写入数据库......
官方文档SQL Server 事务日志体系结构和管理指南介绍如下（个人对翻译做了一下调整，也增加了一点点内容）：
要了解预写日志的工作方式，了解如何将修改的数据写入磁盘很重要。SQL Server维护一个缓冲区缓存(buffer cache)，在必须检索数据时从其中读取数据页。 在缓冲区缓存中修改页后，不会将其立即写回磁盘；而是将其标记为“脏”数据。在将数据页物理写入磁盘之前，这些脏数据可以多次被修改。 对于每次逻辑写入，都会在日志缓存(log cache)中插入一条事务日志记录记录这些修改。在将关联的脏页从缓冲区缓存中删除并写入磁盘之前，必须将这条些日志记录写入磁盘。检查点进程定期在缓冲区高速缓存中扫描包含来自指定数据库的页的缓冲区，然后将所有脏页写入磁盘。 CHECKPOINT 可创建一个检查点，在该点保证全部脏页都已写入磁盘，从而在以后的恢复过程中节省时间。
将修改后的数据页从高速缓冲存储器写入磁盘的操作称为刷新页。 SQL Server具有一个逻辑，它可以在写入关联的日志记录前防止刷新脏页。 日志记录将在提交事务时写入磁盘。
检查点作用
检查点将脏数据页从当前数据库的缓冲区高速缓存刷新到磁盘上。这最大限度地减少了数据库完整恢复时必须处理的活动日志,减少的崩溃恢复需要的时间。其实CheckPoint是为了优化IO和减少Recovery时间 在完整恢复时，需执行下列操作：
§ 前滚系统停止之前尚未刷新到磁盘上的日志记录修改信息。
§ 回滚与未完成的事务（如没有 COMMIT 或 ROLLBACK 日志记录的事务）相关联的所有修改。
检查点操作
检查点在数据库中执行下列过程：
· 将记录写入日志文件，标记检查点的开始。
· 将为检查点记录的信息存储在检查点日志记录链内。
· 记录在检查点中的一条信息是第一条日志记录的日志序列号 (LSN)，它必须存在才能成功进行数据库范围内的回滚。 该 LSN 称为“最小恢复 LSN”（“MinLSN”）。 MinLSN 是下列各项中的最小者：
o 检查点开始的 LSN。
o 最早的活动事务起点的 LSN。
o 尚未传递给分发数据库的最早的复制事务起点的 LSN。
o 检查点记录还包含所有已修改数据库的活动事务的列表。
· 如果数据库使用简单恢复模式，检查点则标记在 MinLSN 前重用的空间。
· 将所有脏日志和脏数据页写入磁盘。
· 将标记检查点结束的记录写入日志文件。
· 将这条链起点的 LSN 写入数据库引导页。
导致检查点的活动
下列情形下将出现检查点：
· 显式执行 CHECKPOINT 语句。 用于连接的当前数据库中出现检查点。
· 在数据库中执行了最小日志记录操作，例如，在使用大容量日志恢复模式的数据库中执行大容量复制操作。
· 已经使用 ALTER DATABASE 添加或删除了数据库文件。
· 通过 SHUTDOWN 语句或通过停止 SQL Server (MSSQLSERVER) 服务停止了 SQL Server 实例。 任一操作都会在 SQL Server 实例的每个数据库中生成一个检查点。
· SQL Server 实例在每个数据库内定期生成自动检查点，以减少实例恢复数据库所需的时间。
· 进行了数据库备份。
· 执行了需要关闭数据库的活动。 例如，AUTO_CLOSE 设置为 ON ，并且关闭了数据库的最后一个用户连接，或者执行了需要重新启动数据库的数据库选项更改。
事务日志物理结构
SQL Server数据库中的事务日志可以有一个或多个事务日志文件。当存在多个事务日志文件时，这些日志文件也只能顺序调用，并不能并行使用，因此使用多个日志文件并不会带来性能上的提升（后面内容会展开讨论这个）。其实，如果你对ORACLE当中联机重做日志体系结构非常熟悉的话，多个事务日志文件就相当于多个redo log file，不同的是，ORACLE下面的redo log可以实现多路复用（日志组可以有一个或多个同样的日志成员redo log file，多个日志成员的原因是防止日志文件组内某个日志文件损坏后及时提供备份，所以同一组的日志成员一般内容信息相同，但是存放位置不同）。一般会将同一组的不同日志成员文件放到不同的磁盘或不同的裸设备上。以提高安全性。SQL Server似乎没有这个架构设计。另外，ORACLE的REDO 与UNDO在结构设计上是分开的。而SQL Server可以通过事务日志进行REDO和UNDO操作。
事务日志逻辑结构
从逻辑结构上看，SQL Server对于日志文件的管理，是将逻辑上一个ldf文件划分成多个逻辑上的虚拟日志文件(virtual log files,简称VLFs).以便于管理。SQL Server事务日志按逻辑运行，就好像事务日志是一串日志记录一样。每条日志记录由一个日志序列号 (LSN) 标识。 每条新日志记录均写入日志的逻辑结尾处，并使用一个比前面记录的 LSN 更高的 LSN。 日志记录按创建时的串行序列存储。 每条日志记录都包含其所属事务的 ID。对于每个事务，与事务相关联的所有日志记录通过使用可提高事务回滚速度的向后指针挨个链接在一个链中。 虚拟日志文件没有固定大小，且物理日志文件所包含的虚拟日志文件数不固定。 数据库引擎在创建或扩展日志文件时动态选择虚拟日志文件的大小。 数据库引擎尝试维护少量的虚拟文件。 在扩展日志文件后，虚拟文件的大小是现有日志大小和新文件增量大小之和。 管理员不能配置或设置虚拟日志文件的大小或数量。但是如果设置日志文件的增量过小，则会产生过多的VLFS,也就是日志文件碎片，过多的日志文件碎片会拖累SQL Server性能.因此，指定合适的日志文件初始大小和增长，是减少日志碎片最关键的部分.
事务日志是一种回绕的文件。 例如，假设有一个数据库，它包含一个分成四个虚拟日志文件的物理日志文件。 当创建数据库时，逻辑日志文件从物理日志文件的始端开始。 新日志记录被添加到逻辑日志的末端，然后向物理日志的末端扩张。 日志截断将释放记录全部在最小恢复日志序列号 (MinLSN) 之前出现的所有虚拟日志。 MinLSN 是成功进行数据库范围内回滚所需的最早日志记录的日志序列号。 示例数据库中的事务日志的外观与下图所示相似。
当逻辑日志的末端到达物理日志文件的末端时，新的日志记录将回绕到物理日志文件的始端。
上面关于事务日志的虚拟日志循环覆盖使用是否有点眼熟的感觉，这个跟ORACLE下REDO LOG的循环覆盖使用的理念是一模一样的。只不过是不同的概念和不同的实现方式。
事务日志功能
事务日志有啥功能呢？关于事务日志的功能，详细具体内容可以参考官方文档事务日志 (SQL Server)，里面已经详细介绍了事务日志的几个功能，在此不做展开。
事务日志支持以下操作：
· 恢复个别的事务。
· 在SQL Server启动时恢复所有未完成的事务。
· 将还原的数据库、文件、文件组或页前滚至故障点。
· 支持事务复制。
· 支持高可用性和灾难恢复解决方案： AlwaysOn 可用性组、数据库镜像和日志传送。
事务日志截断
什么是事务日志截断呢？ 在介绍事务日志截断前，我们必须先了解一下MinLSN、活动日志（Actvie Log）等概念。
最小恢复LSN（Minimum Recovery LSN（MinLSN）)概念
MinLSN是在还未结束的事务记录在日志中最小的LSN号,MinLSN是下列三者之一的最小值:
· CheckPoint的开始LSN
· 还未结束的事务在日志的最小LSN
· 尚未传递给分发数据库的最早的复制事务起点的 LSN.
从MinLSN到日志的逻辑结尾处，则称为活动日志(Active Log)。日志文件中从 MinLSN 到最后写入的日志记录这一部分称为日志的活动部分，或者称为活动日志（Active log）。 这是进行数据库完整恢复所需的日志部分。 永远不能截断活动日志的任何部分。所有的日志记录都必须从 MinLSN 之前的日志部分截断。也就是说永远不能截断活动日志的任何部分。
下图显示了具有两个活动事务的结束事务日志的简化版本。 检查点记录已压缩成单个记录。
LSN 148 是事务日志中的最后一条记录。 在处理 LSN 147 处记录的检查点时，Tran 1 已经提交，而 Tran 2 是唯一的活动事务。 这就使 Tran 2 的第一条日志记录成为执行最后一个检查点时处于活动状态的事务的最旧日志记录。 这使 LSN 142（Tran 2 的开始事务记录）成为 MinLSN。
活动日志必须包括所有未提交事务的每一部分。如果应用程序开始执行一个事务但未提交或回滚，将会阻止数据库引擎推进 MinLSN。 这可能会导致两种问题：
如果系统在事务执行了许多未提交的修改后关闭，以后重新启动时，恢复阶段所用的时间将比“恢复间隔”选项指定的时间长得多。
因为不能截断 MinLSN 之后的日志部分，日志可能变得很大。 即使数据库使用的是简单恢复模式，这种情况也有可能出现，在简单恢复模式下，每次执行自动检查点操作时通常都会截断事务日志。
日志截断其实指从SQL Server数据库的逻辑事务日志中删除不活动的虚拟日志文件，释放逻辑日志中的空间以便物理事务日志重用这些空间。 如果事务日志从不截断，它最终将填满分配给物理日志文件的所有磁盘空间。 但是，在截断日志前，必须执行检查点操作。检查点将当前内存中已修改的页（称为“脏页”）和事务日志信息从内存写入磁盘。 执行检查点时，事务日志的不活动部分将标记为可重用。 此后，日志截断可以释放不活动的部分。有关检查点的详细信息，请参阅数据库检查点 (SQL Server)。
关于日志截断，必须定期截断事务日志，防止其占满分配给物理日志文件的磁盘空间。日志截断并不减小物理日志文件的大小。 若要减少物理日志文件的物理大小，则必须收缩日志文件。
日志截断会在下面事件后自动进行截断：
简单恢复模式下，在检查点之后发生。
在完整恢复模式或大容量日志恢复模式下，如果自上一次备份后生成检查点，则在日志备份后进行截断（除非是仅复制日志备份）。
CHECKPOINT only truncates the transaction log (marks the VLF for reuse) only in simple recovery model. In Full recovery, you have to take log backup.
实际上，日志截断会由于多种原因发生延迟。 查询 sys.databases 目录视图的 log_reuse_wait 和 log_reuse_wait_desc 列，了解哪些因素（如果存在）阻止日志截断。 下表对这些列的值进行了说明：
Log_reuse_wait 值 |
Log_reuse_wait_desc 值 |
说明 |
0 |
NOTHING |
当前有一个或多个可重复使用的虚拟日志文件。 |
1 |
CHECKPOINT |
自上次日志截断之后，尚未生成检查点，或者日志头尚未跨一个虚拟日志文件移动。 （所有恢复模式） |
2 |
LOG_BACKUP |
在截断事务日志前，需要进行日志备份。 （仅限完整恢复模式或大容量日志恢复模式） |
3 |
ACTIVE_BACKUP_OR_RESTORE |
数据备份或还原正在进行（所有恢复模式）。 |
4 |
ACTIVE_TRANSACTION |
事务处于活动状态（所有恢复模式）： |
5 |
DATABASE_MIRRORING |
数据库镜像暂停，或者在高性能模式下，镜像数据库明显滞后于主体数据库。 （仅限完整恢复模式） |
6 |
REPLICATION |
在事务复制过程中，与发布相关的事务仍未传递到分发数据库。 （仅限完整恢复模式） |
7 |
DATABASE_SNAPSHOT_CREATION |
正在创建数据库快照。 （所有恢复模式） |
8 |
LOG_SCAN |
发生日志扫描。 （所有恢复模式） |
9 |
AVAILABILITY_REPLICA |
可用性组的辅助副本正将此数据库的事务日志记录应用到相应的辅助数据库。 （完整恢复模式） |
10 |
— |
仅供内部使用 |
11 |
— |
仅供内部使用 |
12 |
— |
仅供内部使用 |
13 |
OLDEST_PAGE |
如果将数据库配置为使用间接检查点，数据库中最早的页可能比检查点 LSN 早。 在这种情况下，最早的页可以延迟日志截断。 （所有恢复模式） |
14 |
OTHER_TRANSIENT |
当前未使用此值。 |
事务日志收缩
有时候我们监控告警会发现事务日志出现暴增的情况，那么此时就必须对是事务日志进行收缩，不管数据库处于那种恢复模式，简单、完整模式。都可以按下面流程进行收缩。
1：查看对应数据库事务日志的逻辑名称（name)，后续操作需要用到。
SELECT database_id ,
name ,
type_desc ,
physical_name
FROM sys.master_files
WHERE database_id = DB_ID('YourSQLDba')
AND type_desc='LOG'
2: 使用DBCC SQLPERF查看事务日志空间使用情况统计信息：
DBCC SQLPERF (LOGSPACE)
如果对应数据库的Log Space Used(%)的值较小，那么就可以收缩事务日志。
3：执行类似下面的收缩事务日志文件语句。
USE YourSQLDba;
GO
DBCC SHRINKFILE('YourSQLDba_Log', 128);
如果Log Space Used(%)很小，而收缩效果又不佳，那么一般是因为日志截断延迟造成，一般可以通过下面脚本检查原因，大部分情况是因为等待LOG_BACKUP缘故。所以你对事务日志做一次备份后，再进行收缩即可解决。
SELECT name ,
log_reuse_wait ,
log_reuse_wait_desc
FROM sys.databases
WHERE database_id = DB_ID('YourSQLDba');
backup log [YourSQLDba]
to disk = 'M:DB_BACKUPLOG_BACKUPYourSQLDba_[2018-01-11_06h37m26_Thu]_logs.TRN'
with noInit, checksum, name = 'YourSQLDba:15h40: M:DB_BACKUPLOG_BACKUPYourSQLDba_[2018-01-11_06h37m26_Thu]_logs.TRN'
增加事务日志文件
SQL Server数据库中的事务日志可以有一个或多个事务日志文件，但是即使有多个事务日志文件，也不能并行写入多个事务日志文件，数据库引擎还是会串行使用多个事务日志文件。也就是说大多数场景，多个事务日志文件其实并没有什意义，那么它存在的意义是什么呢？例如，当你当前磁盘告警，事务日志无法继续增长，你需要在其他磁盘新增一个事务日志文件，让数据库继续顺畅运行。个人觉得多个事务日志文件确实是一个很鸡肋的东西。Paul S. Randal在“了解SQL Server的日志记录和恢复”中明确指出：不要创建多个的日志文件，因为它不会导致性能增益。
下面是如何增加一个事务日志文件的样例：
USE [master]
GO
ALTER DATABASE [YourSQLDba] ADD LOG FILE ( NAME = N'YourSQLDba_Log2', FILENAME = N'D:SQL_LOGYourSQLDba_Log1.LDF' , SIZE = 65536KB , MAXSIZE = 55296KB , FILEGROWTH = 10%)
GO
删除事务日志文件
既然可以增加事务日志文件，那么当然也可以删除事务日志文件，但是这个删除操作是有限制的。主日志文件（primary log）是不能删除的。如果你删除primary log就会报“不能从数据库中删除主数据文件或主日志文件。”，下面我们来测试一下。
准备测试环境如下：
USE master;
GO
CREATE DATABASE [TEST]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'TEST', FILENAME = N'D:SQL_DATATEST.mdf' , SIZE = 100MB , MAXSIZE = 40GB, FILEGROWTH = 64MB )
LOG ON
( NAME = N'TEST_log' , FILENAME = N'D:SQL_LOGTEST_LOG_1.ldf' , SIZE = 20MB , MAXSIZE = 40MB , FILEGROWTH = 10MB),
( NAME = N'TEST_log2', FILENAME = N'D:SQL_LOGTEST_LOG_2.ldf' , SIZE = 20MB , MAXSIZE = 20GB , FILEGROWTH = 10MB)
GO
BACKUP DATABASE [TEST] TO DISK = N'D:DB_BACKUPTest.bak'
WITH NOFORMAT, NOINIT,
NAME = N'TEST-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO
USE TEST;
GO
SELECT * INTO mytest FROM sys.objects;
GO
INSERT INTO mytest
SELECT * FROM mytest
GO 12
DBCC SQLPERF(LOGSPACE)
DBCC LOGINFO('TEST')
注意，此时DBCC LOGINFO显示FileId=3的日志文件对应的虚拟日志(VLF)的Status为2，此时删除事务日志文件会提示文件无法删除，因为Status=2意味着VLF不能被覆盖和重用。
Status = 2 means that VLF can't be reused (overwritten) at this time and it doesn't necessarily mean that VLF is still active and writing transactions to that VLF. As Jonathan already mentioned, it means that the VLF is waiting for backup/REPL/Mirroring etc..
USE master;
GO
ALTER DATABASE TEST REMOVE FILE TEST_log2
备份事务日志后，你会发现FileId=3的日志文件对应的虚拟日志(VLF)的Status变为了0，那么此时就可以移除事务日志文件了。
BACKUP LOG TEST TO DISK = 'D:SQL_LOGTest.Trn'
GO
DBCC LOGINFO('TEST')
GO
USE master;
GO
ALTER DATABASE TEST REMOVE FILE TEST_log2
如果是生产环境或者在上述备份事务日志后，对应日志文件的VLF的状态仍然为2，那么可以用收缩日志文件和备份事务日志循环处理，直至对应日志文件下所有的VLF状态全部为0，就可以删除事务日志文件。
USE TEST;
GO
DBCC SHRINKFILE(TEST_log2);
BACKUP LOG TEST TO DISK = 'D:SQL_LOGTest.Trn'
注意，主日志文件（primary log）是不能删除的，如下测试所示：
USE master;
GO
ALTER DATABASE TEST REMOVE FILE TEST_log
Msg 5020, Level 16, State 1, Line 35
The primary data or log file cannot be removed from a database.
但是当你需要规划存储路径、移动事务日志文件时，你可以使用折中的方法将主事务日志文件（primary log）移动到其它目录。如下所示：
1： 将当前数据库脱机;
ALTER DATABASE TEST SET OFFLINE;
2： 修改数据库的事务日志位置
ALTER DATABASE TEST
MODIFY FILE
(
NAME = N'TEST_log'
, FILENAME = N'E:SQL_LOGTEST_LOG_1.ldf'
)
3： 手工将事务日志文件移动到上面位置
4：将数据库联机操作。
ALTER DATABASE TEST SET ONLINE;
另外，如何判断那个日志文件是主事务日志文件？目前来说，我只能这样判断， sys.master_files当中，file_id最小值对应的日志文件为主事务日志文件。用脚本判断如下：
SELECT f.database_id AS database_id ,
DB_NAME(f.database_id) AS database_name,
MIN(f.file_id) AS primary_log_id ,
f.type_desc AS type_desc
FROM sys.master_files f
WHERE f.database_id= DB_ID('databasename') AND type = 1
GROUP BY f.database_id,f.type_desc;
另外，你也可以用下面脚本查出哪些数据库拥有两个或以上事务日志。
SELECT f.database_id AS database_id ,
d.name AS database_name,
f.type_desc AS type_desc ,
COUNT(*) AS log_count
FROM sys.master_files f
INNER JOIN sys.databases d ON f.database_id = d.database_id
WHERE type = 1
GROUP BY f.database_id ,
f.type_desc,
d.name
HAVING COUNT(*) >= 2;
参考资料：
本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处：系统数据库，Server系统视图sys
TAG标签：
