none
Transactional Replication error RRS feed

  • Question

  • Hi,

    I am getting below error while creating Transactional Replication, database location is in D:\Data.

    TITLE: New Publication Wizard
    ------------------------------

    SQL Server could not configure 'SQLSTD-UAEDC1\MSSQL' as a Distributor.

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

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

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

    Destination path E:\MSSQL\LOG is not valid. Unable to list directory contents. Specify a valid destination path.
    Changed database context to 'master'. (Microsoft SQL Server, Error: 14430)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=14430&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------
     
    Sunday, July 28, 2019 3:05 PM

All replies

  • It is complaining about the E drive. Does the folder MSSQL\Log exist there?
    Sunday, July 28, 2019 9:53 PM
    Moderator
  • Hi Superuser2013,

    According to your error log, please check the account that your SQL server service is running under, I suggest you to run SQL server service under an administrator account. Please check your account has the permissions on folders that you want to access. Please refer to this log to get more useful information.

    I also find a similar thread. Hope it could help you. 

    Best regards,
    Cathy Ji

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, July 29, 2019 3:02 AM
  • Hi hilary

    it does exist on D drive.

    this is a VM, for some reason D partition got detached few months back when we attached back sql DB was not running. we found that drive letter has changed to E and when changed back to D , database started working.later we added E partition for SQL DB backup.

    Monday, July 29, 2019 6:38 AM
  • Hi Cathy,

    i had changed SQL service to administrator account, still no luck.

    Monday, July 29, 2019 6:35 PM
  • for whatever reason it want to put the log file for the distribution database on the E drive.  Try this:

    /****** Scripting replication configuration. Script Date: 7/29/2019 2:41:00 PM ******/
    /****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/
    
    /****** Begin: Script to be run at Publisher ******/
    
    /****** Installing the server as a Distributor. Script Date: 7/29/2019 2:41:00 PM ******/
    use master
    exec sp_adddistributor @distributor = @@SERVERNAME, @password = N''
    GO
    
    -- Adding the agent profiles
    declare @config_id int
    exec sp_add_agent_profile @profile_id = @config_id OUTPUT, @profile_name = N'QueryTimeout', @agent_type = 4, @profile_type  = 1, @description = N''
    exec sp_change_agent_parameter @profile_id = @config_id, @parameter_name = N'-BcpBatchSize', @parameter_value = N'100000'
    exec sp_change_agent_parameter @profile_id = @config_id, @parameter_name = N'-ChangesPerHistory', @parameter_value = N'100'
    exec sp_change_agent_parameter @profile_id = @config_id, @parameter_name = N'-DestThreads', @parameter_value = N'2'
    exec sp_change_agent_parameter @profile_id = @config_id, @parameter_name = N'-DownloadGenerationsPerBatch', @parameter_value = N'50'
    exec sp_change_agent_parameter @profile_id = @config_id, @parameter_name = N'-DownloadReadChangesPerBatch', @parameter_value = N'100'
    exec sp_change_agent_parameter @profile_id = @config_id, @parameter_name = N'-DownloadWriteChangesPerBatch', @parameter_value = N'100'
    exec sp_change_agent_parameter @profile_id = @config_id, @parameter_name = N'-FastRowCount', @parameter_value = N'1'
    exec sp_change_agent_parameter @profile_id = @config_id, @parameter_name = N'-HistoryVerboseLevel', @parameter_value = N'2'
    exec sp_change_agent_parameter @profile_id = @config_id, @parameter_name = N'-KeepAliveMessageInterval', @parameter_value = N'300'
    exec sp_change_agent_parameter @profile_id = @config_id, @parameter_name = N'-LoginTimeout', @parameter_value = N'15'
    exec sp_change_agent_parameter @profile_id = @config_id, @parameter_name = N'-MakeGenerationInterval', @parameter_value = N'1'
    exec sp_change_agent_parameter @profile_id = @config_id, @parameter_name = N'-MaxBcpThreads', @parameter_value = N'2'
    exec sp_change_agent_parameter @profile_id = @config_id, @parameter_name = N'-MaxDownloadChanges', @parameter_value = N'0'
    exec sp_change_agent_parameter @profile_id = @config_id, @parameter_name = N'-MaxUploadChanges', @parameter_value = N'0'
    exec sp_change_agent_parameter @profile_id = @config_id, @parameter_name = N'-MetadataRetentionCleanup', @parameter_value = N'1'
    exec sp_change_agent_parameter @profile_id = @config_id, @parameter_name = N'-NumDeadlockRetries', @parameter_value = N'5'
    exec sp_change_agent_parameter @profile_id = @config_id, @parameter_name = N'-PollingInterval', @parameter_value = N'60'
    exec sp_change_agent_parameter @profile_id = @config_id, @parameter_name = N'-QueryTimeout', @parameter_value = N'30000'
    exec sp_change_agent_parameter @profile_id = @config_id, @parameter_name = N'-SrcThreads', @parameter_value = N'2'
    exec sp_change_agent_parameter @profile_id = @config_id, @parameter_name = N'-StartQueueTimeout', @parameter_value = N'0'
    exec sp_change_agent_parameter @profile_id = @config_id, @parameter_name = N'-UploadGenerationsPerBatch', @parameter_value = N'50'
    exec sp_change_agent_parameter @profile_id = @config_id, @parameter_name = N'-UploadReadChangesPerBatch', @parameter_value = N'100'
    exec sp_change_agent_parameter @profile_id = @config_id, @parameter_name = N'-UploadWriteChangesPerBatch', @parameter_value = N'100'
    exec sp_change_agent_parameter @profile_id = @config_id, @parameter_name = N'-Validate', @parameter_value = N'0'
    exec sp_change_agent_parameter @profile_id = @config_id, @parameter_name = N'-ValidateInterval', @parameter_value = N'60'
    GO
    -- Updating the agent profile defaults
    exec sp_MSupdate_agenttype_default @profile_id = 1
    GO
    exec sp_MSupdate_agenttype_default @profile_id = 2
    GO
    exec sp_MSupdate_agenttype_default @profile_id = 4
    GO
    exec sp_MSupdate_agenttype_default @profile_id = 6
    GO
    exec sp_MSupdate_agenttype_default @profile_id = 11
    GO
    
    -- Adding the distribution databases
    use master
    exec sp_adddistributiondb @database = N'distribution', @data_folder = N'D:\MSSQL\Data', @data_file = N'distribution.MDF', @data_file_size = 501, @log_folder = N'D:\MSSQL\Data', @log_file = N'distribution.LDF', @log_file_size = 521, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @deletebatchsize_xact = 5000, @deletebatchsize_cmd = 2000, @security_mode = 1
    GO
    
    -- Adding the distribution publishers
    exec sp_adddistpublisher @publisher = @@SERVERNAME, @distribution_db = N'distribution', @security_mode = 1, @working_directory = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\ReplData', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
    GO
     

    Monday, July 29, 2019 6:42 PM
    Moderator
  • Hi Cathy,

    i had changed SQL service to administrator account, still no luck.

    Hi Superuser2013,

    I want to confirm that you have followed the steps from the blog and tried the solution from the thread . But they all not worked. Is it right?

    Best regards,

    Cathy Ji


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, July 30, 2019 8:40 AM