none
Purging Transaction Replication Snapshot Folder Contents RRS feed

  • Question

  • Hello,

    SQL2k16 Ent ed. Articles from 4 different user databases are set for transitional replication from ServerA to ServerB. ServerB has both Distributor and Subscriber and ServerA has Publication. The snapshot is written to a network share of ServerB. There are 4 different parent folders for each of the said user databases.

    Currently, the replication happens twice a day and each time it runs, it creates a folder with the date/time stamp on it. An example is given below:

    20190703000008

    Within each of the above date/time stamp folder, there are bunch of .bcp files, .idx files etc.

    Is there a replication server related process that can be used to keep only one copy of the said folder instead of keeping multiple copies since some of the replicated database size has over 100 GBs which eventually will result in running out of disk space if multiple copies are kept. If there are no sql server related processes to control this, then, will end up coming up a powershell script or similar possible solution.

    Will greatly appreciate your help.

    Thanks.

    Victor


    Victor

    Wednesday, July 3, 2019 11:13 PM

All replies

  • There will only be one folder for each publication. Within it there will be subfolders for every time your snapshot agent runs. Can you ensure that your snapshot agent runs on demand instead of on schedule? You can locate your snapshot agent in the jobs folder to change the schedule.
    Thursday, July 4, 2019 1:16 AM
    Moderator
  • Hi Victor,

     

    >>Currently, the replication happens twice a day and each time it runs, it creates a folder with the date/time stamp on it. An example is given below:

    20190703000008

    Within each of the above date/time stamp folder, there are bunch of .bcp files, .idx files etc.

     

    Whenever the Snapshot Agent runs, a folder named 20190703000008 is created. This depicts the time the Snapshot Agent is running.

     

    >>If there are no sql server related processes to control this, then, will end up coming up a powershell script or similar possible solution.

     

    Would you please check the maintenance job Distribution cleanup .It removes replicated transactions from the distribution database. This clean up job also deletes any old snapshot files that were created by the snapshot agent job keeping only the latest files that get generated the last time this job ran.

     

    So, we need to ensure that this job and the Agent history clean up: distribution job is scheduled to run periodically to mitigate space issues that arises due to the replication configuration. For more details, please refer to https://www.mssqltips.com/sqlservertip/2375/space-impact-of-replication-snapshot-agent-job-in-sql-server/

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    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

    Friday, July 5, 2019 7:32 AM
  • Dai and Hillary,

    Thanks. I did find out that I can control the dump to replication folder via the agent job itself. The synchronization runs continuously, but the snapshot is now set to run only when the agent is restarted which will prevent it from dumping new copies on whatever time interval it was earlier set to.

    Victor


    Victor

    Friday, July 5, 2019 8:06 PM
  • Hi Victor,

     

    Thank you for you reply. Have you solved this problem ?  In order to close this thread, please kindly mark useful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best regards,

    Dedmon Dai


    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 8, 2019 2:03 AM
  • Yes, no more issues.

    Thanks.


    Victor

    Sunday, July 21, 2019 10:38 PM
  •  In order to close this thread, please kindly mark useful replies as answers. By doing so, it will benefit all community members who are having this similar issue.

    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 5:57 AM