SQL SERVER – Unable to Recycle ERRORLOG Using sp_cycle_errorlog

SQL
No Comments

There are many DBAs who create jobs in SQL Server to recycle ERRORLOG files every midnight. They also increase the files from 6 to a bigger number so that they have data for more than 6 days.

SQL SERVER - Unable to Recycle ERRORLOG Using sp_cycle_errorlog error-log-01-800x254

Here is the blog where I wrote about sp_cycle_errorlog. SQL SERVER – Recycle Error Log – Create New Log file without a Server Restart

You can read comments on above blog and see various usage scenarios.

One of the blog reader quoted my blog and said that I am unable to recycle ERRORLOG file using sp_cycle_errorlog command. I asked to share the ERRORLOG file content while running the command. Here is what we see in ERRORLOG.

2016-09-09 08:24:37.45 spid70 Attempting to cycle error log. This is an informational message only; no user action is required.
2016-09-09 08:24:37.46 spid70 Error: 17049, Severity: 16, State: 1.
2016-09-09 08:24:37.46 spid70 Unable to cycle error log file from ‘E:\MSSQL10_50.INSTANCE1\MSSQL\Log\ERRORLOG.5’ to ‘E:\MSSQL10_50.INSTANCE1\MSSQL\Log\ERRORLOG.6’ due to OS error ‘1392(The file or directory is corrupted and unreadable.)’. 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.”

I asked to open file in SSMS and he informed that he is getting error.

.Net SqlClient Data Provider Unicode file expected.

All of these point to corruption to the file in such a way that SQL is not able to read them correctly. Strangely, they were able to open via notepad.

SOLUTION

I have asked them to make sure that there is no corruption on the E drive. Since we were able to read the files via notepad, I told them to move “bad” file to some other location and then try.

We moved all ERRORLOG.* files to a different folder and after that recycle worked well.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – FIX Error: Maintenance plan scheduled but job is not running as per schedule
Next Post
SQL SERVER – Unable to Bring SQL Online – DoSQLDataRootApplyACL : Failed to Create Directory Tree at SQLDataRoot

Related Posts

Leave a Reply

Menu