无主键和时间戳列，如何统计近10天插入记录数？fn_dblog异常排查

需要统计目标表EHSRegis近10天插入的记录数，但该表既无主键，也未添加时间戳列。曾尝试使用fn_dblog函数查询事务日志，语句如下：

SELECT 
    [Current LSN],
    [Operation],
    [Transaction ID],
    [Begin Time],
    [End Time],
    [Transaction Name],
    [Transaction SID],    
    [Page ID],
    [Slot ID],
    [Lock Information],
    [XAct ID],    
    [AllocUnitName],
    [Num Elements] 
FROM 
    fn_dblog(NULL, NULL)
WHERE 
    Operation = 'LOP_INSERT_ROWS'
    AND AllocUnitName LIKE '%EHSRegis%';

但遇到以下问题：

• 查询结果可读性差，难以直接统计插入数；
• 手动插入记录后仅能短暂看到结果，后续查询返回空值；
• [Begin Time]和[End Time]字段始终为NULL。

方法1：关联事务日志获取插入时间与统计数 #

fn_dblog仅能读取未被截断的事务日志，且插入操作本身不记录事务时间，需关联事务的开始/结束操作获取时间：

WITH InsertOperations AS (
    SELECT 
        [Transaction ID],
        [Num Elements] AS RowsInserted,
        [AllocUnitName]
    FROM fn_dblog(NULL, NULL)
    WHERE 
        Operation = 'LOP_INSERT_ROWS'
        AND AllocUnitName LIKE '%EHSRegis%'
),
TransactionDetails AS (
    SELECT 
        [Transaction ID],
        MAX(CASE WHEN Operation = 'LOP_BEGIN_XACT' THEN [Begin Time] END) AS TransactionStart,
        MAX(CASE WHEN Operation = 'LOP_COMMIT_XACT' THEN [End Time] END) AS TransactionEnd
    FROM fn_dblog(NULL, NULL)
    GROUP BY [Transaction ID]
)
SELECT 
    td.TransactionStart,
    td.TransactionEnd,
    SUM(io.RowsInserted) AS TotalInsertedRows
FROM InsertOperations io
JOIN TransactionDetails td ON io.[Transaction ID] = td.[Transaction ID]
WHERE td.TransactionStart >= DATEADD(DAY, -10, GETDATE())
GROUP BY td.TransactionStart, td.TransactionEnd
ORDER BY td.TransactionStart DESC;

注意：如果事务日志已被截断（如简单恢复模式下的自动截断、日志备份后截断），此方法无法获取历史数据。

方法2：通过数据库备份恢复统计 #

若存在完整备份+近10天的日志备份，可通过恢复备份到不同时间点计算差值：

• 1. 将最近的完整备份恢复到测试数据库，恢复选项选择NORECOVERY；
• 2. 依次恢复近10天内的所有日志备份，每次均使用NORECOVERY，直到恢复至10天前的时间点；
• 3. 统计测试库中EHSRegis表的记录数，记为CountBefore；
• 4. 继续恢复日志到当前时间点（或最近的日志备份），统计表记录数，记为CountNow；
• 5. 近10天插入数 = CountNow - CountBefore。

方法3：临时添加时间列（允许修改表结构时） #

这是最可靠的长期解决方案，适合可以临时修改表结构的场景：

• 1. 给目标表添加自动记录插入时间的列：

ALTER TABLE EHSRegis ADD InsertedAt DATETIME DEFAULT GETDATE();

• 2. 统计近10天插入记录：

SELECT COUNT(*) AS Recent10DaysInserts 
FROM EHSRegis 
WHERE InsertedAt >= DATEADD(DAY, -10, GETDATE());

• 若后续不需要该列，统计完成后可执行ALTER TABLE EHSRegis DROP COLUMN InsertedAt;删除。

• 手动插入后结果消失：事务日志会在检查点（简单恢复模式）或日志备份后自动截断，插入操作的日志记录被清除，导致后续查询无结果；
• Begin Time/End Time为NULL：LOP_INSERT_ROWS是事务内的具体操作，时间字段仅在事务开始（LOP_BEGIN_XACT）和结束（LOP_COMMIT_XACT）的日志记录中存在，需关联事务ID才能获取。

内容的提问来源于stack exchange，提问作者Sumeet Kumar