On two of my fifteen SQL Server 2008 R2 instances, my weekly sp_cycle_errorlog job fails very often. One instance has 28 failures out of the last 36 weekly runs, with error:
Executed as user: [service account]. Unable to cycle error log file from 'P:\MSSQL10_50.PROD\MSSQL\Log\ERRORLOG' to 'P:\MSSQL10_50.PROD\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." [SQLSTATE 42000] (Error 17049) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528). The step failed.
The error is always from ERRORLOG to ERRORLOG.1
When this error happens:
ERRORLOG is unchanged ERRORLOG.1 becomes ERRORLOG.2 ERRORLOG.2 becomes ERRORLOG.3 etc...
ERRORLOG keeps getting bigger (until a successful cycle), and ERRORLOG.1 disappears. Depending on the failure pattern over a few months, I end up with holes, e.g.
ERRORLOG ERRORLOG.2 ERRORLOG.4 ERRORLOG.5 ERRORLOG.6
I'm using the SQL default of 6 archive files and my logs are on a 5 Gb mount point with 1.5 Gb free space. The largest of the 7 log files at the moment is ERRORLOG at 12 Mb.
A similar issue was discussed here, but the only resolution offered was to restart the SQL service and that it was because the log files were very large. In my case the ERRORLOG file is only 12 Mb, as opposed to the Gb they were talking about.
sp_cycle_errorlog uses DBCC ERRORLOG:
USE [master] GO /****** Object: StoredProcedure [sys].[sp_cycle_errorlog] Script date removed ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [sys].[sp_cycle_errorlog] --(date removed) as if (not (is_srvrolemember('sysadmin') = 1)) -- Make sure that it is the SA executing this. begin raiserror(15247,-1,-1) return(1) end dbcc errorlog return (0)
I don't know what DBCC ERRORLOG does.
Any advice or ideas are appreciated.