none
SQL Server Transactional Replication Fails OS error 3

    Question

  • I have set up Transactional Replication without updating Subscriptions.  The Distribution database appears to be configured correctly (it resides on the same SQL Server instance as the Publication database).  The Publication database seems to work okay, as evidenced by: 1) the initial Snapshot seems to have been set up successfully; and 2) the log reader agent is running successfully.  Where I have the problem is on the Subscription side agent.  It fails to complete giving an error message of:

    The process could not read file 'f:\MSSQL10.MSSQLSERVER\MSSQL\ReplData\unc\PDAEGISDBDEVVM_NWSAEGISMSPDB_NWSAEGISMSPDB_PUB\20121008163528\ZetronUnitNumber_1465.pre' due to OS error 3. (Source: MSSQL_REPL, Error number: MSSQL_REPL20024)

    I believe I know what the problem is, but I don't know how to fix it.  I think the Subscription database is trying to access the file on a path that doesn't specify the Publication database.  I specified that the repldata files would be stored on a locally attached disk with a path name of f:\MSSQL10.MSSQLSERVER\MSSQL\ReplData.  But I am not too sure about how to make this path name known to the Subscription server. 

    I am performing a "pull" subscription.
    • Edited by JimAzSQL2 Tuesday, October 9, 2012 7:54 PM
    Tuesday, October 9, 2012 7:51 PM

Answers

  • A locally attached drive will work just fine.  It just needs to be shared.  Right-click the folder -> Properties -> Sharing and share it.

    Also, you need to update the Distributor Properties Default Snapshot Folder to reflect the UNC network share.

    This is the requirement for pull subscriptions.  Only give the Distribution Agent process account permission to the UNC share.


    Brandon Williams (blog | linkedin)

    • Marked as answer by JimAzSQL2 Tuesday, October 9, 2012 8:52 PM
    Tuesday, October 9, 2012 8:37 PM
    Moderator

All replies

  • Is this a pull subscription?

    You must specify a UNC network share such as \\<computername>\snapshot rather than a local path when using pull subscriptions and/or a remote Distributor.

    To alleviate the issue, right-click f:\MSSQL10.MSSQLSERVER\MSSQL\ReplData folder -> Properties -> Sharing -> Advanced Sharing..., and check Share this folder.  Add your Distribution Agent process account to the Share Permissions and give Read access.

    Then right-click the Replication node in Object Explorer -> Distributor Properties... -> Publishers page, and click the ellipses ... and set the Default Snapshot Folder to \\<computername>\snapshot.

    Then start the Distribution Agent.


    Brandon Williams (blog | linkedin)

    Tuesday, October 9, 2012 8:01 PM
    Moderator
  • The account running the agent on the subscriber need to have access to the snapshot folder mentioned in the error,

    I think, your subscriber is in a non-trusted domain. The error is due to domain account cannot be validated.

    You can try with share folder to access the files.


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Tuesday, October 9, 2012 8:15 PM
  • OS error 3 is a path error, not a permissions error.

    When using pull subscriptions you must use a UNC network share, not a local path.

    Secure the Snapshot Folder


    Brandon Williams (blog | linkedin)

    Tuesday, October 9, 2012 8:17 PM
    Moderator
  • Brandon ... thank you for your reply.  I suspected that this could be problematic, so I had already provided "everyone" read access on this folder.  I am thinking that what you previously stated about the path being  UNC network share such as \\PDAegisDBDevVM\MSSQL10.MSSQLSERVER\MSSQL\ReplData .  I restarted the Subscription agent and it still failed.  Any suggestions? 
    Tuesday, October 9, 2012 8:18 PM
  • The folder needs to be shared and the Distributor properties Default Snapshot Folder needs to be updated to reflect this.

    Also, please post the error message here.


    Brandon Williams (blog | linkedin)

    Tuesday, October 9, 2012 8:20 PM
    Moderator
  • Here is the exact error message:

    The process could not read file 'f:\MSSQL10.MSSQLSERVER\MSSQL\ReplData\unc\PDAEGISDBDEVVM_NWSAEGISMSPDB_NWSAEGISMSPDB_PUB\20121008163528\ZetronUnitNumber_1465.pre' due to OS error 3. (Source: MSSQL_REPL, Error number: MSSQL_REPL20024)

    If I understand you correctly, I can only specify a network shared drive, not a locally attached drive?  I don't think this will work because the data is confidential and only supposed to be read between these two servers (they are on their own network).

    Thx.

    Tuesday, October 9, 2012 8:25 PM
  • A locally attached drive will work just fine.  It just needs to be shared.  Right-click the folder -> Properties -> Sharing and share it.

    Also, you need to update the Distributor Properties Default Snapshot Folder to reflect the UNC network share.

    This is the requirement for pull subscriptions.  Only give the Distribution Agent process account permission to the UNC share.


    Brandon Williams (blog | linkedin)

    • Marked as answer by JimAzSQL2 Tuesday, October 9, 2012 8:52 PM
    Tuesday, October 9, 2012 8:37 PM
    Moderator