As a SQL Server administrator, it is crucial to monitor and manage the error logs to ensure the smooth operation of your database. Error logs provide valuable information about any issues or errors that occur within SQL Server. In this article, we will discuss a common problem related to error log cycling and explore a solution to resolve it.
The Problem
Recently, one of my clients encountered an issue with their node production clustered server. They noticed that the nightly error log had stopped cycling, which raised concerns about potential errors going unnoticed. When they attempted to run the sp_cycle_errorlog stored procedure, they received the following error message:
Msg 17049, Level 16, State 1, Procedure sp_cycle_errorlog, Line 9 Unable to cycle error log file from 'G:\SQL_DATAMNT\MSSQL13.MSSQLSERVER\MSSQL\Log\ERRORLOG.10' to 'G:\SQL_DATAMNT\MSSQL13.MSSQLSERVER\MSSQL\Log\ERRORLOG.11' due to OS error '1392(failed to retrieve text for this error. Reason: 15105)'. A process outside of SQL Server may be preventing SQL Server from reading the files. As a result, error log entries may be lost, and it may not be possible to view some SQL Server error logs. Make sure no other processes have locked the file with write-only access.
This error message indicates that the file or directory is corrupted and unreadable, as denoted by OS Error 1392. It is essential to address this issue promptly to prevent any potential loss of error log entries.
The Solution
To resolve this problem, we need to take the following steps:
- Change the
-eparameter in the SQL Server Configuration Manager to use a new path for the error log files. - Fail over the SQL cluster instance to another node to ensure the SQL server can come online.
- Verify that the new error log is generated at the new location.
- Run the
sp_cycle_errorlogstored procedure again to recycle the error log.
By following these steps, we can successfully address the error log cycling issue and ensure that the error logs are properly managed.
Conclusion
It is crucial to pay attention to error log management in SQL Server. The example we discussed highlights the importance of promptly addressing any issues related to error log cycling. Failure to do so can result in the loss of valuable error log entries, which may hinder troubleshooting efforts in the future. Remember, if this issue occurs with database-related MDF or LDF files, the consequences could be even more severe. Always be proactive in monitoring and managing your SQL Server error logs to maintain a healthy and efficient database environment.