SQL SERVER – FIX – Error: One or more files do not match the primary file of the database

Writing about SQL Server for close to 8 years, almost every single day means I get a lot of questions from people on a daily basis. Though I try to answer as many as possible (via email, blog, twitter and Facebook), I get many interesting questions. There are a variety of questions ranging from installation, scalability, performance, TSQL, new feature and a variety of error messages. Recently I got an email which had below the question:

Hi Pinal,

I was given task to move the files for few databases and also to rename them. While doing this activity I did some mistake and few of my databases are not coming online. Here is the error I am getting for those databases.

2014-10-20 17:52:39.08 spid37s     Error: 5173, Severity: 16, State: 1.

2014-10-20 17:52:39.08 spid37s     One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.

2014-10-20 17:52:39.08 spid37s     Log file ‘E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\SQLAuth_log.ldf’ does not match the primary file.  It may be from a different database or the log may have been rebuilt previously.

Can you please help me how I can resolve this issue and what needs to be done to mitigate?

On further investigation of ERRORLOG it was found that we have “RECOVERY_PENDING” state for the database because recovery of database could not be complete. The cause of the error is all files which are getting used by database don’t belong to the same database. There is a safety mechanism in the database startup where it checks whether all files below to same database. This is so very important because we don’t want to override existing files inadvertently.

It is not possible to open and read the files to find out information about MDF files when it is attached. We can use DBCC CHECKPRIMARYFILE command to read the primary file header to know the information stored. Here is the quick demo of various parameters and the output. I have found this quite powerful and thought was worth a share.

First, let’s create the database with two data files and one log file.

CREATE DATABASE [SQLAuthority]
ON PRIMARY
(NAME = N'SQLAuthority_MDF', FILENAME = N'C:\Temp\SQLAuthority_MDF.mdf'),
(
NAME = N'SQLAuthority_NDF', FILENAME = N'C:\Temp\SQLAuthority_NDF.ndf')
LOG ON
(NAME = N'SQLAuthority_log', FILENAME = N'C:\Temp\SQLAuthority_log.ldf')
GO

Let us detach the database using the following command:

sp_detach_db 'SQLAuthority'

Now let’s use DBCC CHECKPRIMARYFILE to read file header and get details.

We would see “1” and output only for first file because that’s the primary data file. Let’s run the same command with other parameters and check the output.

As we can see that by using the command we can read the header of the file and get various properties of primary files.

  1. Option 1: File IDs, filegroup id, logical name, physical name for other files belonging to same database.
  2. Option 2: Which database this file belongs to, what was the database version and collation.
  3. Option 3 is subset of option 1

This doesn’t mean that we can use two different files of same database taken from different servers and attach them. Sometime this command is useful when you don’t know the logical file names of the files which you have got and want to use the file (when attach is not working due to corruption).

Hope this was an interesting option and you were able to learn something from it as I did when first using the same.

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

About these ads

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:

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 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.

BEGIN DISTRIBUTED TRANSACTION
SELECT
* FROM <linkedServer>.<DatabaseName>.dbo.TestTable
ROLLBACK TRANSACTION

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.

SET XACT_ABORT ON
GO
BEGIN DISTRIBUTED TRANSACTION
BEGIN
TRY
INSERT INTO SQLLinkedServer.SQLAuthority.dbo.TestTable VALUES (1,'MyFirstName')
END TRY
BEGIN CATCH
IF @@Trancount > 0
ROLLBACK TRAN
END
CATCH
GO
SET XACT_ABORT OFF

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.

CREATE TABLE SampleTable (ID INT IDENTITY(1,1), Col VARCHAR(10))
GO

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

BEGIN TRY
INSERT INTO SampleTable (Col)
SELECT 'FourthRow'
UNION ALL
SELECT 'FifthRow---------'
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
GO

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

BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(2000), @ErrorSeverity INT
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY()
RAISERROR (@ErrorMessage, @ErrorSeverity, 1)
END CATCH

SQL Server 2012 and Latest Version

BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
THROW
END CATCH

That’s it! We are done!

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