SQL Server错误日志被占用无法轮转案例

遇到一台SQL Server错误日志无法轮转的案例, 如下所示,作业/SQL窗口执行下面脚本:

USE msdb;
GO
EXEC sp_cycle_errorlog;

就会遇到下面错误

Msg 17049, Level 16, State 1, Procedure sp_cycle_errorlog, Line 11
Unable to cycle error log file from 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\ERRORLOG' to 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\ERRORLOG.1' due to OS error '32(The process cannot access the file because it is being used by another process.)'. A process outside of SQL Server may be preventing SQL Server from reading the files. As a result, errorlog entries may be lost and it may not be possible to view some SQL Server errorlogs. Make sure no other processes have locked the file with write-only access."
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

从错误提示可以看出, 这个错误日志被占用, 只需找到是哪一个进程占用文件即可. 如下所示:

G:\tmp\Handle>handle.exe C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\ERRORLOG

Nthandle v5.0 - Handle viewer
Copyright (C) 1997-2022 Mark Russinovich
Sysinternals - www.sysinternals.com

sqlservr.exe       pid: 4332   type: File          1678: C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\ERRORLOG
opcle.exe          pid: 7228   type: File            90: C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\ERRORLOG

G:\tmp\Handle>

进程opcle.exe是一个监控日志的软件, 后面跟系统组同事沟通发现,是因为忘记勾选"读取后关闭日志文件"这个选项,导致这个进程一直占用SQL Server错误日志文件导致.勾选这个选项后,问题解决.