0

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.

  • 1
    Do you have AV installed on your server? And if so, have you put an exclusion on the log files directory? I have seen this lock the errorlog in the past and prevent it from rolling over. – Nic Aug 1 '17 at 15:19
  • Sorry for the delayed response. AV is system endpoint protection, and there is no exclusion that I know of. However, it's only 2 of 15 servers that we have this issue with, so I doubt that's it, but I will investigate that possibility. Thanks. – Dave Goldsmith Aug 3 '17 at 15:53
  • I just checked System Center Endpoint Protection on both servers that aren't working and compared to servers that are working. All setting are the same, there are no exclusions for the log files, and there are no scheduled scans, only real-time protection. Is it possible that the file is locked because SQL Server is writing to the errorlog when the sp_cycle_errorlog is requested? Thanks again – Dave Goldsmith Aug 7 '17 at 20:50
  • I've not seen that happen (in 15+ years), so I would not expect that to be the case. The locking will be from some external process (or filter driver). The realtime scans could cause that locking behavior, you should really exclude the log folder (as well as the SQL data file, and log file folders) from those scans. – Nic Aug 7 '17 at 21:31
  • If the real-time scans were involved, why would the other 13 instances have never had this problem? – Dave Goldsmith Aug 9 '17 at 14:03
0

There's a process that's accessing the file when sp_cycle_errorlog is trying to run. Run Process Explorer to see what process has a handle on the file. I would bet it's a system backup or anti-virus software. Process Explorer will tell you what is accessing it, as long as you are using it when the problem occurs.

  • Sorry for delayed response. Unfortunately this job runs late at night and I haven't had a chance yet to monitor the processes while it's trying to run. Process explorer doesn't come on Windows Server 2008 R2 and I'll have to go through a lot of red tape to install it. – Dave Goldsmith Aug 3 '17 at 16:00
  • See comment on OP – Dave Goldsmith Aug 7 '17 at 20:50
  • @DaveGoldsmith, I don't believe SQL Server writing to it would cause this issue. Process Explorer needs to be run to see which process has a handle on the file when you attempt to cycle the error log. Alternatively, modify the job schedule to a time when the file isn't locked. – Tara Kizer Aug 7 '17 at 20:58
  • Sorry, I should have been more explicit, installing Process Explorer is not an option. Because the failures are only on these two instances (production with the most error log traffic) and based on the assumption that the issue is with SQL writing to the log at high speed, I set the job to retry 5 times at a one minute interval. I'll find out this weekend if it's able to cycle at all in that 5 minute interval. – Dave Goldsmith Aug 9 '17 at 14:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.