问题[transaction-log](dba)

DB2 UDB 11.5.7.0

$ db2 get db cfg for PRPT01D | grep  LOGARCH
 First log archive method                 (LOGARCHMETH1) = DISK:/udb/alogs/
 Archive compression for logarchmeth1    (LOGARCHCOMPR1) = OFF
 Options for logarchmeth1                  (LOGARCHOPT1) = 
 Second log archive method                (LOGARCHMETH2) = OFF
 Archive compression for logarchmeth2    (LOGARCHCOMPR2) = OFF
 Options for logarchmeth2                  (LOGARCHOPT2) = 


$ db2 list history backup all for PRPT01D | head

                    List History File for PRPT01D

Number of matching file entries = 1


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20241006050916001   N    D  S0002434.LOG S0002434.LOG                
 ----------------------------------------------------------------------------
$ db2 "prune logfile prior to S0002323.LOG"
DB20000I  The PRUNE command completed successfully.

即使运行该命令后，旧日志文件仍然存在/udb/alogs/udbinst/PRPT01D/NODE0000/LOGSTREAM0000/C0000000。我是不是从根本上误解了什么？我可以只保留rm那些存档日志文件吗？

• 据我所知，WAL 是一个仅追加文件，对 DB 的所有操作都会在对数据实际执行操作之前写入其中。
• 还有一个“检查点”的概念，即数据库实际将数据从内存写入磁盘，并在 WAL 末尾附加一个特殊的检查点条目。
• 现在，如果数据库在任何时候崩溃，它可以从最新的检查点条目开始读取 WAL 并重做所有后续操作。
• 但是数据库如何确保检查点 WAL 条目和数据实际刷新到磁盘以事务方式进行？
• 如果数据已刷新但数据库在 WAL 中创建检查点条目之前崩溃了，该怎么办？
• 相反，如果首先修改了 WAL，那么如果在检查点条目之后但在实际刷新数据之前 DB 崩溃，会发生什么情况。

例如，考虑以下情况：

• 我们有一张虚拟表Person(name, age, salary)。
• 它有一个条目John, 25, 100。
• 在时间T1，一个新的交易到达UPDATE Person SET salary += 100 WHERE name='John'。
• 假设在 T1 之前，所有数据都已被刷新并且检查点条目已附加到 WAL。
• 现在，在此事务之后，数据库将首先在日志中附加精确的事务语句UPDATE Person SET salary += 100 WHERE name='John'。
• 现在数据变成了John, 25, 200。
• 然后过了一段时间，假设数据库决定在时间 T2 将数据刷新到磁盘。
• 然后在时间 T3（紧接着 T2）时，DB 尝试将检查点条目写入 WAL。
• 然而，航班尚未完成，T2和T3之间就发生了电源故障。
• 现在，当数据库重新启动并尝试恢复时，它会注意到最新检查点之后有一个事务，并将尝试执行该事务：UPDATE Person SET salary += 100 WHERE name='John'
• 但由于该交易在崩溃之前已经执行，这次工资将取值为 300，尽管它应该是 200。

数据库如何在恢复期间防止这些冗余更新？

我了解 sql server 将更新写入内存中的数据页（使它们变脏），并在向用户发送提交通知之前将 tlog 写入磁盘。

这个问题是在以下任一情况下提出的：

1. 将脏页从内存刷新到磁盘后
2. 或者在重新启动后 sql 崩溃

sql server 如何知道哪些脏页（由于最近的一组 tlogs 导致的数据更改）已写入磁盘或尚未写入磁盘？

我正在阅读sp_flush_log的文档，其中指出：

将当前数据库的事务日志刷新到磁盘，从而强化所有先前提交的延迟持久事务。

还会sp_flush_log导致未提交事务的日志记录写入磁盘吗？

我正在阅读有关 SQL Server延迟持久性功能的文档，并看到了这张表：

它指出数据库级别设置始终优先于提交级别查看。但我无法理解这张桌子。

据我了解，2 是数据库级别设置，3 必须是提交级别设置。但是为什么他们的交叉点位置 4 表明结果是延迟持久的呢？

有人可以帮我理解如何阅读这张表吗？

当对Temporal Table执行 DML 和 DDL 查询时，这些更改会适当地传播到相关的 History 表。例如UPDATE，临时表会导致将新记录INSERT编辑到历史表中以反映该更改。

是INSERT记录在事务日志中的历史表以及其他传播的更改吗？

这是否意味着启用临时表将有效地使通过事务日志的数据量翻倍（至少对于恢复模型设置为完整的数据库）？

在我之前的问题中，我询问了如何知道我的 SQL-Server 2019 数据库中发生了什么。过了一会儿，我发现有一种表格，叫做fn_dblog，它提供了一些有趣的信息，但我仍然有一些问题（字面意思是从上一个问题复制而来）：

SELECT Top 10 [Begin Time] [End Time]
  FROM fn_dblog(null,null)
  WHERE UPPER(AllocUnitName) LIKE '%TABLENAME_STATUS%'

...由我的 Microsoft SQL Server Management Studio 提出的位置[Begin Time]和位置。[End Time]令我惊讶的是，结果如下所示：

Title: End Time
NULL
...
NULL

现在我有以下问题：

• [Begin Time]柱子怎么了？
• 为什么[End Time]没有填写？
• 当我将普通的 SQL 查询结果复制到剪贴板时，列名也会被复制，但是当我复制与 相关的 SQL 查询的结果时fn_dblog，列名不会出现在剪贴板中。为什么会这样，我该如何改变呢？

除此之外，我还发现了另一个“表”，名为fn_full_dblog，但更糟糕的是：

SELECT Top 10 * 
  FROM sys.fn_full_dblog(null,null, null, null, null, null, 
                         null, null, null, null,null)
  WHERE UPPER(AllocUnitName) LIKE '%TABLENAME_STATUS%'

=> 根本没有结果！

有人可以给我一些关于如何阅读fn_dblog以及可能fn_full_dblog或任何其他可能使用有趣信息的“表格”的信息吗？

提前致谢

假设数据文件在 D: 并且日志文件在 E:

假设 E 驱动器崩溃并且日志文件 (.ldf) 丢失。我将一个新的空磁盘附加到 E: 并启动 sql server。

启动时，SQL Server 将意识到 .mdf 文件存在，但日志文件不存在。由于缺少日志文件，SQL 服务器将无法执行任何撤消/重做恢复步骤，并将数据库视为可疑对象。

注意：我试过这个并注意到数据库进入了可疑模式。这里的最佳做法是使用备份（完整 + tlog）来恢复数据库。在我的情况下，我从上午 12 点开始进行完整备份，但没有 tlog 备份。如果我从上午 12 点的备份中恢复，那么我会丢失一整天的数据（假设崩溃发生在中午）。我正在尝试通过利用现有数据文件来考虑如何在这种情况下进行。

1. 在这种情况下如何恢复数据库？
2. 说数据丢失只是那些在 tlog 文件中但未写入磁盘的事务是否正确？

事务日志中数据修改的日志记录：是只包含受影响的行和列的前后状态还是包含所有列（受影响的行）的前后状态，即日志记录包含整行还是仅包含被修改的行的一部分？

谢谢

编辑：我现在创建了一个实验，似乎表中的列数不会影响数据修改的事务日志的大小，即如果我col2在表中更新，那么表是否有 5 列或100 列，事务日志的增长将是相同的。

我正在运行 SQL Server 2019 企业版。每个月，我们都会运行一个存储过程来加载数百万条服务日期可以追溯到五年前的记录。我为 4 个数据文件分配了 400GB 的空间，并为日志文件分配了 100GB 的空间。作业经常失败，因为日志文件因活动事务而被填满。数据库处于简单恢复模式。所以，我相信它应该在每笔交易结束时清除。开发人员更改了工作，使其一次循环并加载一年的记录。

DROP TABLE IF EXISTS #UnpvtDx;

SELECT ClaimHeader_ID
    ,ClaimDetail_ID
    ,ClaimServiceLine
    ,Unpvt.CodeLine
    ,Unpvt.DxCode 
INTO #UnpvtDx
FROM PRINCE.Claim.ClaimDetail det WITH (NOLOCK)
UNPIVOT 
(
    DxCode FOR CodeLine
    IN
    (
        Diagnosis1CD,Diagnosis2CD,Diagnosis3CD,Diagnosis4CD,Diagnosis5CD,
        Diagnosis6CD,Diagnosis7CD,Diagnosis8CD,Diagnosis9CD,
        Diagnosis10CD,Diagnosis11CD,Diagnosis12CD,Diagnosis13CD
    )
) as Unpvt ---53 secs
WHERE YEAR(ServiceFromDT) = @year;

DROP TABLE IF EXISTS #UnpvtPointer;

SELECT ClaimHeader_ID
    ,ClaimDetail_ID
    ,ClaimServiceLine
    ,Unpvt.CodeLine
    ,Unpvt.Pointer 
INTO #UnpvtPointer
FROM PRINCE.Claim.ClaimDetail det WITH (NOLOCK)
UNPIVOT 
(
    Pointer FOR CodeLine 
    IN (DiagPointer1,DiagPointer2,DiagPointer3,DiagPointer4)
) as Unpvt ---40 secs
WHERE YEAR(ServiceFromDT) = @year;

INSERT INTO PROD.Claim.ClaimDiag
(
    ClaimHeader_ID,ClaimDetail_ID,SourceID,EDWLoadDTS,PartnerCD,
    PartnerNM,ClaimID,ClaimServiceLine,ClaimStatus,CCOMemberID,
    MemberID,PlaceOfServiceCD,ServiceFromDT,ServiceToDT,ClaimForm,
    TypeOfBillCD,DiagnosisCD,DiagnosisDESC,DiagPointer
)
SELECT DISTINCT
    det.ClaimHeader_ID,det.ClaimDetail_ID,det.SourceID,det.EDWLoadDTS,
    det.PartnerCD,det.PartnerNM,det.ClaimID,det.ClaimServiceLine,
    det.ClaimStatus,det.CCOMemberID,det.MemberID,det.PlaceOfServiceCD,
    det.ServiceFromDT,det.ServiceToDT,det.ClaimForm,det.TypeOfBillCD,
    DiagnosisCD = dx.DxCode,
    DiagnosisDESC = diag.DiagnosisDESC,
    DiagPointer = point.Pointer
FROM PROD.Claim.ClaimDetail det WITH (NOLOCK)
INNER JOIN PROD.Claim.ClaimHeader ch WITH (NOLOCK)
    ON ch.ClaimHeader_ID = det.ClaimHeader_ID
INNER JOIN #UnpvtDx dx
    ON dx.ClaimDetail_ID = det.ClaimDetail_ID
        AND dx.ClaimHeader_ID = det.ClaimHeader_ID
        AND dx.ClaimServiceLine = det.ClaimServiceLine
LEFT JOIN #UnpvtPointer point
    ON point.ClaimDetail_ID = det.ClaimDetail_ID
        AND point.ClaimHeader_ID = det.ClaimHeader_ID
        AND point.ClaimServiceLine = det.ClaimServiceLine
LEFT OUTER JOIN Reference.Reference.Diagnosis diag WITH (NOLOCK)
    ON dx.DxCode = diag.DiagnosisCD
        AND diag.ICDVersion = 'ICD10CM'
        AND diag.ActiveFLG = 1
WHERE YEAR(det.ServiceFromDT) = @year;

使用以下命令从 SQL 代理作业执行存储过程：

DECLARE @year INT
DECLARE cur CURSOR FOR 

SELECT yr = YEAR(hdr.MinServiceFromDT)
FROM PROD.Claim.ClaimHeader hdr WITH (NOLOCK)   
GROUP BY YEAR(hdr.MinServiceFromDT)
ORDER BY YEAR(hdr.MinServiceFromDT)

OPEN cur
FETCH NEXT FROM cur INTO @year
WHILE @@FETCH_STATUS = 0  
BEGIN  
    EXEC Claim.sp_UpdateClaimDiag @year

    FETCH NEXT FROM cur INTO @year      
END 

CLOSE cur
DEALLOCATE cur

循环的结束是否被视为一个事务，因此日志文件应该在处理完每一年的记录后清空，或者日志文件是否继续填充，直到作业遍历每个循环并加载所有年份的记录？

我还将日志文件增加到 150GB，但这会最大化可用空间（不会低于 10% 的缓冲区）。

发布整个存储过程代码。

USE [Prod]
GO
    
SET ANSI_NULLS ON
GO
    
SET QUOTED_IDENTIFIER ON
GO
    
CREATE PROCEDURE [Claim].[sp_UpdateClaimDiag] @year INT
AS
    
BEGIN
    SET ANSI_DEFAULTS, ARITHABORT, NOCOUNT ON
    SET IMPLICIT_TRANSACTIONS OFF
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    
    -- variable declaration
    DECLARE
        @transactional BIT
        , @trancount INT
        , @err INT
        , @procname SYSNAME
        , @error INT
        , @message VARCHAR(4000)
        , @xstate INT
        , @RecordCount int;
    
    SELECT @procname = OBJECT_SCHEMA_NAME(@@PROCID, DB_ID()) + '.' + OBJECT_NAME(@@PROCID, DB_ID())
        -- 0 =  no: will not execute batches inside a transaction; a partial success of procedure is possible
        -- 1 = yes: batches in procedure will be bound together by a transaction, partial success is impossible
        , @transactional = 0 
    
        -- optionally begin transaction and begin try block
        IF @transactional = 1 SET @trancount = @@TRANCOUNT

        BEGIN TRY
            IF @trancount = 0 and @transactional = 1
                BEGIN TRANSACTION

            ELSE IF @transactional = 1
                SAVE TRANSACTION p1
    
            ----------------------------------------------------------------------------------------
            ---Unpivot columns to Rows into Temp tables
            ----------------------------------------------------------------------------------------
                
            ---ICD9CM  & ICD10CM
            DROP TABLE IF EXISTS #UnpvtDx;

            SELECT
                ClaimHeader_ID
                ,ClaimDetail_ID
                ,ClaimServiceLine
                ,Unpvt.CodeLine
                ,Unpvt.DxCode 
            INTO #UnpvtDx
            FROM Prod.Claim.ClaimDetail det WITH (NOLOCK)
            UNPIVOT 
            (
                DxCode FOR CodeLine IN
                (
                    Diagnosis1CD,Diagnosis2CD,Diagnosis3CD,
                    Diagnosis4CD,Diagnosis5CD,Diagnosis6CD,
                    Diagnosis7CD,Diagnosis8CD,Diagnosis9CD,
                    Diagnosis10CD,Diagnosis11CD,Diagnosis12CD,
                    Diagnosis13CD
                )
            ) as Unpvt ---53 secs
            WHERE YEAR(ServiceFromDT) = @year;

            --Select top 100 * from #UnpvtDx where DxCode is null
    
            DROP TABLE IF EXISTS #UnpvtPointer;

            SELECT
                ClaimHeader_ID
                ,ClaimDetail_ID
                ,ClaimServiceLine
                ,Unpvt.CodeLine
                ,Unpvt.Pointer 
            INTO #UnpvtPointer
            FROM Prod.Claim.ClaimDetail det WITH (NOLOCK)
            UNPIVOT 
            (
                Pointer FOR CodeLine IN
                (
                    DiagPointer1, DiagPointer2,
                    DiagPointer3,DiagPointer4
                )
            ) as Unpvt ---40 secs
            WHERE YEAR(ServiceFromDT) = @year;
    
            --Select top 100 * from #UnpvtPointer
                
            ----------------------------------------------------------------------------------------
            --- INSERT INTO yearly records from the temp table
            ----------------------------------------------------------------------------------------
        
            INSERT INTO Prod.Claim.ClaimDiag (
                ClaimHeader_ID,
                ClaimDetail_ID,
                SourceID,
                EDWLoadDTS,
                PartnerCD,
                PartnerNM,
                ClaimID,
                ClaimServiceLine,
                ClaimStatus,
                CCOMemberID,
                MemberID,
                PlaceOfServiceCD,
                ServceFromDT,
                ServiceToDT,
                ClaimForm,
                TypeOfBillCD,
                DiagnosisCD,
                DiagnosisDESC,
                DiagPointer
            )
    
            SELECT DISTINCT
                det.ClaimHeader_ID,
                det.ClaimDetail_ID,
                det.SourceID,
                det.EDWLoadDTS,
                det.PartnerCD,
                det.PartnerNM,
                det.ClaimID,
                det.ClaimServiceLine,
                det.ClaimStatus,
                det.CCOMemberID,
                det.MemberID,
                det.PlaceOfServiceCD,
                det.ServiceFromDT,
                det.ServiceToDT,
                det.ClaimForm,
                det.TypeOfBillCD,
                DiagnosisCD = dx.DxCode,
                DiagnosisDESC = diag.DiagnosisDESC,
                DiagPointer = point.Pointer
            FROM Prod.Claim.ClaimDetail det WITH (NOLOCK)
            INNER JOIN Prod.Claim.ClaimHeader ch WITH (NOLOCK)
                ON ch.ClaimHeader_ID = det.ClaimHeader_ID
            INNER JOIN #UnpvtDx dx
                ON dx.ClaimDetail_ID = det.ClaimDetail_ID
                    AND dx.ClaimHeader_ID = det.ClaimHeader_ID
                    AND dx.ClaimServiceLine = det.ClaimServiceLine
            LEFT JOIN #UnpvtPointer point
                ON point.ClaimDetail_ID = det.ClaimDetail_ID
                    AND point.ClaimHeader_ID = det.ClaimHeader_ID
                    AND point.ClaimServiceLine = det.ClaimServiceLine
            LEFT OUTER JOIN Reference.Reference.Diagnosis diag WITH (NOLOCK)
                ON dx.DxCode = diag.DiagnosisCD
                    AND diag.ICDVersion = 'ICD10CM'
                    AND diag.ActiveFLG = 1
            WHERE YEAR(det.ServiceFromDT) = @year
                --AND Year(det.ServiceFromDT) = 2021--for testing
                --and det.ClaimID ='21006E06455'--for testing
    
            ----------------------------------------------------------------------------------------
            --insert into updatelog table
            SET @RecordCount = @@ROWCOUNT;
    
            DECLARE @procName1 SYSNAME
            SET @procName1 = @procname + ' ' + CAST(@year AS varchar(4))
            INSERT INTO Prod.dbo.UpdateLog(EventTimestamp,EventDescription,ProcName,TableName)
            SELECT GETDATE(),
                'Inserted ' + CAST(@RecordCount AS varchar(100)) + ' records',
                @procName1,
                'Claim.ClaimDiag'
    ----------------------------------------------------------------------------------------
            DROP TABLE IF EXISTS #UnpvtDx
            DROP TABLE IF EXISTS #UnpvtPointer
    ----------------------------------------------------------------------------------------
            SPEXIT:

            IF @transactional = 1 and @trancount = 0 COMMIT
        END TRY

        ----------------------------------------------------------------------------------------
        -- error handling with catch
        BEGIN CATCH
            SELECT @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE()
            IF @transactional = 1 and @xstate = -1 ROLLBACK
            IF @transactional = 1 and @xstate = 1 and @trancount = 0 ROLLBACK
            IF @transactional = 1 and @xstate = 1 and @trancount > 0 ROLLBACK TRANSACTION p1
        
            DROP TABLE IF EXISTS #claims
            SET @procName1 = @procname + ' ' + CAST(@year AS varchar(4)) ---+ ', ' + CAST(@month AS varchar(4)) 
    
            RAISERROR ('%s, Error %d, %s', 16, 1, @procname1, @error, @message) 
            RETURN @error
        END CATCH
    
        RETURN 0  
    END
    GO