Fail to cycle errorlog

sometime we observed that errorlog recycle is failed this happens when we monitor/read errorlog using our monitoring tool or monitoring application/scripts during this task if we want to recycle the errorlog it will fail.

 

and we get error that it is being used and could not able to recycle.

their are blogs where it shows it would be curruption but this is mostly not happens due to it. it is most likely due to that errorlog file is read by your monitoring script/tool.

SELECT *
FROM sys.dm_exec_requests a
OUTER APPLY sys.dm_exec_sql_text(a.sql_handle) b
WHERE session_id > 50
and session_id <> @@spid AND( text = ‘xp_readerrorlog’  OR  text = ‘sp_cycle_errorlog’)
ORDER BY start_time

this will provide that informaiton.

once you stop it by killing or stop the job and no output from above query run

 

DBCC ERRORLOG

or

sp_cycle_errorlog

 

other issue could be if you have cluster/AG and fail over testing generates the same number on Errorlog.1 (file) could cause it just validate that once as well.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2bbfa8ff-9ecb-4a14-9e7a-4a94c2ec1b6c/unable-to-cycle-error-log-due-to-lock-that-will-not-clear?forum=sqldatabaseengine

This entry was posted in Error Log, Others, What I learned today and tagged . Bookmark the permalink.

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.