图片 6

MS SQL 事务日志管理小结

 

Resource 数据库是只读数据库,它包含了 SQL Server 中的所有系统对象。SQL
Server 系统对象(例如 sys.objects)在物理上持续存在于 Resource
数据库中,但在逻辑上,它们出现在每个数据库的 sys 架构中。Resource
数据库不包含用户数据或用户元数据。

DBCC SHRINKFILE(TEST_log2);

 

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

·        
将这条链起点的 LSN
写入数据库引导页。

 

  • 显式创建的临时用户对象,例如全局或局部临时表、临时存储过程、表变量或游标。

  • SQL Server
    数据库引擎创建的内部对象,例如,用于存储假脱机或排序的中间结果的工作表。

  • 由使用已提交读(使用行版本控制隔离或快照隔离事务)的数据库中数据修改事务生成的行版本。

  • 由数据修改事务为实现联机索引操作、多个活动的结果集 (MARS) 以及 AFTER
    触发器等功能而生成的行版本。

 

 

tempdb 数据库

事务日志有啥功能呢?关于事务日志的功能,详细具体内容可以参考官方文档事务日志 (SQL
Server),里面已经详细介绍了事务日志的几个功能,在此不做展开。

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

不能对 tempdb 数据库执行以下操作:

·        
恢复个别的事务。

 

master数据库

 

 

 

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

msdb 数据库

 

 

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

Write-Ahead
Logging的核心思想是:在数据写入到数据库之前,先写入到日志.

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
  • 添加文件或文件组。

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

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

  • 删除数据库。

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

  • 启用变更数据捕获。

  • 参与数据库镜像。

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

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

  • 将数据库设置为 OFFLINE。

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

  • 使用 WITH ENCRYPTION
    选项创建过程、视图或触发器。加密密钥与在其中创建对象的数据库绑定在一起。在
    model 数据库中创建的加密对象只能用于 model 中。

 

 

 

NAME
= N’TEST_log’

 

msdb 数据库由 SQL Server
代理用于计划警报和作业,也可以由其他功能(如 Service Broker
和数据库邮件)使用。

 

 

 

·        
将所有脏日志和脏数据页写入磁盘。

 

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

 

 

  • 添加文件组。

  • 备份或还原数据库。

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

  • 更改数据库所有者。tempdb 的所有者是 dbo

  • 创建数据库快照。

  • 删除数据库。

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

  • 启用变更数据捕获。

  • 参与数据库镜像。

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

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

  • 运行 DBCC CHECKALLOC。

  • 运行 DBCC CHECKCATALOG。

  • 将数据库设置为 OFFLINE。

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

 

 

数据库记录 SQL Server
系统的所有系统级信息。这包括实例范围的元数据(例如登录帐户)、端点、链接服务器和系统配置设置。此外,master
数据库还记录了所有其他数据库的存在、数据库文件的位置以及 SQL Server
的初始化信息。因此,如果 master 数据库不可用,则 SQL Server
无法启动。在 SQL Server 中,系统对象不再存储在 master
数据库中,而是存储在 Resource
数据库.aspx)中。

 

最近发现在SQL
Server数据库(目前测试过SQL Server 2008,
2012,2014,2016各个版本)中,即使数据库处于脱机(OFFLINE)状态,但是sys.master_files中依然显示是联机状态。本文测试环境为Microsoft
SQL Server 2014 (SP2) (KB3171021) – 12.0.5000.0 (X64)
。具体测试过程如下所示:

model 数据库

 

那么我们接下来看看sys.sysbrickfiles的具体定义,如下所示:

  • 添加文件或文件组。

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

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

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

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

  • 删除数据库。

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

  • 启用变更数据捕获。

  • 参与数据库镜像。

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

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

  • 将数据库设置为 OFFLINE。

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

 

 

tempdb 系统数据库是一个全局资源,可供连接到 SQL Server
实例的所有用户使用,并可用于保存下列各项:

 

 

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

Msg
5020, Level 16, State 1, Line 35

 

Resource 数据库

 

 

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

  • 删除数据库。

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

  • 启用变更数据捕获。

  • 参与数据库镜像。

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

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

  • 将数据库设置为 OFFLINE。

  • 将主文件组设置为 READ_ONLY。

增加事务日志文件

图片 1

ALTER
DATABASE TEST

如上所示,sys.databases系统视图正确的显示数据库处于脱机状态(OFFLINE),但是系统视图sys.master_files显示的依然是联机(ONLINE),我们可以获取系统视图sys.master_files的定义,如下所示(至于如何获取视图定义,如果你不清楚,可以参考我的博客SQL
Server查看视图定义总结),

 

 

 

 

 

 

 

 

事务日志介绍

 

 

其实发现这个问题(其实我更愿意称其为一个bug)是一次查询时的意外发现。实验测试让我有点吃惊。居然这么多版本都是这种情况!
这到底是一个“bug”还是数据库什么内部机制呢?

GO

图片 2

 

 

 

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

 

 

 

 

 

可以看出sys.master_files的state值来自于系统基表sys.sysbrickfiles的filestate字段,我们从DAC模式去查看,发现TEST数据库(dbid=21)的filestat为0,这个值应该为6才对,另外,还有一个让人意外的是,这个系统表里面关于TEST数据库有两个事务日志文件记录,实际上只有一个(其实这个是前阵子写这篇博客“MS
SQL 事务日志管理小结”时,测试添加、删除数据事务日志文件遗留下来的记录,不清楚是Bug还是什么问题导致在系统基表还存在这样的一条记录)

 

 

·        
每次分配或释放区和页。

因为当前数据库版本为标准版,所以stated的值来自OpenRowset(TABLE
DBPROP, (case when serverproperty(‘EngineEdition’) = 5 then DB_ID() else d.id end)) p

图片 3

 

 

 

      DBCC
SQLPERF (LOGSPACE)

 

检查点作用

图片 4

 

 

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

USE
YourSQLDba;

 

·        
在SQL
Server启动时恢复所有未完成的事务。

 

UPDATE
dbo.TEST SET ID=101;

 

o   检查点记录还包含所有已修改数据库的活动事务的列表。

 

 

图片 5

The
primary data or log file cannot be removed from a database.

 

 

 

 

 

GO

什么是预写式日志呢?
其实其核心思想就是在变化的数据写入到数据库之前,将相关日志记录信息先写入到日志. SQL
Server的预写式日志(Write-Ahead
Logging)机制保证修改的描述(例如日志记录)会在数据本身修改写入数据文件前写入,会写入磁盘上的事务日志文件。它是SQL
Server保证事务持久性(Durability)的基本机制。一个日志记录会包含已提交事务或未提交事务的详细信息,在数据被事务修改的不同情况下,可能已经写入数据文件或还没来得及写入数据文件,这取决于检查点是否已发生。

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

 

 

 

1:
将当前数据库脱机;

DELETE
FROM dbo.TEST WHERE ID=101;

 

 

 

 

 

 

 

 

 

 

 

  如果Log Space
Used(%)很小,而收缩效果又不佳,那么一般是因为日志截断延迟造成,一般可以通过下面脚本检查原因,大部分情况是因为等待LOG_BACKUP缘故。所以你对事务日志做一次备份后,再进行收缩即可解决。

 

GO

检查点在数据库中执行下列过程:

参考资料:

最小恢复LSN(Minimum Recovery
LSN(MinLSN))概念

 

(

GO

关于日志截断,必须定期截断事务日志,防止其占满分配给物理日志文件的磁盘空间。日志截断并不减小物理日志文件的大小。 若要减少物理日志文件的物理大小,则必须收缩日志文件。

 

o   尚未传递给分发数据库的最早的复制事务起点的 LSN。

 

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

 

GO

 

GO

备份事务日志后,你会发现FileId=3的日志文件对应的虚拟日志(VLF)的Status变为了0,那么此时就可以移除事务日志文件了。

 

GO

 

 

图片 6

 

·        
在数据库中执行了最小日志记录操作,例如,在使用大容量日志恢复模式的数据库中执行大容量复制操作。

 

 

 

 

 

UPDATE
dbo.TEST SET ID=101;

 

另外,如何判断那个日志文件是主事务日志文件?目前来说,我只能这样判断,
sys.master_files当中,file_id最小值对应的日志文件为主事务日志文件。用脚本判断如下:

 

 

 

 

 

 

 

 

 

·        
执行了需要关闭数据库的活动。 例如,AUTO_CLOSE 设置为 ON
,并且关闭了数据库的最后一个用户连接,或者执行了需要重新启动数据库的数据库选项更改。

 

 

 

 

    
但WAL不仅仅是保证了原子性和持久性。还会提高性能.

 

 

    如果系统在事务执行了许多未提交的修改后关闭,以后重新启动时,恢复阶段所用的时间将比“恢复间隔”选项指定的时间长得多。

LSN
148 是事务日志中的最后一条记录。 在处理 LSN 147
处记录的检查点时,Tran 1 已经提交,而 Tran 2 是唯一的活动事务。 这就使 Tran 2
的第一条日志记录成为执行最后一个检查点时处于活动状态的事务的最旧日志记录。 这使 LSN 142(Tran 2
的开始事务记录)成为 MinLSN。

 

 

 

 

   

 

 

 

 

GO

 

 

 

但是当你需要规划存储路径、移动事务日志文件时,你可以使用折中的方法将主事务日志文件(primary
log)移动到其它目录。如下所示:

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_BACKUP\LOG_BACKUP\YourSQLDba_[2018-01-11_06h37m26_Thu]_logs.TRN' 

with noInit, checksum, name = 'YourSQLDba:15h40: M:\DB_BACKUP\LOG_BACKUP\YourSQLDba_[2018-01-11_06h37m26_Thu]_logs.TRN'

 

 

 

 

·        
进行了数据库备份。