SQL SERVER – Fix – Error 5058, Level 16, State 1 – Option cannot be set in database

Of late I have been writing about errors quite a bit because I seem to have been digging these from my email archives based on interaction with multiple DBA’s over the past 6-7 years. These are interesting conversations that have become blog posts for your reference. I feel these error messages give me an opportunity to understand SQL Server better.

One of these many interactions brought me to the mail from one of the DBA’s where he mentioned that one of his maintenance tasks were sending error of Msg 5058. At first look I was clueless to what this error is, so I turned myself to DMVs for some help. I executed the following command to start with:

SELECT *
FROM sys.messages
WHERE message_id = 5058 AND language_id = 1033

This returned me the error text of:

Option '%.*ls' cannot be set in database '%.*ls'.

Still not convinced why this can ever happen. I wanted to learn from this DBA to what the specific scenario was and why he was getting this error. So I emailed in anticipation to the response. After a couple of days, he did send me back the message and everything fell into place. The mail reads as:

Msg 5058, Level 16, State 1, Line 3
Option 'RECOVERY' cannot be set in database 'tempdb'.

Now, the error message made complete sense and it was super easy for me to give him an explanation to what needs to be done on his server. I quickly got onto a chat window to understand how the maintenance plans were made.

Pinal: Hi there

DBA: Hello Pinal

Pinal: I saw your mail and want to know how you set your maintenance plans and what is the process?

DBA: As per our company policy, we need to have our databases in the FULL recovery model.

Pinal: Ahha … Now I know the problem. I think you have scripted everything using a looping logic?

DBA: Let me check, yes I can see.

Pinal: Just go ahead and please exclude the TempDB database from that list of databases list.

DBA: Ok. Will do so. But why?

Pinal: That will solve your problem and I will write about the why in my next blog post later this week.

DBA: Wow, thanks it helped.

Explanation / Reasons

As per the DBA and his company policies, he had gone ahead and made all DB’s as FULL recovery mode. But by design we cannot change the recovery model of TempDB. It will always be SIMPLE and cannot be changed. To mimic this behavior, here is the command that I can execute:

ALTER DATABASE [tempdb] SET RECOVERY FULL WITH NO_WAIT
GO


And we will get the error message as mentioned before.

Msg 5058, Level 16, State 1, Line 3
Option 'RECOVERY' cannot be set in database 'tempdb'.

Even if the recovery model is set to BULK_LOGGED or even SIMPLE, the error message is the same. There is no change in the error message. For demo purposes, I even tried using SIMPLE recovery model using:

ALTER DATABASE [tempdb] SET RECOVERY SIMPLE WITH NO_WAIT

The error message was consistent and same. We are not allowed to change the recovery settings of TempDB.

Here is another example of 5058 error.

ALTER DATABASE MASTER SET OFFLINE

As expected, we should get an error. (How can we take master offline :-))

Msg 5058, Level 16, State 5, Line 1
Option 'OFFLINE' cannot be set in database 'master'.

Notice that the error number is same but the state is different here because we are changing different setting of database (not recovery model).

Have you ever encountered similar errors in your environment? Let me know, how you found the same?

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