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)

About these ads

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)

SQLAuthority News – Get My Two Books FREE at #SQLPASS

Every year when I visit SQLPASS, I do book signing where I give away my books for free. This year there are two opportunities when I am going to give away my books for free.

Though the books are in great numbers they always run away early. I strongly suggest that you arrive early and secure your copy. I will be extremely delighted to shake your hands and talk SQL.

Book Signing will be at the booth of Embarcadero and here are the timings for the same.

Book: SQL Wait Stats - Thursday, November  6th from 1 PM to 2PM.

Book Secret Tool Box of Successful Bloggers – Friday, November 7th from 11 AM to 12 PM.

Do not miss this opportunity.

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

SQL SERVER – Unable to Restore From Backup After Detach Operation

While doing community work I travel a lot, speak at various conferences and get a chance to meet many new faces. The best part is that I get a chance to hear a variety of issues which people face while using SQL Server. This blog post is an outcome of one such interaction with a DBA from one of the organizations I had to meet.

After the conference a young guy came to me and said – “I found a bug in SQL Server Restore”. I was amazed with his confidence and asked him to tell more before concluding. He said that to restore a database from backup, you need to have same database created before restore. I told that there is something which is not right with the test which he is performing because that doesn’t sound correct. I gave him my email address and asked to contact me to find more. I was eagerly waiting for his mail as this was on top of my mind and I was restless for two days. Finally the mail landed-

He sent an email repro steps.

  1. Create new database.
  2. Take a backup of the database.
  3. Detach the database.
  4. Restore from backup taken in step 2. This step would fail.

I followed the same steps

sp_detach_db 'SQLAuth'

As soon as I run the last command of restore, I get below error

Msg 3142, Level 16, State 1, Line 7
File "SQLAuth" cannot be restored over the existing "E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\SQLAuth.mdf". 
Reissue the RESTORE statement using WITH REPLACE to overwrite pre-existing files, or WITH MOVE to identify an alternate location.
Msg 3142, Level 16, State 1, Line 7
File "SQLAuth_log" cannot be restored over the existing "E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\SQLAuth_log.ldf". 
Reissue the RESTORE statement using WITH REPLACE to overwrite pre-existing files, or WITH MOVE to identify an alternate location.
Msg 3119, Level 16, State 1, Line 7
Problems were identified while planning for the RESTORE statement. Previous messages provide details.

Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.

Error Message is very clear about the cause of restore failure. Since we detached the database, the mdf and ldf files are still available at the location when the database was created. It’s good that SQL is not over writing the files by itself unless we specify explicitly.

If you want to over write the files then we can use the “WITH REPLACE” clause to the command as shown below.


If we don’t detach the database and perform restore on top of existing database like below


Then we will get a slightly different message as shown below:

Msg 3159, Level 16, State 1, Line 5
The tail of the log for the database "SQLAuth" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.

Again, this is a safety mechanism where a user has to confirm their actions. Recall the situation when you have an existing file in windows and you paste same file at same location – you always get warning. SQL Server is no different and I was pleasantly relived with the fact that this was not a bug inside SQL Server. I am glad the DBA did send me this information because it made me revalidate and play around with backups with SQL Server.

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

SQL SERVER – PowerShell way of Restarting SQL Server Service

I learn a lot when I go for conferences all around the world. There are styles, content and cultural differences that I get a chance to learn and enjoy as part of my trips. Most of these trips I make are geared towards at least one learning experience I can get end of the day. With these travels, I also attend and speak at local user groups to get a flavour of what is the neat and coolest way to work with SQL Server. Recently at our local user group (SQL Server Bangalore UG) UG meeting, I was vividly stumped by a question from one of the speakers. Here he was talking about various ways to shut down or start SQL Server.

This is was an interesting quiz and I was pleasantly surprised by the most common methods and wanted to participate in this quiz. The answers were flowing all over and here are some that I would like to share which were part of audience interactions:

  1. Object Explorer in SSMS (SQL server Management Studio)
  2. Net Start Command.
  3. SQL Server Configuration Manager.
  4. Windows Services.
  5. Using the Sqlservr.exe executable

I thought I had known most of these and the usage of sqlservr.exe was something I wanted to check as it had been ages. For complete list of startup parameters refer book online.

Getting back to UG meet, I couldn’t hold my curiosity back and I was quick to raise my hand to give what I thought could be yet another answer. I shouted out loud the “SHUTDOWN” command. This was something I thought would fit the bill too. The speaker was understanding to say, this was one of the partially correct answer. Taken aback, I eagerly waited for the explanation.

Though the answer was correct, it was to shut down the service and didn’t allow us to restart our SQL Server service. And the speaker, waited for more answers. This made me curious of the fact that there was more to what I knew.

Welcome PowerShell

The speaker was quick to say, now that we have exhausted all the options available – let me talk about Windows PowerShell and it can be yet another way to restart SQL Server Service. It completely slipped my mind about this option. PowerShell is a powerful way to script and do activities with SQL Server. If you haven’t played around, then I strongly suggest to have a look into the same.

Now, how can I restart SQL Server Service? In this example below, I have used the PowerShell ISE IDE to execute the command as identified below:

Restart-Service -Force MSSQLSERVER


Since there was no feedback from the console, I was curious to know if the SQL Server service did restart properly. On examining the Task manager I could confirm that the service did restart because the PID values were completely different after the command got executed.

Sometimes, we carry of knowledge that needs a refresher. Hence taking a student approach to everything we do can help a lot in this learning experience.

Do let me know if you found any other way to restart SQL Server and I would be more than happy to learn it from you.

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

SQL Authority News – Presenting Session at #SQLPASS on November 5, 2014

I will be attending SQLPASS 2014 this year in Seattle. I have attended SQLPASS 5 times before and this is my sixth times for the event. I love this event and that is why every single year I attend the event.

This time I will be presenting on session Secrets of SQL Server: Database Worst Practices as well. Here is the details for my session.

Secrets of SQL Server: Database Worst Practices


“Oh my God! What did I do?” Chances are you have heard, or even uttered, this expression. This demo-oriented session will show many examples where database professionals were dumbfounded by their own mistakes, and could even bring back memories of your own early DBA days. The goal of this session is to expose the small details that can be dangerous to the production environment and SQL Server as a whole, as well as talk about worst practices and how to avoid them.

Shedding light on some of these perils and the tricks to avoid them may even save your current job. After attending this session, developers will only need 60 seconds to improve performance of their database server in their SharePoint implementation. We will have a quiz during the session to keep the conversation alive. Developers will walk out with scripts and knowledge that can be applied to their servers, immediately after the session.

Date: November 05, 2014

Time: 10:15 AM – 11:30 AM

Room: 6B

Why should you attend my session?

When I want to attend any session, I try to reason myself about why should I attend any particular session. Well, if you are wondering the same for me, here are my reasons for you.

  • We will have lots of FUN
  • You will LEARN something while you are having FUN
  • FUN and LEARN

Remember, we are going to have lots of fun and while having fun we are going to learn. This session is set at LEVEL 100, so I will be talking about at the beginning level. With that said if you are an expert DBA/Developer and want to have fun, join me in my session. We are going to have a great time together.

Here is one of the questions which I am going to ask in the session: If you get it right, I will give you a small token of appreciation.

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