locked
distribution database cann't be dropped RRS feed

  • Question

  • i  dropped all publisher and subscribers
    i tried to drop the distribution database using the command:
    EXEC sp_dropdistributor @no_checks = 1

    i got error:


    TITLE: Microsoft.SqlServer.ConnectionInfo
    ------------------------------

    SQL Server could not disable publishing and distribution on 'server1'.

    ------------------------------
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
    The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
    The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
    The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
    The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
    The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
    The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
    The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
    The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.
    Changed database context to 'master'. (Microsoft SQL Server, Error: 3930)


    how can i drop the distribution database and resolve that error?
    thanks

    Tuesday, September 1, 2009 10:55 PM

Answers

  • Hi

    After you drop all the subscriptions and the publications, you can drop the relevant Distributor. However, before you drop the distributor, you must drop the subscriber designation from Publisher. So before you run sp_dropdistributor, you need to run sp_dropsubscriber first.

    Regards
    Monday, September 7, 2009 3:38 AM

All replies

  • Hi

    Based on the error message, we could know that the transaction used to drop the distribution database is not able to committed and be rolled back. To figure out the roor cause of the problem, we need to run the sql profiler trace when the issue happens. for the problem, we could use the default of the configuratin of the sql profiler.

    after we capture the trace file, first we need to find the error(Error: 3930) and then check the transactions which run before the error occurs to get the cause of the problem.

    also, if it is not convenient for you to analysis the profiler trace, you could upload the trace file into you skydriver and only share it with us((support-sqlforum@live.com).) so that I can help you to analysis the trace file;

    Note: after you upload the file, please post the link here.

    Regards

    Thursday, September 3, 2009 7:23 AM
  • This error also come when there is a connection made to distributor by somesource.

    U need to check the activity monitor and kill all the open transaction on distributor dastabase.
    Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.
    Thursday, September 3, 2009 9:44 AM
  • run dbcc opentran in each database on the publisher. You should find an open replication related transaction. Kill it and issue commit tran statements in each database.

    Then try to run the drop distribution database command again.
    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    • Proposed as answer by Jacx Friday, September 4, 2009 6:50 AM
    Thursday, September 3, 2009 1:20 PM
    Answerer
  • Could you paste the error message which you are getting when trying to disable the publication from the management studio and also paste the error message which you are getting when using the procedure sp_dropdistributor
    A Junior DBA
    Friday, September 4, 2009 6:53 AM
  • Thanks all for reply and your valuable advices.


    i think it is enough to run the script: EXEC sp_dropdistributor @no_checks = 1 , based on the article http://support.microsoft.com/default.aspx/kb/324401

    but i could drop the distribution database by running the following two statements in sequence on the distributor server (which also is a publisher)

    exec sp_dropdistributiondb @database = N'distribution'
    GO


    exec sp_dropdistributor @no_checks = 1, @ignore_distributor = 1
    GO


    • Proposed as answer by cmawman Friday, March 11, 2011 2:13 PM
    Saturday, September 5, 2009 10:42 PM
  • Hi

    After you drop all the subscriptions and the publications, you can drop the relevant Distributor. However, before you drop the distributor, you must drop the subscriber designation from Publisher. So before you run sp_dropdistributor, you need to run sp_dropsubscriber first.

    Regards
    Monday, September 7, 2009 3:38 AM
  • Thanks Mark, sorry for delay in reply.
    good advice for dropping subscriber designation.
    Friday, September 18, 2009 7:50 PM