SQL SERVER – FIX: ERROR : Msg 3023, Level 16, State 2 – Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized

Errors are the best way to learn how SQL Server works and as DBA’s we are bound to see many of them from time to time. One of the primary functions of a DBA would include creating backups and most importantly trying to automate the same using jobs and maintenance plans.

Here is a typical scenario which a DBAs can encounter. One fine day they notice that some backup jobs are failing for no reason. Normal troubleshooting always starts with an error message. Recently, one of my blog readers sent an email to me which was worth a look.

I am getting below error. What is the cause and solution?

Msg 3023, Level 16, State 2, Line 1
Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. 
Reissue the statement after the current backup or file manipulation operation is completed.

I pinged him on twitter and asked more details. He informed that they have a job which runs and fails with the error described above. I asked him to get more details about the job and post back. I also asked him to check details from my good friend Balmukund’s blog – query to find what is running at the same time when job runs. He didn’t come back to me – that means his issue might be resolved.

But that left me curious to find the possible causes of the error Msg 3023, Level 16, State 2. Reading the message again, it looks like two parallel backups would cause error. So I ran two parallel backup command for a database which was little big in size (100GB). As soon as two full backups started, I could see that only one backup was making progress (session id 57) and another (session id 58) was waiting for first one to finish.

Which means the error is not raised and backup is waiting. But as soon as I cancelled the query (session 58), I got below message.

Another possible reason of the error is that if we perform shrink operation in parallel to backup operation. (Shrink is NOT something which I recommend, but people would never listen)

Here is the text

Msg 3140, Level 16, State 5, Line 1
Could not adjust the space allocation for file 'SQLAuthority'.
Msg 3023, Level 16, State 2, Line 1
Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. 
Reissue the statement after the current backup or file manipulation operation is completed.

Depending on who came first, here is the behavior. If a backup is started when either add or remove file operation is in progress, the backup will wait for a timeout period, then fail. If a backup is running and one of these operations is attempted, the operation fails immediately.

Solution: Find out the conflicting operation and retry your operation after stopping or finishing conflicting operation.

Learning using error messages is a great way to understand what happens inside SQL Server. Do let me know in the recent past, what have you learnt from error messages in your environments.

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

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:

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:


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:


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.


As expected, we should get an error.  (How can we take master offline J)
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 (http://blog.sqlauthority.com)

SQL SERVER – Msg 1206, Level 18, State 118 – The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction

The saga of working with error messages continues. Here is an error that was shared at one of the User group meetings by a member. While working via linked server to do data manipulation they reported this error on their servers:

Msg 1206, Level 18, State 118, Line 9
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.

Initially I thought it was due to misconfigured DTC between source and destination, but digging more into this error revealed something really interesting and again I learned something new. Hence this blog post was born.

Whenever I get any DTC errors while working with linked server, the very first test I perform is by doing the dummy distributed transaction to see whether DTC between two servers is working or not.

* FROM <linkedServer>.<DatabaseName>.dbo.TestTable

If above test is failing, then make sure you follow the KB article 2027550 and make setting as shown below:

Below are some errors which I have seen in this regards in the past:

  • OLE DB provider “SQLNCLI11″ for linked server “linkedservername” returned message “No transaction is active.”
  • The operation could not be performed because OLE DB provider “SQLNCLI11″ for linked server “linkedservername” was unable to begin a distributed transaction.

In the error message for which I was consulted, the DBA/Developer confirmed the above test was successful. Moreover, the setting were are per above screenshot too. So there was something else causing the DTC error which made me curious. I asked what type of code raised this sort of error.

Here was the piece of code which was failing as per the Developer. I have done simplification of the code for your reference.

INSERT INTO SQLLinkedServer.SQLAuthority.dbo.TestTable VALUES (1,'MyFirstName')
IF @@Trancount > 0

In above script, SQLLinkedServer is the name of linked server, SQLAuthority is the name of the database located on the linked server instance.

We captured profiler and found that the actual error was something else which was not shown due to the fact that we have used TRYCATCH block. If we remove the block, we can see exact error.

The actual error is masked due to the way TRY… CATCH works. As per this – This is more of a limitation with the ERROR functions since they can return only one error & hence the last one.

To reproduce the error, I have already inserted a record in table residing under database on linked server. That’s why we are seeing primary key violation error.

Though this is an interesting scenario to investigate, I generally try to understand why we are using Linked Server and if there are ways to mitigate the same. But that will be a different discussion for some other blog post. If you ever encountered this error, do let me know what ways in which you found a solution.

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

SQL SERVER – How to Catch Errors While Inserting Values in Table

Question: “I often get errors when I insert values into a table, I want to gracefully catch them, how do I do that.”

Answer: Very simple. Just use TRY… CATCH. Here is the simple example of TRY…CATCH I have blogged earlier when it was introduced.

Here is the example, I have build from the earlier blog post where user can catch the error details during inserting value in table.

First, we will create a sample table.


Now we will attempt to insert value in this table which will throw errors and the same error we will catch into the table.

INSERT INTO SampleTable (Col)
SELECT 'FourthRow'
SELECT 'FifthRow---------'
,ERROR_MESSAGE() AS ErrorMessage;

The second row of the above table will throw an error as the length of the row is larger than the column in which we are inserting values. It will throw an error and the same error will be caught via TRY…CATCH and it will be displayed in the SELECT statement. Here is the result set.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Fix Error 7202 Could not find server in sys.servers. Verify that the correct server name was specified

The other day I received following error when I tried to restore a database from one server to another server.

Msg 7202, Level 11, State 2, Line 1
Could not find server ‘MyLinkedServer’ in sys.servers. Verify that the correct server name was specified.

Well, this is quite a popular error one receive when they attempt to restore database containing references of the linked server. The solution is to create a link server and restore database. Here is the quick script which can fix your error.

EXEC sp_addlinkedserver @server='MyLinkedServer'
EXEC sp_addlinkedsrvlogin 'MyLinkedServer', 'false', NULL, 'MyUserName', 'MyPassword'

Well, that’s it. Sometimes the solution is much simpler, even though the original problem looks complicated.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Convert Old Syntax of RAISEERROR to THROW

I have been quite a few comments on my Facebook page and here is one of the questions which instantly caught my attention.

“We have a legacy application and it has been a long time since we are using SQL Server. Recently we have upgraded to the latest version of SQL Server and we are updating our code as well.

Here is the question for you, there are plenty of places we have been using old style RAISEERROR code and now we want to convert it to use THROW. Would you please suggest a sample example for the same.”

Very interesting question. THROW was introduced in SQL Server 2012 to handle the error gracefully and return the error message. Let us see quickly two examples of SQL Server 2012 and earlier version.

Earlier Version of SQL Server

DECLARE @ErrorMessage NVARCHAR(2000), @ErrorSeverity INT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY()
RAISERROR (@ErrorMessage, @ErrorSeverity, 1)

SQL Server 2012 and Latest Version


That’s it! We are done!

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Fix Error – Package ‘Microsoft SQL Management Studio Package’ failed to load in SQL Server Management Studio

I recently formatted my computer and reinstalled my machine and I end up on following error:

Package ‘Microsoft SQL Management Studio Package’ failed to load

I was able to fix the error quickly, but while I was searching online, I noticed quite a few of the people have similar error and they struggle to fix it. I have decided to list a few of the ways to fix the error here.

Method 1: Reinstall .NET Framework

Method 2: Start SSMS with Admin privileges

Method 3: Reinstall SQL client with either command prompt or with wizard

Method 4: Delete the registry HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools and try to load the server again.

Let me know if there is any method which works to fix this error and I have missed it.

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