系统数据库,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';
  • 添加文件或文件组。

  • 更改排序规则。默认排序规则为服务器排序规则。

  • 更改数据库所有者。masterdbo 所有。

  • 创建全文目录或全文索引。

  • 在数据库的系统表上创建触发器。

  • 删除数据库。

  • 从数据库中删除 guest 用户。

  • 启用变更数据捕获。

  • 参与数据库镜像。

  • 删除主文件组、主数据文件或日志文件。

  • 重命名数据库或主文件组。

  • 将数据库设置为 OFFLINE。

  • 将数据库或主文件组设置为 READ_ONLY。

 

 

model 数据库

事务日志介绍

 

model 数据库用作在 SQL Server 实例上创建的所有数据库的模板。因为每次启动 SQL Server 时都会创建 tempdb,所以 model 数据库必须始终存在于 SQL Server 系统中。

 

图片 1

 

 

 

不能在 model 数据库中执行下列操作:

 

 

  • 添加文件或文件组。

  • 更改排序规则。默认排序规则为服务器排序规则。

  • 更改数据库所有者。modeldbo 所有。

  • 删除数据库。

  • 从数据库中删除 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。

 

图片 2

另外,像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版本。

1 = Personal 或 Desktop Engine(不适用于 SQL Server 2005?和更高版本。)

2 = Standard(对 Standard、Web 和 Business Intelligence 返回该值。)

3 = Enterprise(对 Enterprise、Developer 以及两个 Enterprise 版本返回该值。)

4 = Express(对 Express、Express with Tools 和 Express with Advanced Services 返回该值)

5 = SQL Database

6 = SQL 数据仓库

8 = 托管实例

基本数据类型:int

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

 

 

 

我们可以在单用户专用连接服务器模式下查看相关记录的值。如下截图所示:

图片 3

 

 

 

如上所示,像DDL、DML操作都会记录在事务日志当中,但是SELECT是不会记录在事务日志当中(当然SELECT INTO除外,其实SELECT INTO在事务日志里面转化为了CREATE TABLE形式)。另外,需要注意: 事务日志并不是审计跟踪。也就是说事务日志并不能完全替代审计跟踪。它不提供对数据库做出改变的审计跟踪;它不保持对数据库已执行命令的记录,只有数据如何改变的结果。其实很多对事务日志了解不深入的人都以为事务日志可以代替审计跟踪(曾经有项目经理想让我从事务日志当中挖掘出谁误删了数据,其实事务日志只会记录那个账号删除了记录,并不会记录客户端信息,所以不能定位到谁删除了数据)。如下所示,我们多了一个DROP TABLE操作。你会看到跟上面不一样的结果。

图片 4

 

 

USE YourSQLDba;

 

GO

图片 5

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

 

 

图片 6

 

 

这篇博客transactionlog中有一张图,描述了一个更新操作的流程中,事务日志在这个流程中的位置以及作用。想必看过这张图后,大家在大脑中会对事务日志的功能作用有一个初步的形象认识。

 

 

图片 7

 

 

 

其实这张图还包含了很多隐藏的重要信息,下面我们一一来述说一下:

 

 

预写式日志(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操作。

 

 

图片 8

 

 

事务日志逻辑结构

 

 

从逻辑结构上看,SQL Server对于日志文件的管理,是将逻辑上一个ldf文件划分成多个逻辑上的虚拟日志文件(virtual log files,简称VLFs).以便于管理。SQL Server事务日志按逻辑运行,就好像事务日志是一串日志记录一样。每条日志记录由一个日志序列号 (LSN) 标识。 每条新日志记录均写入日志的逻辑结尾处,并使用一个比前面记录的 LSN 更高的 LSN。 日志记录按创建时的串行序列存储。 每条日志记录都包含其所属事务的 ID。对于每个事务,与事务相关联的所有日志记录通过使用可提高事务回滚速度的向后指针挨个链接在一个链中。 虚拟日志文件没有固定大小,且物理日志文件所包含的虚拟日志文件数不固定。 数据库引擎在创建或扩展日志文件时动态选择虚拟日志文件的大小。 数据库引擎尝试维护少量的虚拟文件。 在扩展日志文件后,虚拟文件的大小是现有日志大小和新文件增量大小之和。 管理员不能配置或设置虚拟日志文件的大小或数量。但是如果设置日志文件的增量过小,则会产生过多的VLFS,也就是日志文件碎片,过多的日志文件碎片会拖累SQL Server性能.因此,指定合适的日志文件初始大小和增长,是减少日志碎片最关键的部分.

 

 

事务日志是一种回绕的文件。 例如,假设有一个数据库,它包含一个分成四个虚拟日志文件的物理日志文件。 当创建数据库时,逻辑日志文件从物理日志文件的始端开始。 新日志记录被添加到逻辑日志的末端,然后向物理日志的末端扩张。 日志截断将释放记录全部在最小恢复日志序列号 (MinLSN) 之前出现的所有虚拟日志。 MinLSN 是成功进行数据库范围内回滚所需的最早日志记录的日志序列号。 示例数据库中的事务日志的外观与下图所示相似。

 

图片 9

 

当逻辑日志的末端到达物理日志文件的末端时,新的日志记录将回绕到物理日志文件的始端。

 

 

 

图片 10

 

上面关于事务日志的虚拟日志循环覆盖使用是否有点眼熟的感觉,这个跟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 之前的日志部分截断。也就是说永远不能截断活动日志的任何部分。

 

 

下图显示了具有两个活动事务的结束事务日志的简化版本。 检查点记录已压缩成单个记录。

 

图片 11 图片 12 图片 13

 

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

自上次日志截断之后,尚未生成检查点,或者日志头尚未跨一个虚拟日志文件移动。 (所有恢复模式)

这是日志截断延迟的常见原因。 有关详细信息,请参阅数据库检查点 (SQL Server)。

2

LOG_BACKUP

在截断事务日志前,需要进行日志备份。 (仅限完整恢复模式或大容量日志恢复模式)

完成下一个日志备份后,一些日志空间可能变为可重复使用。

3

ACTIVE_BACKUP_OR_RESTORE

数据备份或还原正在进行(所有恢复模式)。

如果数据备份阻止了日志截断,则取消备份操作可能有助于解决备份直接导致的此问题。

4

ACTIVE_TRANSACTION

事务处于活动状态(所有恢复模式):

一个长时间运行的事务可能存在于日志备份的开头。 在这种情况下,可能需要进行另一个日志备份才能释放空间。 请注意,长时间运行的事务将阻止所有恢复模式下的日志截断,包括简单恢复模式,在该模式下事务日志一般在每个自动检查点截断。

延迟事务。 “延迟的事务 ”是有效的活动事务,因为某些资源不可用,其回滚受阻。 有关导致事务延迟的原因以及如何使它们摆脱延迟状态的信息,请参阅延迟的事务 (SQL Server)。

长时间运行的事务也可能会填满 tempdb 的事务日志。 Tempdb 由用户事务隐式用于内部对象,例如用于排序的工作表、用于哈希的工作文件、游标工作表,以及行版本控制。 即使用户事务只包括读取数据(SELECT 查询),也可能会以用户事务的名义创建和使用内部对象, 然后就会填充 tempdb 事务日志。

5

DATABASE_MIRRORING

数据库镜像暂停,或者在高性能模式下,镜像数据库明显滞后于主体数据库。 (仅限完整恢复模式)

有关详细信息,请参阅数据库镜像 (SQL Server)。

6

REPLICATION

在事务复制过程中,与发布相关的事务仍未传递到分发数据库。 (仅限完整恢复模式)

有关事务复制的信息,请参阅 SQL Server Replication。

7

DATABASE_SNAPSHOT_CREATION

正在创建数据库快照。 (所有恢复模式)

这是日志截断延迟的常见原因,通常也是主要原因。

8

LOG_SCAN

发生日志扫描。 (所有恢复模式)

这是日志截断延迟的常见原因,通常也是主要原因。

9

AVAILABILITY_REPLICA

可用性组的辅助副本正将此数据库的事务日志记录应用到相应的辅助数据库。 (完整恢复模式)

有关详细信息,请参阅:AlwaysOn 可用性组概述 (SQL Server)。

10

仅供内部使用

11

仅供内部使用

12

仅供内部使用

13

OLDEST_PAGE

如果将数据库配置为使用间接检查点,数据库中最早的页可能比检查点 LSN 早。 在这种情况下,最早的页可以延迟日志截断。 (所有恢复模式)

有关间接检查点的信息,请参阅数据库检查点 (SQL Server)。

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')

 

 

图片 14

 

 

注意,此时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

 

 

 

 

图片 15

 

 

备份事务日志后,你会发现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

 

 

图片 16

 

 

 

如果是生产环境或者在上述备份事务日志后,对应日志文件的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标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。