通过

解决事务日志已满的问题（SQL Server 错误 9002）

  • 项目

适用于：SQL Server

本文适用于 SQL Server 实例。

注意

本文侧重介绍 SQL Server。 有关 Azure SQL 平台中此错误的特定详细信息，请参阅排查 Azure SQL 数据库中的事务日志错误和排查 Azure SQL 托管实例中的事务日志错误。 Azure SQL 数据库和 Azure SQL 托管实例基于最新稳定版本的 Microsoft SQL Server 数据库引擎，因此很多内容是相似的，不过故障排除选项和工具可能有所不同。

选项 1：通过 Azure Data Studio 直接在可执行笔记本中运行这些步骤

注意

在尝试打开此笔记本之前，请检查本地计算机上是否安装了 Azure Data Studio。 若要安装，请参阅了解如何安装 Azure Data Studio。

在 Azure Data Studio 中打开笔记本

选项 2：手动执行步骤

本文讨论对已满事务日志可以采取的几种应对措施，并就以后如何避免出现已满事务日志给出建议。

如果事务日志已满，则 SQL Server 数据库引擎 会发出 9002 错误。 当数据库联机或恢复时，日志可能会满。 如果日志在数据库处于联机状态时已满，则该数据库仍会保持联机状态，但只能读取，不能更新。 如果恢复过程中日志已满，则 数据库引擎 将数据库标记为 RESOURCE PENDING。 不管哪种情况，都需要用户执行操作才能使日志空间可用。

导致已满事务日志的常见原因

对已满事务日志的正确响应取决于导致日志已满的情况。 常见原因包括：

  • 日志未截断
  • 磁盘卷已满
  • 日志大小设置为固定的最大值或禁用自动增长
  • 无法完成复制或可用性组同步

如何解决已满事务日志

以下具体步骤将有助于找到已满事务日志的原因并解决该问题。

1. 截断日志

此问题的一个非常常见的解决方案是确保为数据库执行事务日志备份，此操作将确保日志被截断。 如果没有最近的事务日志历史记录指示具有已满事务日志的数据库，则此问题的解决方案很简单，即恢复数据库的常规事务日志备份。

有关详细信息，请参阅管理日志大小和收缩文件。

日志截断说明

截断事务日志和收缩事务日志是有区别的。 日志截断是一种逻辑操作，通常发生在事务日志备份期间，用于删除日志中已提交的记录；而日志收缩通过减小文件大小来回收文件系统上的物理空间。 日志截断发生在虚拟日志文件 (VLF) 边界上，并且一个日志文件可能包含许多 VLF。 仅当某个日志文件内有可回收的空白空间时，才能收缩该日志文件。 仅收缩日志文件并不能解决完整日志文件的问题。 相反，必须找出日志文件已满且不能截断的原因。

警告

被移动用来收缩文件的数据可以分布到文件的任何可用位置。 这将导致索引碎片并可能会使搜索索引范围的查询变慢。 若要消除碎片，请考虑在收缩后重新生成文件的索引。 有关详细信息，请参阅收缩数据库。

是什么阻止了日志截断？

若要发现在给定情况下阻止日志截断的内容，请使用 sys.databases 目录视图的 log_reuse_wait 和 log_reuse_wait_desc 列。 有关详细信息，请参阅 sys.databases (Transact-SQL)。 有关延迟日志截断的因素的说明，请参阅事务日志 (SQL Server)。

下面的一组 T-SQL 命令有助于确定数据库事务日志是否未被截断及其原因。 以下脚本还将推荐解决此问题的步骤：

SET NOCOUNT ON
DECLARE @SQL VARCHAR (8000), @log_reuse_wait tinyint, @log_reuse_wait_desc nvarchar(120), @dbname sysname, @database_id int, @recovery_model_desc varchar (24)

IF ( OBJECT_id (N'tempdb..#CannotTruncateLog_Db') is not null)
BEGIN
    DROP TABLE #CannotTruncateLog_Db
END

--get info about transaction logs in each database.

IF ( OBJECT_id (N'tempdb..#dm_db_log_space_usage') is not null)
BEGIN
    DROP TABLE #dm_db_log_space_usage
END
SELECT * INTO #dm_db_log_space_usage FROM sys.dm_db_log_space_usage where 1=0

DECLARE log_space CURSOR FOR SELECT NAME FROM sys.databases
OPEN log_space

FETCH NEXT FROM log_space into @dbname

WHILE @@FETCH_STATUS = 0
BEGIN

    set @SQL = '
    insert into #dm_db_log_space_usage (
    database_id,
    total_log_size_in_bytes,
    used_log_space_in_bytes,
    used_log_space_in_percent,
    log_space_in_bytes_since_last_backup
    )
    select
    database_id,
    total_log_size_in_bytes,
    used_log_space_in_bytes,
    used_log_space_in_percent,
    log_space_in_bytes_since_last_backup
    from ' + @dbname +'.sys.dm_db_log_space_usage'

    BEGIN TRY
        exec (@SQL)
    END TRY

    BEGIN CATCH
        SELECT ERROR_MESSAGE() AS ErrorMessage;
    END CATCH;

    FETCH NEXT FROM log_space into @dbname
END

CLOSE log_space
DEALLOCATE log_space

--select the affected databases
SELECT
    sdb.name as DbName,
    sdb.log_reuse_wait, sdb.log_reuse_wait_desc,
    log_reuse_wait_explanation = CASE

        WHEN log_reuse_wait = 1 THEN 'No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond'
        WHEN log_reuse_wait = 2 THEN 'A log backup is required before the transaction log can be truncated.'
        WHEN log_reuse_wait = 3 THEN 'A data backup or a restore is in progress (all recovery models). Please wait or cancel backup'
        WHEN log_reuse_wait = 4 THEN 'A long-running active transaction or a deferred transaction is keeping log from being truncated. You can attempt a log backup to free space or complete/rollback long transaction'
        WHEN log_reuse_wait = 5 THEN 'Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database. (Full recovery model only)'
        WHEN log_reuse_wait = 6 THEN 'During transactional replication, transactions relevant to the publications are still undelivered to the distribution database. Investigate the status of agents involved in replication or Changed Data Capture (CDC). (Full recovery model only.)'
        WHEN log_reuse_wait = 7 THEN 'A database snapshot is being created. This is a routine, and typically brief, cause of delayed log truncation.'
        WHEN log_reuse_wait = 8 THEN 'A transaction log scan is occurring. This is a routine, and typically a brief cause of delayed log truncation.'
        WHEN log_reuse_wait = 9 THEN 'A secondary replica of an availability group is applying transaction log records of this database to a corresponding secondary database. (Full recovery model only.)'
        WHEN log_reuse_wait = 13 THEN 'If a database is configured to use indirect checkpoints, the oldest page on the database might be older than the checkpoint log sequence number (LSN).'
        WHEN log_reuse_wait = 16 THEN 'An In-Memory OLTP checkpoint has not occurred since the last log truncation, or the head of the log has not yet moved beyond a VLF.'
    ELSE 'None' END,

    sdb.database_id,
    sdb.recovery_model_desc,
    lsu.used_log_space_in_bytes / 1024 as Used_log_size_MB,
    lsu.total_log_size_in_bytes / 1024 as Total_log_size_MB,
    100 - lsu.used_log_space_in_percent as Percent_Free_Space
INTO #CannotTruncateLog_Db
FROM sys.databases AS sdb INNER JOIN #dm_db_log_space_usage lsu ON sdb.database_id = lsu.database_id
WHERE log_reuse_wait > 0

SELECT * FROM #CannotTruncateLog_Db

DECLARE no_truncate_db CURSOR FOR
    SELECT log_reuse_wait, log_reuse_wait_desc, DbName, database_id, recovery_model_desc FROM #CannotTruncateLog_Db;

OPEN no_truncate_db

FETCH NEXT FROM no_truncate_db into @log_reuse_wait, @log_reuse_wait_desc, @dbname, @database_id, @recovery_model_desc

WHILE @@FETCH_STATUS = 0
BEGIN
    if (@log_reuse_wait > 0)
        select '-- ''' + @dbname +  ''' database has log_reuse_wait = ' + @log_reuse_wait_desc + ' --'  as 'Individual Database Report'

    if (@log_reuse_wait = 1)
    BEGIN
        select 'Consider running the checkpoint command to attempt resolving this issue or further t-shooting may be required on the checkpoint process. Also, examine the log for active VLFs at the end of file' as Recommendation
        select 'USE ''' + @dbname+ '''; CHECKPOINT' as CheckpointCommand
        select 'select * from sys.dm_db_log_info(' + CONVERT(varchar,@database_id)+ ')' as VLF_LogInfo
    END
    else if (@log_reuse_wait = 2)
    BEGIN
        select 'Is '+ @recovery_model_desc +' recovery model the intended choice for ''' + @dbname+ ''' database? Review recovery models and determine if you need to change it. https://learn.microsoft.com/sql/relational-databases/backup-restore/recovery-models-sql-server' as RecoveryModelChoice
        select 'To truncate the log consider performing a transaction log backup on database ''' + @dbname+ ''' which is in ' + @recovery_model_desc +' recovery model. Be mindful of any existing log backup chains that could be broken' as Recommendation
        select 'BACKUP LOG [' + @dbname + '] TO DISK = ''some_volume:\some_folder\' + @dbname + '_LOG.trn ''' as BackupLogCommand
    END
    else if (@log_reuse_wait = 3)
    BEGIN
        select 'Either wait for or cancel any active backups currently running for database ''' +@dbname+ '''. To check for backups, run this command:' as Recommendation
        select 'select * from sys.dm_exec_requests where command like ''backup%'' or command like ''restore%''' as FindBackupOrRestore
    END
    else if (@log_reuse_wait = 4)
    BEGIN
        select 'Active transactions currently running  for database ''' +@dbname+ '''. To check for active transactions, run these commands:' as Recommendation
        select 'DBCC OPENTRAN (''' +@dbname+ ''')' as FindOpenTran
        select 'select database_id, db_name(database_id) dbname, database_transaction_begin_time, database_transaction_state, database_transaction_log_record_count, database_transaction_log_bytes_used, database_transaction_begin_lsn, stran.session_id from sys.dm_tran_database_transactions dbtran left outer join sys.dm_tran_session_transactions stran on dbtran.transaction_id = stran.transaction_id where database_id = ' + CONVERT(varchar, @database_id) as FindOpenTransAndSession
    END

    else if (@log_reuse_wait = 5)
    BEGIN
        select 'Database Mirroring for database ''' +@dbname+ ''' is behind on synchronization. To check the state of DBM, run the commands below:' as Recommendation
        select 'select db_name(database_id), mirroring_state_desc, mirroring_role_desc, mirroring_safety_level_desc from sys.database_mirroring where mirroring_guid is not null and mirroring_state <> 4 and database_id = ' + convert(sysname, @database_id)  as CheckMirroringStatus

        select 'Database Mirroring for database ''' +@dbname+ ''' may be behind: check unsent_log, send_rate, unrestored_log, recovery_rate, average_delay in this output' as Recommendation
        select 'exec msdb.sys.sp_dbmmonitoraddmonitoring 1; exec msdb.sys.sp_dbmmonitorresults ''' + @dbname+ ''', 5, 0; waitfor delay ''00:01:01''; exec msdb.sys.sp_dbmmonitorresults ''' + @dbname+ '''; exec msdb.sys.sp_dbmmonitordropmonitoring'   as CheckMirroringStatusAnd
    END

    else if (@log_reuse_wait = 6)
    BEGIN
        select 'Replication transactions still undelivered from publisher database ''' +@dbname+ ''' to Distribution database. Check the oldest non-distributed replication transaction. Also check if the Log Reader Agent is running and if it has encountered any errors' as Recommendation
        select 'DBCC OPENTRAN  (''' + @dbname + ''')' as CheckOldestNonDistributedTran
        select 'select top 5 * from distribution..MSlogreader_history where runstatus in (6, 5) or error_id <> 0 and agent_id = find_in_mslogreader_agents_table  order by time desc ' as LogReaderAgentState
    END

    else if (@log_reuse_wait = 9)
    BEGIN
        select 'Always On transactions still undelivered from primary database ''' +@dbname+ ''' to Secondary replicas. Check the Health of AG nodes and if there is latency is Log block movement to Secondaries' as Recommendation
        select 'select availability_group=cast(ag.name as varchar(30)), primary_replica=cast(ags.primary_replica as varchar(30)),primary_recovery_health_desc=cast(ags.primary_recovery_health_desc as varchar(30)), synchronization_health_desc=cast(ags.synchronization_health_desc as varchar(30)),ag.failure_condition_level, ag.health_check_timeout, automated_backup_preference_desc=cast(ag.automated_backup_preference_desc as varchar(10))  from sys.availability_groups ag join sys.dm_hadr_availability_group_states ags on ag.group_id=ags.group_id' as CheckAGHealth
        select 'SELECT  group_name=cast(arc.group_name as varchar(30)), replica_server_name=cast(arc.replica_server_name as varchar(30)), node_name=cast(arc.node_name as varchar(30)),role_desc=cast(ars.role_desc as varchar(30)), ar.availability_mode_Desc, operational_state_desc=cast(ars.operational_state_desc as varchar(30)), connected_state_desc=cast(ars.connected_state_desc as varchar(30)), recovery_health_desc=cast(ars.recovery_health_desc as varchar(30)), synchronization_health_desc=cast(ars.synchronization_health_desc as varchar(30)), ars.last_connect_error_number, last_connect_error_description=cast(ars.last_connect_error_description as varchar(30)), ars.last_connect_error_timestamp, primary_role_allow_connections_desc=cast(ar.primary_role_allow_connections_desc as varchar(30)) from sys.dm_hadr_availability_replica_cluster_nodes arc join sys.dm_hadr_availability_replica_cluster_states arcs on arc.replica_server_name=arcs.replica_server_name join sys.dm_hadr_availability_replica_states ars on arcs.replica_id=ars.replica_id join sys.availability_replicas ar on ars.replica_id=ar.replica_id join sys.availability_groups ag on ag.group_id = arcs.group_id and ag.name = arc.group_name ORDER BY cast(arc.group_name as varchar(30)), cast(ars.role_desc as varchar(30))' as CheckReplicaHealth
        select 'select database_name=cast(drcs.database_name as varchar(30)), drs.database_id, drs.group_id, drs.replica_id, drs.is_local,drcs.is_failover_ready,drcs.is_pending_secondary_suspend, drcs.is_database_joined, drs.is_suspended, drs.is_commit_participant, suspend_reason_desc=cast(drs.suspend_reason_desc as varchar(30)), synchronization_state_desc=cast(drs.synchronization_state_desc as varchar(30)), synchronization_health_desc=cast(drs.synchronization_health_desc as varchar(30)), database_state_desc=cast(drs.database_state_desc as varchar(30)), drs.last_sent_lsn, drs.last_sent_time, drs.last_received_lsn, drs.last_received_time, drs.last_hardened_lsn, drs.last_hardened_time,drs.last_redone_lsn, drs.last_redone_time, drs.log_send_queue_size, drs.log_send_rate, drs.redo_queue_size, drs.redo_rate, drs.filestream_send_rate, drs.end_of_log_lsn, drs.last_commit_lsn, drs.last_commit_time, drs.low_water_mark_for_ghosts, drs.recovery_lsn, drs.truncation_lsn, pr.file_id, pr.error_type, pr.page_id, pr.page_status, pr.modification_time from sys.dm_hadr_database_replica_cluster_states drcs join sys.dm_hadr_database_replica_states drs on drcs.replica_id=drs.replica_id and drcs.group_database_id=drs.group_database_id left outer join sys.dm_hadr_auto_page_repair pr on drs.database_id=pr.database_id  order by drs.database_id' as LogMovementHealth
        select 'For more information see https://learn.microsoft.com/troubleshoot/sql/availability-groups/error-9002-transaction-log-large' as OnlineDOCResource
    END
    else if (@log_reuse_wait in (10, 11, 12, 14))
    BEGIN
        select 'This state is not documented and is expected to be rare and short-lived' as Recommendation
    END
    else if (@log_reuse_wait = 13)
    BEGIN
        select 'The oldest page on the database might be older than the checkpoint log sequence number (LSN). In this case, the oldest page can delay log truncation.' as Finding
        select 'This state should be short-lived, but if you find it is taking a long time, you can consider disabling Indirect Checkpoint temporarily' as Recommendation
        select 'ALTER DATABASE [' +@dbname+ '] SET TARGET_RECOVERY_TIME = 0 SECONDS' as DisableIndirectCheckpointTemporarily
    END
    else if (@log_reuse_wait = 16)
    BEGIN
        select 'For memory-optimized tables, an automatic checkpoint is taken when transaction log file becomes bigger than 1.5 GB since the last checkpoint (includes both disk-based and memory-optimized tables)' as Finding
        select 'Review https://blogs.msdn.microsoft.com/sqlcat/2016/05/20/logging-and-checkpoint-process-for-memory-optimized-tables-2/' as ReviewBlog
        select 'use ' +@dbname+ ' CHECKPOINT' as RunCheckpoint
    END

    FETCH NEXT FROM no_truncate_db into @log_reuse_wait, @log_reuse_wait_desc, @dbname, @database_id, @recovery_model_desc

END

CLOSE no_truncate_db
DEALLOCATE no_truncate_db

重要

如果数据库在恢复过程中出现 9002 错误，则在解决此问题后，可使用 ALTER DATABASE database_name SET ONLINE 恢复数据库。

LOG_BACKUP log_reuse_wait

如果看到 LOG_BACKUP 或 log_reuse_wait，要考虑的最常见操作是查看数据库恢复模式并备份数据库的事务日志。

考虑数据库的恢复模式

事务日志可能无法使用 LOG_BACKUP log_reuse_wait 来截断，因为你从未对其进行备份。 在许多情况下，数据库使用 FULL 或 BULK_LOGGED 恢复模式，但你没有备份事务日志。 应仔细考虑每种数据库恢复模式：在处于 FULL 或 BULK LOGGED 恢复模式的所有数据库上执行定期事务日志备份，以最大程度地减少错误 9002 的发生。 有关详细信息，请参阅恢复模式。

备份日志

在 FULL 或 BULK_LOGGED 恢复模式下，如果最近尚未备份事务日志，则请立即进行备份以免发生日志截断。 必须备份事务日志，以允许释放日志记录并截断日志。 如果从未备份日志，则 必须创建两个日志备份 ，以允许 数据库引擎 将日志截断到上次的备份点。 截断日志可释放逻辑空间以供新的日志记录使用。 若要防止日志再次填满，请定期且更频繁地执行日志备份。 有关详细信息，请参阅恢复模式。

服务器实例上所有 SQL Server 备份和还原操作的完整历史记录存储在 msdb 系统数据库中。 若要查看数据库的完整备份历史记录，请使用以下示例脚本：

SELECT bs.database_name
, backuptype = CASE
    WHEN bs.type = 'D' and bs.is_copy_only = 0 THEN 'Full Database'
    WHEN bs.type = 'D' and bs.is_copy_only = 1 THEN 'Full Copy-Only Database'
    WHEN bs.type = 'I' THEN 'Differential database backup'
    WHEN bs.type = 'L' THEN 'Transaction Log'
    WHEN bs.type = 'F' THEN 'File or filegroup'
    WHEN bs.type = 'G' THEN 'Differential file'
    WHEN bs.type = 'P' THEN 'Partial'
    WHEN bs.type = 'Q' THEN 'Differential partial' END + ' Backup'
, bs.recovery_model
, BackupStartDate = bs.Backup_Start_Date
, BackupFinishDate = bs.Backup_Finish_Date
, LatestBackupLocation = bf.physical_device_name
, backup_size_mb = bs.backup_size/1024./1024.
, compressed_backup_size_mb = bs.compressed_backup_size/1024./1024.
, database_backup_lsn -- For tlog and differential backups, this is the checkpoint_lsn of the FULL backup it is based on.
, checkpoint_lsn
, begins_log_chain
FROM msdb.dbo.backupset AS bs
LEFT OUTER JOIN msdb.dbo.backupmediafamily AS bf ON bs.[media_set_id] = bf.[media_set_id]
WHERE recovery_model in ('FULL', 'BULK-LOGGED')
AND bs.backup_start_date > DATEADD(month, -2, sysdatetime()) --only look at last two months
ORDER BY bs.database_name asc, bs.Backup_Start_Date desc;

服务器实例上所有 SQL Server 备份和还原操作的完整历史记录存储在 msdb 系统数据库中。 有关备份历史记录的更多信息，请参阅备份历史记录和标头信息 (SQL Server)。

创建事务日志备份

如何备份日志的示例：

BACKUP LOG [dbname] TO DISK = 'some_volume:\some_folder\dbname_LOG.trn'

重要

如果数据库已损坏，请参阅结尾日志备份 (SQL Server)。

ACTIVE_TRANSACTION log_reuse_wait

对 ACTIVE_TRANSACTION 问题原因进行故障排除的步骤包括发现长时间运行的事务并着手解决（在某些情况下使用 KILL 命令来实现）。

发现长时间运行的事务

很长时间运行的事务会导致事务日志填满。 若要查看长时间运行的事务，请使用下列方法之一：

此动态管理视图返回有关数据库级事务的信息。 对于长时间运行的事务，最需要注意的列包括：第一条日志记录的时间 (database_transaction_begin_time)、事务的当前状态 (database_transaction_state)和事务日志中 BEGIN 记录的 日志序列号 (LSN)(database_transaction_begin_lsn)。

  • DBCC OPENTRAN。 通过此语句，你可以标识该事务所有者的用户 ID，因此可以隐性地跟踪该事务的源以得到更加有序的终止（将其提交而非回滚）。
终止一个事务

有时只是需要结束事务；此时可能需要使用 KILL 语句。 使用 KILL 语句时要格外小心，特别是当你不想结束的关键进程正在运行时。

检查点 log_reuse_wait

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

这是日志截断延迟的常见原因。 如果延迟，请考虑对数据库执行 CHECKPOINT 命令或检查日志 VLF。

USE dbname; CHECKPOINT
select * from sys.dm_db_log_info(db_id('dbname'))

AVAILABILITY_REPLICA log_reuse_wait

当 Always On 可用性组副本上的事务更改尚未在次要副本上强化时，无法截断主要副本事务日志。 这可能会导致日志增长，并且无论是为同步提交模式还是异步提交模式设置次要副本，都可能会发生此错误。 有关如何排查此类问题的信息，请参阅错误 9002。由于出现 AVAILABILITY_REPLICA 错误，数据库的事务日志已满。

复制、更改跟踪或 CDC

复制、更改跟踪和变更数据捕获 (CDC) 等功能依赖于事务日志，因此如果事务或更改未传递，则可以防止事务日志截断。

使用 DBCC OPENTRAN、 复制监视器或存储过程进行更改跟踪和 CDC，来调查和解决这些功能的任何问题。

有关 log_reuse_wait 因素的更多信息

有关详细信息，请参阅可能延迟日志截断的因素。

2. 解析完整磁盘卷

在某些情况下，托管事务日志文件的磁盘卷可能会填满。 可执行以下操作之一来解决因磁盘已满而导致日志已满的情况：

可用磁盘空间

你可以通过删除或移动其他文件的方法来释放包含数据库事务日志文件的磁盘驱动器上的磁盘空间。 释放磁盘空间后，恢复系统将自动扩大日志文件。

将日志文件移至其他磁盘

如果在当前包含日志文件的驱动器上无法释放足够的磁盘空间，请考虑将该文件移至空间充足的其他驱动器上。

重要

日志文件决不要放在压缩文件系统中。

有关如何更改日志文件位置的信息，请参阅移动数据库文件。

在其他磁盘上添加日志文件

使用 ALTER DATABASE <database_name> ADD LOG FILE，向具有足够空间的其他磁盘上的数据库中添加新日志文件。 应将单一数据库的多个日志文件视为解决空间问题的临时条件，而不是长期条件。 大多数数据库应只有一个事务日志文件。 继续调查事务日志已满并且无法截断的原因。 考虑仅添加临时附加事务日志文件作为高级故障排除步骤。

有关详细信息，请参阅 向数据库中添加数据文件或日志文件。

通过运行以下 T-SQL 脚本可以部分地自动执行这些步骤，以识别使用大量磁盘空间的日志文件并建议相应操作：

DECLARE @log_reached_disk_size BIT = 0

SELECT
    name LogName,
    physical_name,
    CONVERT(bigint, size)*8/1024 LogFile_Size_MB,
    volume_mount_point,
    available_bytes/1024/1024 Available_Disk_space_MB,
    (CONVERT(bigint, size)*8.0/1024)/(available_bytes/1024/1024 )*100 file_size_as_percentage_of_disk_space,
    db_name(mf.database_id) DbName
FROM sys.master_files mf CROSS APPLY sys.dm_os_volume_stats (mf.database_id, file_id)
WHERE mf.[type_desc] = 'LOG'
    AND (CONVERT(bigint, size)*8.0/1024)/(available_bytes/1024/1024 )*100 > 90 --log is 90% of disk drive
ORDER BY size DESC

if @@ROWCOUNT > 0
BEGIN

    set @log_reached_disk_size = 1

    -- Discover if any logs have are close to or completely filled disk volume they reside on.
    -- Either Add A New File To A New Drive, Or Shrink Existing File
    -- If Cannot Shrink, Go To Cannot Truncate Section

    DECLARE @db_name_filled_disk sysname, @log_name_filled_disk sysname, @go_beyond_size bigint

    DECLARE log_filled_disk CURSOR FOR
        SELECT
            db_name(mf.database_id),
            name
        FROM sys.master_files mf CROSS APPLY sys.dm_os_volume_stats (mf.database_id, file_id)
        WHERE mf.[type_desc] = 'LOG'
            AND (convert(bigint, size)*8.0/1024)/(available_bytes/1024/1024 )*100 > 90 --log is 90% of disk drive
        ORDER BY size desc

    OPEN log_filled_disk

    FETCH NEXT FROM log_filled_disk into @db_name_filled_disk , @log_name_filled_disk

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SELECT 'Transaction log for database "' + @db_name_filled_disk + '" has nearly or completely filled disk volume it resides on!' AS Finding
        SELECT 'Consider using one of the below commands to shrink the "' + @log_name_filled_disk +'" transaction log file size or add a new file to a NEW volume' AS Recommendation
        SELECT 'DBCC SHRINKFILE(''' + @log_name_filled_disk + ''')' AS Shrinkfile_Command
        SELECT 'ALTER DATABASE ' + @db_name_filled_disk + ' ADD LOG FILE ( NAME = N''' + @log_name_filled_disk + '_new'', FILENAME = N''NEW_VOLUME_AND_FOLDER_LOCATION\' + @log_name_filled_disk + '_NEW.LDF'', SIZE = 81920KB , FILEGROWTH = 65536KB )' AS AddNewFile
        SELECT 'If shrink does not reduce the file size, likely it is because it has not been truncated. Please review next section below. See https://learn.microsoft.com/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql' AS TruncateFirst
        SELECT 'Can you free some disk space on this volume? If so, do this to allow for the log to continue growing when needed.' AS FreeDiskSpace

         FETCH NEXT FROM log_filled_disk into @db_name_filled_disk , @log_name_filled_disk

    END

    CLOSE log_filled_disk
    DEALLOCATE log_filled_disk

END

3. 更改日志大小限制或启用自动增长

如果事务日志大小已设置为上限，或不允许自动增长功能，则可能会生成错误 9002。 在这种情况下，启用自动增长或手动增加日志大小可以帮助解决该问题。 使用此 T-SQL 命令查找此类日志文件，并按照提供的建议操作：

SELECT DB_NAME(database_id) DbName,
       name LogName,
       physical_name,
       type_desc ,
       CONVERT(bigint, SIZE)*8/1024 LogFile_Size_MB ,
       CONVERT(bigint,max_size)*8/1024 LogFile_MaxSize_MB ,
       (SIZE*8.0/1024)/(max_size*8.0/1024)*100 percent_full_of_max_size,
       CASE WHEN growth = 0 THEN 'AUTOGROW_DISABLED' ELSE 'Autogrow_Enabled' END as AutoGrow
FROM sys.master_files
WHERE file_id = 2
    AND (SIZE*8.0/1024)/(max_size*8.0/1024)*100 > 90
    AND max_size not in (-1, 268435456)
    OR growth = 0

if @@ROWCOUNT > 0
BEGIN
    DECLARE @db_name_max_size sysname, @log_name_max_size sysname, @configured_max_log_boundary bigint, @auto_grow int

    DECLARE reached_max_size CURSOR FOR
        SELECT db_name(database_id),
               name,
               CONVERT(bigint, SIZE)*8/1024,
               growth
        FROM sys.master_files
        WHERE file_id = 2
            AND ( (SIZE*8.0/1024)/(max_size*8.0/1024)*100 > 90
            AND max_size not in (-1, 268435456)
            OR growth = 0 )

    OPEN reached_max_size

    FETCH NEXT FROM reached_max_size into @db_name_max_size , @log_name_max_size, @configured_max_log_boundary, @auto_grow

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @auto_grow = 0
          BEGIN
            SELECT 'The database "' + @db_name_max_size+'" contains a log file "' + @log_name_max_size + '" whose autogrow has been DISABLED' as Finding
            SELECT 'Consider enabling autogrow or increasing file size via these ALTER DATABASE commands' as Recommendation
            SELECT 'ALTER DATABASE ' + @db_name_max_size + ' MODIFY FILE ( NAME = N''' + @log_name_max_size + ''', FILEGROWTH = 65536KB)' as AutoGrowth
          END
        ELSE
          BEGIN
            SELECT 'The database "' + @db_name_max_size+'" contains a log file "' + @log_name_max_size + '" whose max limit is set to ' + convert(varchar(24), @configured_max_log_boundary) + ' MB and this limit has been reached!' as Finding
            SELECT 'Consider using one of the below ALTER DATABASE commands to either change the log file size or add a new file' as Recommendation
          END

        SELECT 'ALTER DATABASE ' + @db_name_max_size + ' MODIFY FILE ( NAME = N''' + @log_name_max_size + ''', MAXSIZE = UNLIMITED)' as UnlimitedSize
        SELECT 'ALTER DATABASE ' + @db_name_max_size + ' MODIFY FILE ( NAME = N''' + @log_name_max_size + ''', MAXSIZE = something_larger_than_' + CONVERT(varchar(24), @configured_max_log_boundary) +'MB )' as IncreasedSize
        SELECT 'ALTER DATABASE ' + @db_name_max_size + ' ADD LOG FILE ( NAME = N''' + @log_name_max_size + '_new'', FILENAME = N''SOME_FOLDER_LOCATION\' + @log_name_max_size + '_NEW.LDF'', SIZE = 81920KB , FILEGROWTH = 65536KB )' as AddNewFile

        FETCH NEXT FROM reached_max_size into @db_name_max_size , @log_name_max_size, @configured_max_log_boundary, @auto_grow

    END

    CLOSE reached_max_size
    DEALLOCATE reached_max_size
END
ELSE
    SELECT 'Found no files that have reached max log file size' as Findings

增加日志文件大小或启用自动增长

如果日志磁盘上具有可用空间，则可以增加日志文件的大小。 日志文件的最大大小是每个日志文件 2 TB。

如果禁用自动增长，数据库处于联机状态，并且磁盘上有足够的可用空间，则考虑执行以下步骤：

注意

不管哪种情况，如果已达到当前大小限制，则应增加 MAXSIZE 值。

相关内容