MSDTC issue

Hi everyone, probably each of you have come across a MSTDTC issue when working with sql server or other DB's. In the last few days i had an issue with it, and took us 2 days to figure out what's teh problem. So i would like to share with you my story so it can help you, as it was really annoying thing. Normally with MSDTC you have to set the DTC on your computer to allow inbound outbound traffic.
You got DTC from component services:

Then you configure it according to your need:

The next step is to check that Distributed transaction service works:

Now after doing all these it still didn't work. So the next step is to check the firewall setting on client and server machine and make sure all relevant ports are open: 135,1433,1434,5000-5050.

A good tools to check if firewall is stopping traffic is: DTCPing. It's important to install it on Client and Server. After installing open it on both machines and only then start making a call, if it's open only on one machine it will tell you that there is an error.

After you ruled out that there is no firewall issue you can use another tool for checking MSDTC issues: DTCTester . 

This article explains in more detail the difference between the tools and how to use DTCTester.

I did all of it but still MSDTC didn't work, i saw that all transactions were being aborted. I did find an article about issues with netbios names, so our IT had a better look at the dns names of the machine and what we found out after 2 days was, that the client machine had a NetBIOS name of more than 15 characters which is not allowed. Now  MSDTC uses NetBios name, but because it can only have 15 charters and not more it took down all the characters after the 15th. So the client made a successful call to the server, but when the server wanted to reply he searched for the machine name with only 15 characters, but couldn't find it as the client has 17 characters name and therefore it failed.

So what IT did is to put on the server machine in the host file the IP of the client machine with the shorted 15 characters, so the server knew that the 15 character name apply to a specific machine with IP given and that way he could connect to the client machine.

Difference between authentication methods on sql server

As we all know sql server provides us with two option to authenticate ourselves: Windows Authentication and Sql Server Authentication. Something very interesting i just figured out when i was using Instant File initialization which i wanted to share wit you. So i had a new server and restored the first database, i saw the restore was stuck at 0%, and remembered that id didn't give permission to sql server user to use instant file Initialization. so i went secpol and added the sql user and restarted the restore process which worked like a charm. After some time i restored a second database, and again saw restore was stuck at 0%. Now i was a bit confused, as i already did IFI before, why would restore take so long? Then i had a better look and saw that this time i logged into sql server using windows authentication with my windows credentials, now that means the sql server runs now with my account and not with the sql server account! But on IFI i only added permission to the sql server account and not to my users, so now that sql server runs with my user it doesn't have permission to run IFI and therefore restore process takes longer. So i cancelled the restore job , logged in to sql server with sql authetication and rerun the restore job which worked now nicely. So what we learn here is that when you use windows authentication the sql server will run with your user account and not the sql server user account.

Database Maintenance

Delete and Shrink Database

Have you ever gotten to the point where your Database was getting very big and needed a shrink and you searched for forums telling you the best way to shrink and the only thing you found were warnings not to shrink, under any circumstances! Was quite helpful right?

I would like to share with you my experience with shrinking in our production DB, and hope it can help you.

In general it's a true statement, not to shrink (look at http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/), but sometimes there is no way out.

Our production database reached 500gb and was keeping getting bigger and bigger. We didn't anticipate that will have such a growth rate in such a small amount of time. The size of the DB caused performance problems, backups took too long (3-4 hours and were getting longer), in short it was a mess.
So the first step was to create an Archive database and then delete from the operational DB the old data.
To delete the old data we looked according to the primary key to find all rows to delete and put the PK into a temp table. Then we had to experience what would be the perfect batch size to delete, as it is an operational DB we can't take it offline and if you delete too much you can cause deadlocks. After some time we found the perfect batch size and started deleting  everyday for 6 hours. After 3 months we finished and came up with a DB of 500 GB that has 30% to shrink, sounds good, right?
But we decided to go a bit further. We first looked at the index fragmentation to see how bad it was because of all the deletes. We were not disappointed, we found a lot of indexes with high fragmentation, over 60%,
so we started to rebuild the indexes!
Yes i know what you think to yourself, why rebuild indexes if you are going to shrink the database, after you shrink you screw the indexes up again and end up with same fragmentation!
That's true, but wanted to bring the size down of the DB and by rebuilding indexes and then shrinking we managed to reduce the size of the DB by 75% so now our DB is 120GB big. So yes we have fragmentation, but it's not too bad and if there is a very bad index will rebuild it, but the main thing is that the size is down and we are not going to let the DB grow back to the monstrous amount is used to be. We achieve that by having daily maintenance job that copied old file to the archive and then deletes them from the DB. By doing this daily it doesn't take long and has no performance affect on the DB.

Shrinking is not a great thing to do, but sometimes necessary, but you have to plan accordingly and take a lot of things into account. Again, if you really hate to shrink you still have Paul's option(mentioned above), creating a new database.