As a database administrator (DBA), one of the common tasks is to manage the error logs in SQL Server. Error logs are essential for troubleshooting and identifying issues within the SQL Server environment. By default, SQL Server keeps a maximum of six error log files, and it is recommended to recycle them regularly to prevent them from growing too large.
In a recent blog post, I discussed the sp_cycle_errorlog command, which is commonly used to recycle error log files in SQL Server. This command allows DBAs to create a new log file without restarting the server. However, there can be instances where the recycling process fails, and it is important to understand the possible reasons behind it.
One of my blog readers encountered an issue where they were unable to recycle the error log file using the sp_cycle_errorlog command. Upon further investigation, we found the following error message in the SQL Server error log:
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.
This error message indicates that the error log file is corrupted and unreadable by SQL Server. It is important to note that the file could still be opened using a text editor like Notepad, but SQL Server was unable to read it correctly.
To resolve this issue, we recommended the following steps:
- Check for corruption on the drive where the error log files are stored. In this case, the error log files were located on the E drive, so we asked the reader to ensure there was no corruption on that drive.
- Since the files could be opened using Notepad, we advised moving the “bad” file to a different location. By moving the corrupted file, we eliminated any potential issues that were preventing SQL Server from reading it correctly.
After following these steps, the reader was able to successfully recycle the error log files without any further issues.
It is important for DBAs to regularly monitor and manage the error log files in SQL Server. By recycling them, you can ensure that the logs remain manageable and easily accessible for troubleshooting purposes. If you encounter any issues during the recycling process, it is crucial to investigate the error messages and take appropriate actions to resolve them.
Thank you for reading this blog post. If you have any questions or would like to share your experiences with error log recycling in SQL Server, please leave a comment below.