External process holding the log and SQL give 'Unable to cycle error log'...
-
Hello
We have a servers with the following issue
Msg 17049, Level 16, State 1, Procedure sp_cycle_errorlog, Line 9
Unable to cycle error log file from 'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG.98' to 'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG.99' due to OS error '5(Access is denied.)'. 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.
Did some researching with sysinternal tools and found that a monitoring tool ( NXlog.exe) is holding some of the log files. (see the image)
I can manually delete some of the logs and then re-run the job and it succeeds but the problem is that we have this issue on multiple servers and wasting time to clear logs each month...
I have contacted the NXlog guys but they did not return anything so far (still waiting some of them to get back from OOH)
was wondering if we can fix this issue somehow from SQL side as we have CMS and can easily configure multiple instances as creating a Windows task to copy the error log files and pointing NXLog to read from the copy dir will mean doing that on 300 servers 1 by 1...dealing with downtime and waiting for a GO from multiple app teams etc
If anyone had similar issues please share your resolutions here.
- This topic was modified 2 years, 11 months ago by Angel_V.
-
Unfortunately if something is holding the file open there is nothing that can really be done except terminating that process, so you would need to kill the nxlog reader in order to cycle the error log.
-
The first to the check for is <insert drum roll here> are you using any monitoring software?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Ant-Green wrote:
Unfortunately if something is holding the file open there is nothing that can really be done except terminating that process, so you would need to kill the nxlog reader in order to cycle the error log.
sad to hear that
Jeff Moden wrote:The first to the check for is <insert drum roll here> are you using any monitoring software?
Yes i use monitoring and nxlog is part of the setup
- Angel_V wrote:Jeff Moden wrote:
The first to the check for is <insert drum roll here> are you using any monitoring software?
Yes i use monitoring and nxlog is part of the setup
So turn the monitoring off and see if that's the cause.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This was removed by the editor as SPAM
- Jeff Moden wrote:Angel_V wrote:Jeff Moden wrote:
The first to the check for is <insert drum roll here> are you using any monitoring software?
Yes i use monitoring and nxlog is part of the setup
So turn the monitoring off and see if that's the cause.
We know that the monitoring is the issue....NXLog is constantly reading the SQL logs so this is why they cannot be recycled. ROOT cause is clear in this case but what is not clear is how to go around it...we cannot stop monitoring or SQL as they are both used and part of the corporation`s contract.
At the end i managed to solve the issue with a simple job which i re-created on all servers using the CMS.
The job acts as follows:
STEP1: Stop-Service -Name "nxlog" -Force (powershell)
STEP2: EXEC master..sp_cycle_errorlog;
STEP3: Start-Service -Name "nxlog"
Turns out there is no problem for NXlog to be killed for couple seconds which is exactly what SQL recycle log needs
So if anyone have the exact same problem you can get around with this. It runs for 10 sec and there is no impact for SQL or NXlog services.
-
This was removed by the editor as SPAM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply