SQL SERVER – FIX: ERROR : Msg 3136, Level 16, State 1 – This differential backup cannot be restored because the database has not been restored to the correct earlier state

During my recent visit to customer site for a session on backups, they asked me to find the cause of the error while restoring a differential backup. Though this seemed to be completely an admin related topic and I had gone for some other session, I took the challenge head-on. These are wonderful ways to explore and learn SQL Server better. The error they showed me was:

Msg 3136, Level 16, State 1, Line 39
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 39
RESTORE DATABASE is terminating abnormally.

In this blog post I will try to explain about the error in detail. In the same context, long time back, I did write a blog post on: SQL SERVER – Backup Timeline and Understanding of Database Restore Process in Full Recovery Model

Over there, I have explained details and co-relation of the various backup type i.e. Full, Differential and Transaction Log backups. I will refrain from rehashing them here again.

Recently, one of my friends asked about if we have differential backup, how we can find the full backup on which differential backup can be restored. If we go back to basics, the differential backup has all the changes in the database made since last full backup was taken.

Let us understand this concept using an example:

USE SQLAuthority
BACKUP DATABASE SQLAuthority TO DISK = 'E:\temp\F1.bak'
BACKUP DATABASE SQLAuthority TO DISK = 'E:\temp\F2.bak'

Once the script has been run we have below backups.

Looking at the backup chain, it is clear that D3 is valid for F2. On the other hand D1 and D2 are valid and restorable on top of F1. Let us drop the database and try to restore D3 on top of F1.


Here is the output.

Processed 296 pages for database 'SQLAuthority', file 'SQLAuthority' on file 1.
Processed 6 pages for database 'SQLAuthority', file 'SQLAuthority_log' on file 1.
RESTORE DATABASE successfully processed 302 pages in 0.213 seconds (11.076 MB/sec).
Msg 3136, Level 16, State 1, Line 43
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 43
RESTORE DATABASE is terminating abnormally. 

This means that first restore was successful and next one has error which means that this is not a valid differential backup to be restored. How would we figure out the correct sequence of restore? Well, there are multiple ways.

1. Have a look at SQL Server ErrorLog where we have successful backup messages. Here is what we saw in ERRORLOG while running above backups.

As highlighted above, we can find the full back up LSN from the message of differential backup.

2. Have a look at Standard Reports to find previous backup events.

SQL SERVER – SSMS: Backup and Restore Events Report

3. Run below query on the server from where backup was taken.

SQL SERVER – Get Database Backup History for a Single Database

Hope fully this blog demystifies and tells you usefulness of the messages in ERRORLOG and logging capability of SQL Server. Do let me know if you have ever encountered these errors in your environments.

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:

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 – Performance Counter Missing: How to Get Them Back?

Of the thousands of mails I receive every day about SQL Server problems, I was recently pinged by a friend who reported a weird problem. He started with a simple question. He said that he wants to monitor SQL Server Performance counters for complete day to send a report back to his manager.  That was a simple one and I asked to capture performance counter data of SQL Server using performance monitor tool (PerfMon.exe). I thought the solution was done and was about to close the chat window that I was questioned for the second time. This was tough one I thought – “I am not seeing any performance counter for my instance”.

I asked him to send a screenshot and instance details. Here is how his perfmon counters screen looks like. (Start > Run > Perfmon.exe) and then Right Click “Add Counters…”

Since we are dealing with a default instance of SQL Server, we should see “SQL Server:Access Methods” as the first counters (they are alphabetical). This got me curious and I wanted to really understand why this could ever happen. I politely asked my friend if he was ready to do some sort of screen sharing at a later date.

I looked at the SQL Server ERRORLOG file first and there was nothing interesting under that. I asked to query sys.dm_os_performance_counters to check if we have counter values there. Here is the screenshot.

We can see that counters are available in SQL Server Engine but not shown in performance monitor tool.

Asked to look into below key:


We were not seeing many registry keys as compared to my system.

Knowing something has gone wrong terribly, the only option left at this point was to reload the counters. Here are the commands.

To unload counter

Default Instance: unlodctr MSSQLSERVER

Named Instance: unlodctr MSSQL$<InstanceName>

To load the counter we can look at the same key and look at the value of “PerfIniFile” which is “perf-MSSQLSERVERsqlctr. ini” in the above screenshot. The file is located under BINN folder. For my machine, it is “E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn”

To load counter

Default Instance:

lodctr “E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\perf-MSSQLSERVERsqlctr.ini”


For named instance, we need to check the file and path and run below (my machine has named instance of SQL Server 2014 called SQL2014)

lodctr “E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Binn\perf-MSSQL$SQL2014sqlctr.ini”

Once that is done, we should be able to see the counters (shown below)

A big sigh of relief as this was a great learning and sharing time for me and I was able to help my friend. I am sure this was helpful to you too, if you ever encounter this situation. Do let me know.

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 – Table Space Allocation Details using DMV

Understanding SQL Server from inside-out is always something I have loved to learn from time to time. Recently, I was asking my good friend Balmukund on how are pages allocated inside SQL Server and what are the various ways to find the same. He was quick to bounce and say have you ever checked DBCC IND and EXTENTINFO commands. These undocumented commands have been there for a while and I have seen usage of these commands at a number of sessions.

In the evening, I was pleasantly surprised to see Balmukund call me. This doesn’t happen quite often and I was eager to know what the context was. He had called me to talk about a DMV (dm_db_database_page_allocations) and if I had seen. Many a times I have seen as techies, it is difficult to sleep if there is a problem at hand. And my friend was no different, he has given me a learning path to look at. I started to dig into this DMV and was pleasantly surprised to see various details.


SQL Server 2012 introduces a new dynamic management function that replaces the old and undocumented DBCC IND and DBCC EXTENTINFO commands. DBCC IND and DBCC EXTENTINFO were widely used by the Microsoft Support team when working on customer issues on database storage or space issue. Examples are database shrink, excessive space usage by a table or index, know the linkages of pages and so on.

DBCC IND and DBCC EXTENTINFO had several limitations. Since it was a DBCC command, in order to perform filtering and advanced grouping, you had to first import the entire output into a temporary table and then perform processing. The use of the new dynamic management function solves all those limitations posed. To recollect, the name of the new dynamic management function is: Sys.dm_db_database_page_allocations()

There are a number of arguments that need to be passed as part of this function. These include:

  1. databaseId – database id [not null]
  2. tableId – object id or NULL
  3. indexId – index id or NULL
  4. partitionId – partition id or NULL

The output of this dynamic management function includes all the pages and extents allocated for table, index or partition. If we supply NULL for the indexId and partitionId arguments, information about all pages and extents allocated to the table is returned.

The last argument “mode” determines if extended information is returned in the output. The output of this function includes information from the page header. Obtaining that information involves extra processing and consumption of resources. In order to keep the execution time within reasonable limits, the default for this argument is LIMITED. If you specifically need information for any of those columns, you need to use the DETAILED option for this argument.

The sample code to use this new dynamic management function is provided, as follows:

FROM sys.dm_db_database_page_allocations(

Sample part output from this command looks like below. Here since we have made the Index as NULL, we get records for both the indexes defined on this table.

FROM sys.dm_db_database_page_allocations(

Here is a detailed dump and partial additional fields like the Page_Type and page_type_desc which we can see. The sys.dm_db_database_page_allocations DMV also shows unallocated pages, system pages like PFS, GAM, IAM, and SGAM pages, as well as pages allocated to a table or index. DBCC IND only shows pages allocated to the table, so the results will not be one-to-one many-a-times if we were to compare. Also, this DMV returns information about the extent and can include pages still not allocated but the extent is allocated to this object. The best way is to check if the additional entries are there for is_allocated = 0 column.

Let me know if you have ever used this DMV before.

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

SQL SERVER – Using Bitwise And (&) Instead of a Junction Table – Notes from the Field #053

[Note from Pinal]: This is a 53rdth episode of Notes from the Field series. Everyday I get 100s of emails and most of the emails have a similar request. Everyone wants to get maximum performance, but they want to make the least amount of changes in their code. Well, though both of them are contradictory requests, it is possible in most of the cases if you know the technology inside like Linchpin People do. Here in this blog post, my close friend Stuart Ainsworth explains a cool trick, which I just learned today after so many years of experience. Wow, Stuart – thanks for this amazing note from the fields – I learned something new and there will be so many who will enjoy this post.

In this episode of the Notes from the Field series database expert Stuart Ainsworth explains Using Bitwise And (&) Instead of a Junction Table.


Bitwise operations in SQL Server are not often used, but like many of the tools available to SQL Server developers, bitwise operations can provide some interesting alternatives when you’re faced with specific challenges. One of my clients has a large database that relies heavily on many-to-many relationships to identify matching interests on multiple characteristics. As a simple example, let’s assume that I’m talking about fruit baskets.

In the simplest version of a fruit basket, you’d have two database objects: the basket, and the assortment of fruit.  Baskets can use different combinations of fruit, and samples of fruit may appear in more than one basket, like so:

Basket 1: Apples
Basket 2: Apples, Bananas
Basket 3: Grapes, Apples
Basket 4: Strawberries, Bananas

The traditional method of modeling this relationship would be to use a junction table, as illustrated below.


However, my client  had 500,000 baskets, and roughly 50 different fruits to choose from. Assuming that every basket had at least 10 different fruits, the junction table would have at least 5,000,000 rows of data. Even though the junction table was well indexed and strongly typed, my client’s design was suffering from slow read times.  The client needed an alternative. Enter the bitwise AND (&).

Setting Up a Demo

Let’s set up a demo that illustrates both the junction table method and the bitwise AND alternative.  First, you’ll create the following three tables and populate them (using table valued constructors):

  1. Baskets, which includes a column for use with the Bitwise AND
  2. FruitID, which is set up for use with the Bitwise AND
  3. FruitBaskets, which is a junction table

Note that primary and foreign key references are not included for the simplicity of the demo. You’ll also be adding an extra column to the Baskets table to use for the Bitwise join. Finally, note that the ID column of the Fruit table mirrors the decimal values of the binary bit positions (e.g., 1, 2, 4, 8, 16, 32, 64, 128).

BasketID INT
, BasketName VARCHAR(100)
FruitBitHash BIGINT
, FruitName VARCHAR(20)
BasketID INT
( FruitID, FruitName)
VALUES  ( 1, 'Apples'),
2, 'Bananas'),
4, 'Grapes'),
8, 'Strawberries')
INSERT  INTO dbo.Baskets
( BasketID, BasketName, FruitBitHash)
VALUES  ( 1, 'Apples', 1),
2, 'Apples, Bananas', 1 + 2),
3, 'Grapes, Apples', 1 + 4),
4, 'Strawberries, Bananas', 8 + 2)
INSERT  INTO dbo.FruitBaskets
( BasketID, FruitID)
VALUES  ( 1, 1),
2, 1 ),
2, 2 ),
3, 1 ),
3, 4 ),
4, 8 ),
4, 2 )

Now that you’ve got your tables set up, let’s run a couple of queries. First, you’ll use a junction table (the traditional, normalized model), and then you’ll use the Bitwise AND (&).  In both cases, youy’re looking for baskets that contain apples:

/*Select the fruitbaskets containing Apples using the junction table*/
SELECT BasketID, BasketName
FROM dbo.Baskets b
FROM dbo.FruitBaskets fb
JOIN dbo.Fruit f ON fb.FruitID = f.FruitID
WHERE b.BasketID = fb.BasketID
AND f.FruitName = 'Apples')
/*Select the fruitbaskets containing Apples using the bithash*/
SELECT BasketID, BasketName
FROM dbo.Baskets b
FROM dbo.Fruit f
WHERE b.FruitBitHash &amp; f.FruitID <>0
AND f.FruitName = 'Apples')

If you run this demo, you’ll see that you get the exact same results from the two queries. However, the first query would need to read data from 3 tables, and the second query only needs 2. If the junction table is very large, the traditional method can be significantly slower than the second method.

But how does it work? An excellent explanation can be found here, but the short answer is that when you’re using the Bitwise AND (&) to compare two different integers, any value other than 0 that is returned from that comparison means that those integers share a common base. The magic happens with this line of code:

WHERE b.FruitBitHash & f.FruitID <>0

So, why don’t we do this all the time?

There’s an old expression, “If all you have is a hammer, then everything looks like a nail.” Different tools are best suited for different problems. The limitations of using the Bitwise method to remove a junction table include:

  1. Violation of relational integrity: The surrogate IDs in the lookup table (e.g., the Fruit table) have to have a specific order and meaning. If you make a mistake when setting up the key values, you can get wrong answers.
  2. A limited number of bitwise values can be stored in a bigint: In SQL Server, a bigint is 8 bytes, which means that there are 64 bits. When using a single bithash column, you can only have one value per bit. (Note that you can work around this by using multiple columns, but that gets complicated.)

The benefit of the Bitwise AND method is reduced disk I\O because it eliminates a large junction table. In this case, you did notice increased CPU usage using the Bitwise method, but the increase in performance was significant. However, on faster hardware, a junction table would probably have worked as well and still maintained relational integrity. For now, Bitwise AND is a useful tool for a very specific type of problem.

If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.

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