As a SQL Server DBA, you may have encountered various error messages while working with the database. These error messages provide valuable insights into the inner workings of SQL Server and can help you troubleshoot and resolve issues. In this blog post, we will explore one such error message – Msg 5058.
Msg 5058 is an error message that indicates that a specific option cannot be set in a particular database. Let’s take a closer look at an example scenario to understand this error message better.
Recently, I received an email from a fellow DBA who was facing the Msg 5058 error in one of his maintenance tasks. Initially, I was unfamiliar with this error and decided to investigate further. I used the following command to retrieve the error text:
SELECT * FROM sys.messages WHERE message_id = 5058 AND language_id = 1033This query returned the error text: “Option ‘%. *ls’ cannot be set in database ‘%.*ls’.” Although this text provided some information, I still needed more context to understand the specific scenario causing this error.
After a few days, the DBA responded to my email and shared the error message he encountered: “Msg 5058, Level 16, State 1, Line 3 Option ‘RECOVERY’ cannot be set in database ‘tempdb’.” With this information, I was able to provide him with a solution.
The root cause of the error in this case was that the DBA had set all databases to the FULL recovery model, including the TempDB database. However, the recovery model of TempDB cannot be changed and is always set to SIMPLE. To demonstrate this behavior, I executed the following command:
ALTER DATABASE [tempdb] SET RECOVERY FULL WITH NO_WAITExecuting this command resulted in the same error message: “Msg 5058, Level 16, State 1, Line 3 Option ‘RECOVERY’ cannot be set in database ‘tempdb’.” Even if we try to set the recovery model to BULK_LOGGED or SIMPLE, the error message remains the same.
Another example of the Msg 5058 error is when we try to take the master database offline:
ALTER DATABASE MASTER SET OFFLINEExecuting this command also results in the same error message: “Msg 5058, Level 16, State 5, Line 1 Option ‘OFFLINE’ cannot be set in database ‘master’.” Here, the error number is the same, but the state is different because we are changing a different setting of the database.
Encountering errors like Msg 5058 is not uncommon in SQL Server environments. It is important to understand the specific error message and its implications to effectively troubleshoot and resolve the issue.
Have you ever encountered similar errors in your SQL Server environment? Let me know in the comments below how you dealt with them.
Thank you for reading this blog post. Stay tuned for more articles on SQL Server error messages and troubleshooting tips.