【MSSQL】分析数据库日志文件无法收缩的问题

曾经终败给现在 2023-09-26 17:53 128阅读 0赞

## 一、问题描述 ##

在SQL Server 2008R2数据库中，无法对数据库日志进行收缩，导致日志不断膨胀。

## 二、问题分析 ##

由于是日志文件不断增大且无法收缩，所以初步判断为存在未提交的事务。检查可能阻止日志阶段的活动事务，执行：

DBCC OPENTRAN

![img][]

通过结果发现存在活动的事务。

进而检查数据库可能延迟日志截断的因素，执行：

SELECT name, log_reuse_wait_desc FROM sys.databases WHERE name = 'dbname'

可以从结果看出待检查的数据库状态为**REPLICATION**，在事务复制过程中，与发布相关的事务仍未传递到分发数据库。

## 三、问题处理 ##

由于是开设了发布服务器实例的发布数据库上或 SQL Server 订阅服务器实例的订阅数据库上的复制对象，所以删除不再使用的复制数据库即可结束当前事务。执行：

EXEC sp_removedbreplication 'dbname'

删除成功后，再进行日志收缩就能将日志截断。

USE [dbname]
    GO
     
    ALTER DATABASE [dbname] SET RECOVERY SIMPLE WITH NO_WAIT
    GO
    DBCC SHRINKFILE (N'dbname_log', 0, TRUNCATEONLY)
    GO
    ALTER DATABASE [dbname] SET RECOVERY FULL WITH NO_WAIT
    GO

## 四、技术梳理 ##

### 4.1 sys.databases ###

为 SQL Server实例中的每个数据库都包含一行。具体列的描述详见[SQL Server官网sys.databases][SQL Server_sys.databases]。

这里主要关注**log\_reuse\_wait\_desc**字段：

<table> 
 <thead> 
  <tr> 
   <th align="left">log_reuse_wait 值</th> 
   <th align="left">log_reuse_wait_desc 值</th> 
   <th align="left">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">0</td> 
   <td align="left">NOTHING</td> 
   <td align="left">当前有一个或多个可重复使用的<a href="https://learn.microsoft.com/zh-cn/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide?view=sql-server-ver16#physical_arch" rel="nofollow">虚拟日志文件 (VLF)</a>。</td> 
  </tr> 
  <tr> 
   <td align="left">1</td> 
   <td align="left">CHECKPOINT</td> 
   <td align="left">自上次日志截断之后，尚未生成检查点，或者日志头尚未跨一个<a href="https://learn.microsoft.com/zh-cn/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide?view=sql-server-ver16#physical_arch" rel="nofollow">虚拟日志 (VLF)</a> 文件移动。 （所有恢复模式） 这是日志截断延迟的常见原因。 有关详细信息，请参阅<a href="https://learn.microsoft.com/zh-cn/sql/relational-databases/logs/database-checkpoints-sql-server?view=sql-server-ver16" rel="nofollow">数据库检查点 (SQL Server)</a>。</td> 
  </tr> 
  <tr> 
   <td align="left">2</td> 
   <td align="left">LOG_BACKUP</td> 
   <td align="left">在截断事务日志前，需要进行日志备份。 （仅限完整恢复模式或大容量日志恢复模式） 完成下一个日志备份后，一些日志空间可能变为可重复使用。</td> 
  </tr> 
  <tr> 
   <td align="left">3</td> 
   <td align="left">ACTIVE_BACKUP_OR_RESTORE</td> 
   <td align="left">数据备份或还原正在进行（所有恢复模式）。 如果数据备份阻止了日志截断，则取消备份操作可能有助于解决备份直接导致的此问题。</td> 
  </tr> 
  <tr> 
   <td align="left">4</td> 
   <td align="left">ACTIVE_TRANSACTION</td> 
   <td align="left">事务处于活动状态（所有恢复模式）： 一个长时间运行的事务可能存在于日志备份的开头。 在这种情况下，可能需要进行另一个日志备份才能释放空间。 请注意，长时间运行的事务将阻止所有恢复模式下的日志截断，包括简单恢复模式，在该模式下事务日志一般在每个自动检查点截断。 延迟事务。 “延迟的事务 ”是有效的活动事务，因为某些资源不可用，其回滚受阻。 有关导致事务延迟的原因以及如何使其摆脱延迟状态的信息，请参阅<a href="https://learn.microsoft.com/zh-cn/sql/relational-databases/backup-restore/deferred-transactions-sql-server?view=sql-server-ver16" rel="nofollow">延迟的事务 (SQL Server)</a>。 长时间运行的事务也可能会填满 tempdb 的事务日志。 Tempdb 由用户事务隐式用于内部对象，例如用于排序的工作表、用于哈希的工作文件、游标工作表，以及行版本控制。 即使用户事务只包括读取数据（<code>SELECT</code> 查询），也可能会以用户事务的名义创建和使用内部对象， 然后就会填充 tempdb 事务日志。</td> 
  </tr> 
  <tr> 
   <td align="left">5</td> 
   <td align="left">DATABASE_MIRRORING</td> 
   <td align="left">数据库镜像暂停，或者在高性能模式下，镜像数据库明显滞后于主体数据库。 （仅限完整恢复模式） 有关详细信息，请参阅<a href="https://learn.microsoft.com/zh-cn/sql/database-engine/database-mirroring/database-mirroring-sql-server?view=sql-server-ver16" rel="nofollow">数据库镜像 (SQL Server)</a>。</td> 
  </tr> 
  <tr> 
   <td align="left">6</td> 
   <td align="left"><strong>REPLICATION</strong></td> 
   <td align="left">在事务复制过程中，与发布相关的事务仍未传递到分发数据库。 （仅限完整恢复模式） 有关事务复制的信息，请参阅 <a href="https://learn.microsoft.com/zh-cn/sql/relational-databases/replication/sql-server-replication?view=sql-server-ver16" rel="nofollow">SQL Server Replication</a>。</td> 
  </tr> 
  <tr> 
   <td align="left">7</td> 
   <td align="left">DATABASE_SNAPSHOT_CREATION</td> 
   <td align="left">正在创建数据库快照。 （所有恢复模式） 这是日志截断延迟的常见原因，通常也是主要原因。</td> 
  </tr> 
  <tr> 
   <td align="left">8</td> 
   <td align="left">LOG_SCAN</td> 
   <td align="left">发生日志扫描。 （所有恢复模式） 这是日志截断延迟的常见原因，通常也是主要原因。</td> 
  </tr> 
  <tr> 
   <td align="left">9</td> 
   <td align="left">AVAILABILITY_REPLICA</td> 
   <td align="left">可用性组的辅助副本正将此数据库的事务日志记录应用到相应的辅助数据库。 （完整恢复模式） 有关详细信息，请参阅 <a href="https://learn.microsoft.com/zh-cn/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server?view=sql-server-ver16" rel="nofollow">Always On 可用性组概述 (SQL Server)</a>。</td> 
  </tr> 
  <tr> 
   <td align="left">10</td> 
   <td align="left">-</td> 
   <td align="left">仅供内部使用</td> 
  </tr> 
  <tr> 
   <td align="left">11</td> 
   <td align="left">-</td> 
   <td align="left">仅供内部使用</td> 
  </tr> 
  <tr> 
   <td align="left">12</td> 
   <td align="left">-</td> 
   <td align="left">仅供内部使用</td> 
  </tr> 
  <tr> 
   <td align="left">13</td> 
   <td align="left">OLDEST_PAGE</td> 
   <td align="left">如果将数据库配置为使用间接检查点，数据库中最早的页可能比检查点<a href="https://learn.microsoft.com/zh-cn/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide?view=sql-server-ver16#Logical_Arch" rel="nofollow">日志序列号 (LSN)</a> 早。 在这种情况下，最早的页可以延迟日志截断。 （所有恢复模式） 有关间接检查点的信息，请参阅<a href="https://learn.microsoft.com/zh-cn/sql/relational-databases/logs/database-checkpoints-sql-server?view=sql-server-ver16" rel="nofollow">数据库检查点 (SQL Server)</a>。</td> 
  </tr> 
  <tr> 
   <td align="left">14</td> 
   <td align="left">OTHER_TRANSIENT</td> 
   <td align="left">当前未使用此值。</td> 
  </tr> 
  <tr> 
   <td align="left">16</td> 
   <td align="left">XTP_CHECKPOINT</td> 
   <td align="left">需要执行内存中 OLTP 检查点。对于内存优化表，如果上次检查点后事务日志文件变得大于 1.5 GB（包括基于磁盘的表和内存优化表），则执行自动检查点 有关详细信息，请参阅<a href="https://learn.microsoft.com/zh-cn/sql/relational-databases/in-memory-oltp/checkpoint-operation-for-memory-optimized-tables?view=sql-server-ver16" rel="nofollow">内存优化表的检查点操作</a>和<a href="https://blogs.msdn.microsoft.com/sqlcat/2016/05/20/logging-and-checkpoint-process-for-memory-optimized-tables-2/" rel="nofollow">内存中优化表的日志记录和检查点流程</a></td> 
  </tr> 
 </tbody> 
</table>

### 4.2 DBCC OPENTRAN ###

帮助识别可能阻止日志截断的活动事务。 如果在指定数据库的事务日志中存在最早的活动事务以及最早的分布式和非分布式复制事务，`DBCC OPENTRAN` 将显示与之相关的信息。 仅当日志中存在活动事务或数据库包含复制信息时，才显示结果。 如果日志中没有活动事务，则显示信息性消息。

语法：

DBCC OPENTRAN
    [
        ( [ database_name | database_id | 0 ] )
        { [ WITH TABLERESULTS ]
          [ , [ NO_INFOMSGS ] ]
        }
    ]

参数：

*  database\_name | database\_id | 0：数据库的名称或数据库的id，使用0时，表示使用当前数据库。
 *  TABLERESULTS：以表格格式指定结果以便可以加载到表中。 使用此选项创建结果表，可以将该结果表插入到表中以进行比较。 未指定此选项时，对结果进行格式化以增加可读性。
 *  NO\_INFOMSGS：取消显示所有信息性消息。

若存在活动事务，则会输出相关活动事务信息。

### 4.3 DBCC LOGINFO ###

每一个数据库至少有一个日志文件，无论为事务日志定义多个少物理文件，SQL Server均视为一个连续的文件。该事务日志文件实际上由一系列的虚拟日志文件VLF来管理。虚拟日志文件的大小由SQL Server的总日志文件的大小决定。

当该日志文件收缩时，日志文件末端的未使用的VLF可以被删除。

执行`DBCC LOGINFO`可获得相关的信息：

![img][img 1]

Status状态为2时，代表了它为一个活动的虚拟日志文件，此时将无法收缩日志文件。只有该日志做了备份或截断，其空间才可以被释放。

BACKUP LOG dbname WITH NO_TRUNCATE

> 执行典型日志备份后，如果没有指定 `WITH NO_TRUNCATE` 或 `COPY_ONLY`，某些事务日志记录将变为不活动状态。 一个或多个虚拟日志文件中的所有记录变为不活动状态后，日志将被截断。 如果日志在常规日志备份后未被截断，则可能是某些操作延迟了日志截断。 有关详细信息，请参阅[可能延迟日志截断的因素][Link 1]。

### 4.4 DBCC SHRINKFILE ###

收缩当前数据库的指定数据或日志文件大小。 可以使用它将一个文件中的数据移到同一文件组中的其他文件，这会清空文件，从而允许删除数据库。 可以将文件收缩到小于创建大小，同时将最小文件大小重置为新值。

语法：

DBCC SHRINKFILE   
    (  
        { file_name | file_id }   
        { [ , EMPTYFILE ]   
        | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]  
        }  
    )  
    [ WITH 
      {     
          [ WAIT_AT_LOW_PRIORITY 
            [ ( 
                <wait_at_low_priority_option_list>
            )] 
          ] 
          [ , NO_INFOMSGS]
      }
    ]

参数：

*  file\_name | file\_id：要收缩的文件的逻辑名称 | 要收缩的文件的标识 (ID) 号（可以通过查询[sys.database\_files][sys.database_files]获取file\_id的值）。
 *  target\_size：整数，表示文件的新大小（以 MB 为单位）。 如果未指定或为 0，`DBCC SHRINKFILE` 缩小到文件创建大小。
 *  NOTRUNCATE：无论是否指定 target\_percent，将数据文件末尾中的已分配页移到文件开头的未分配页区域中。 操作系统不会回收文件末尾的可用空间，文件的物理大小也不会改变。 因此，如果指定 `NOTRUNCATE`，文件看起来就像没有收缩一样。`NOTRUNCATE` 只适用于数据文件。 日志文件不受影响。
 *  TRUNCATEONLY：将文件末尾的所有可用空间释放给操作系统，但不在文件内部移动任何页。 数据文件只收缩到最后分配的区。如果使用 `TRUNCATEONLY` 指定，则会忽略 target\_size。

[img]: https://img-blog.csdn.net/20141211130813859?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQva2sxODU4MDA5NjE=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[SQL Server_sys.databases]: https://learn.microsoft.com/zh-cn/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-ver16
[img 1]: https://img-blog.csdn.net/20141211132048637?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQva2sxODU4MDA5NjE=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Link 1]: https://learn.microsoft.com/zh-cn/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-ver16#FactorsThatDelayTruncation
[sys.database_files]: https://learn.microsoft.com/zh-cn/sql/relational-databases/system-catalog-views/sys-database-files-transact-sql?view=sql-server-ver16