SQL SERVER – Fix : Error : 8501 MSDTC on server is unavailable. Changed database context to publisherdatabase

During configuring replication on one of the server, I received following error. This is very common error and the solution of the same is even simpler.

MSDTC on server is unavailable. Changed database context to publisherdatabase. (Microsoft SQL Server, Error: 8501)

SQL SERVER - Fix : Error : 8501 MSDTC on server is unavailable. Changed database context to publisherdatabase msdtc1

Solution:

Enable “Distributed Transaction Coordinator” in SQL Server.

Method 1:

  1. Click on Start–>Control Panel->Administrative Tools->Services
  2. Select the service “Distributed Transaction Coordinator”
  3. Right on the service and choose “Start”

Method 2:

  1. Type services.msc in the run command box
  2. Select “Services” manager; Hit Enter
  3. Select the service “Distributed Transaction Coordinator”
  4. Right on the service and choose “Start”

SQL SERVER - Fix : Error : 8501 MSDTC on server is unavailable. Changed database context to publisherdatabase msdtc2

SQL SERVER - Fix : Error : 8501 MSDTC on server is unavailable. Changed database context to publisherdatabase msdtc4

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

SQL Error Messages, SQL Replication, SQL Scripts
Previous Post
SQLAuthority News – We’re sorry… … but your computer or network may be sending automated queries. To protect our users, we can’t process your request right now.
Next Post
SQL SERVER – Generate Report for Index Physical Statistics – SSMS

Related Posts

24 Comments. Leave new

  • Thank you for sharing, also pinal have you setup merge replication, if so could you blog an article on it. Personally i have done transactional and snapshot replication.

    Reply
  • D P Tripathy
    April 5, 2010 7:45 pm

    Thanks for this article. It helps me a great. I got the same error for first time while configuring replication and confused but solved as per this articles.

    Thanks you very much….

    Reply
  • What happens if the Distributed Transaction Coordinator is already started when you get that error? Is it ever as easy as just start a server. Maybe in fairy tale land.

    Reply
  • Thanks for sharing this, but when I started that service then I am getting this error

    “Error Code: -2146232060 Error: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding” issue now.

    My application was working perfectly earlier, don’t know whats wrong now. Please guide.

    Reply
  • When ever we change the database in the drop down list available in microsft sql server management studio, we get ‘server is not available’. Does anyone know why we get such a message?

    Reply
  • System.Data.SqlClient.SqlException was unhandled by user code
    Message=”Insert Error: Column name or number of supplied values does not match table definition.”
    Source=”.Net SqlClient Data Provider”
    ErrorCode=-2146232060
    Class=16
    LineNumber=1
    Number=213
    Procedure=””
    Server=”\\\\.\\pipe\\C4EF914B-A3EE-47\\tsql\\query”
    State=1
    StackTrace:
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
    at Ragistration.Button1_Click(Object sender, EventArgs e) in d:\HB_DVS\Ragistration.aspx.cs:line 40
    at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
    at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
    at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
    at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
    at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
    at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

    i got this error when i insert data in table………

    plz give me some solution….

    Reply
  • DECLARE @srvname varchar(50) — Servername
    create table #temp(name varchar(20),mode varchar(50))
    create table #servtemp(servername nvarchar(50),RunDate date)
    DECLARE @getsrvname CURSOR

    SET @getsrvname = CURSOR FOR SELECT name FROM master.sys.servers where is_linked 0 — SET cursor statement
    OPEN @getsrvname
    FETCH NEXT
    FROM @getsrvname INTO @srvname
    WHILE @@FETCH_STATUS = 0

    BEGIN
    insert into #temp exec( ‘[‘+@srvname+ ‘ ].master.sys.xp_loginconfig [login mode]’)
    insert into #servtemp values(@srvname)
    exec(‘select * from #servtemp,#temp’)

    FETCH NEXT
    FROM @getsrvname INTO @srvname
    END
    –drop table #servtemp
    –drop table #temp
    CLOSE @getsrvname
    DEALLOCATE @getsrvname

    I am trying to run this for this I am getting error as :

    Msg 8501, Level 16, State 3, Line 1
    MSDTC on server ‘XYZ’ is unavailable.

    I tried restarting service but still error persists….
    I am Using SQL Server 2008R2.

    Reply
  • If you’re running into this issue on a linked server, you’ll need to enable remote DTC requests on Component Services:
    https://docs.microsoft.com/en-us/previous-versions/commerce-server/ms936442(v=cs.70)

    Reply
  • Thank you it worked :)

    Reply
  • Thanks it worked :D

    Reply
  • Thanks..It worked!

    Reply
  • It worked great!

    Reply
  • hi : can you pls let me know why does a select query works fine using linked server but the sp fails when called through trigger? why should be enable msdtc? if it fails it has to fail for select as well correct? any light into this issue will help me. thanks in advance.

    Reply
    • The trigger is automatically promoted to a distributed transaction. I was able to work around this by setting “Enable Promotion of Distributed Transactions” to False in the Server Options of my linked server.

      Reply
  • Thanks! Very helpful.

    Reply
  • Thanx 4 d post.Very helpful.

    Reply
  • Hi Pinal,
    Can we you msdtc through the internet without vpn. I used to msdtc with vpn but i couldnt use withouth vpn. What can i do for this, or is there any solution to start distrubuted transactions without vpn

    Reply
  • Click on Linked Server Properties. Click Server Options. Change Enable Promotion of Distributed Transactions to FALSE.
    OR
    EXEC master.dbo.sp_serveroption @server=N’XXX’, @optname=N’remote proc transaction promotion’, @optvalue=N’FALSE’
    GO

    Reply
  • Manuel Beltran Jr. (@manuelbeltran8)
    February 13, 2014 10:44 am

    it worked for me..a million tnx!

    Reply
  • Rass Ras (@_RassRas)
    August 22, 2014 3:02 pm

    many thanks, really help me :)

    Reply
  • Kuldeep Kumawat
    January 21, 2016 6:22 pm

    Thank you It helps me

    Reply

Leave a Reply