none
old snapshots not being deleted RRS feed

  • Question

  • I have default retention period of >0 and <72hrs. The Agent account has full rights over the folder tree that the snapshots are stored in. 

    SQL Server 2017
    Server 2012 R2

    Wednesday, May 1, 2019 5:25 PM

Answers

  • Your scripts ran successfully, but snapshots are persisting. At this point I'm thinking I'll just make a powershell script and call it from task scheduler to kill the old snap shots every night. 

    EDIT: I've assigned SQL server to run the following Powershell Script: 

    #Days older than
    $HowOld = -3
    
    #Path to the root folder
    $Path = "myPath"
    
    #Deletion files task
    get-childitem $Path -recurse | where {$_.lastwritetime -lt (get-date).adddays($HowOld) -and -not $_.psiscontainer} |% {remove-item $_.fullname -force -verbose}
    
    #Deletion empty folders task
    do {
      $dirs = gci $Path -directory -recurse | Where { (gci $_.fullName -Force).count -eq 0 } | select -expandproperty FullName
      $dirs | Foreach-Object { Remove-Item $_ }
    } while ($dirs.count -gt 0)
    


    • Marked as answer by oldEquipmentGuy Tuesday, May 7, 2019 8:22 PM
    • Edited by oldEquipmentGuy Tuesday, May 7, 2019 8:23 PM added code block for formatting
    Tuesday, May 7, 2019 1:47 PM

All replies

  • This could be an issue due to insufficient rights of the SQL Server agent on the snapshot folder/share.

    Is your distribution cleanup job working? 

    Wednesday, May 1, 2019 5:49 PM
    Moderator
  • Yeah I would say Hilary is correct - as long as the job is being triggered, it sounds like SQL agent permissions to the snapshot location.

    If it has full rights over the folder, is there anything higher up that could stop this?  For example you may have added permissions to the folder, but if it doesnt have permissions over the folders higher up then the permissions you added wont help.

    If this is a local/domain user, you could try logging on as that user and see whether it can browse the file system.  

    One way that you can rule out permissions is to temporarily set the run as user to an admin or domain admin.  Obviously don't keep it as that but it permissions look OK for this user, that is a good way to rule it out.

    Thanks,

    Matt

    Wednesday, May 1, 2019 6:51 PM
  • Hi oldEquipmentGuy,

    Could you check the Job History of the Distribution Clean Up: Distribution job. For snapshot and transactional replication, distribution cleanup agent purges the non-latest folders. By default this job runs every 10 minutes. Please refer to the following thread.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3d3f85b3-1a38-451e-8dab-3ff9fbd6f1ad/delete-the-old-folder-from-distributor-after-snapshot-replication?forum=sqlreplication

    Best Regards,
    Puzzle
    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

    Thursday, May 2, 2019 2:20 AM
  • This could be an issue due to insufficient rights of the SQL Server agent on the snapshot folder/share.

    Is your distribution cleanup job working? 

    The distribution cleanup is working and it says rows are being deleted. The folder as granted all permissions to the agent account 
    Friday, May 3, 2019 8:51 PM
  • Yeah I would say Hilary is correct - as long as the job is being triggered, it sounds like SQL agent permissions to the snapshot location.

    If it has full rights over the folder, is there anything higher up that could stop this?  For example you may have added permissions to the folder, but if it doesnt have permissions over the folders higher up then the permissions you added wont help.

    If this is a local/domain user, you could try logging on as that user and see whether it can browse the file system.  

    One way that you can rule out permissions is to temporarily set the run as user to an admin or domain admin.  Obviously don't keep it as that but it permissions look OK for this user, that is a good way to rule it out.

    Thanks,

    Matt

    It is a domain user.... but curiously, i cannot log in to it... Might be part of the issue., However, i set the agent account to be my own domain account and it is still not deleting the old folders when i run the distribution cleanup. 
    Friday, May 3, 2019 8:52 PM
  • Hi oldEquipmentGuy,

    Could you check the Job History of the Distribution Clean Up: Distribution job. For snapshot and transactional replication, distribution cleanup agent purges the non-latest folders. By default this job runs every 10 minutes. Please refer to the following thread.



    Best Regards,
    Puzzle
    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

    Yes, the job shows that it is running fine but it is not deleting any old folders. 
    Friday, May 3, 2019 8:53 PM
  • Hi oldEquipmentGuy,

    Could you check the immediate_sync column with sp_helppublication? If immediate_sync = 1, the snapshot is kept during the lifetime of the subscription and is kept for future initializations for new subscriptions. You can run the following query to set this option to 0. 

    EXEC sp_changepublication 
    @publication = 'PublicationName',
    @property = 'immediate_sync', 
    @value = 'false'
    GO

    Best Regards,
    Puzzle
    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, May 6, 2019 6:25 AM
  • Indeed it was set to 1, however running your script generated this error: 

    Msg 20011, Level 16, State 1, Procedure sys.sp_MSrepl_changepublication, Line 1285 [Batch Start Line 0]
    Cannot set incompatible publication properties. The 'allow_anonymous' property of a publication depends on the 'immediate_sync' property.
    

    Monday, May 6, 2019 7:49 PM
  • Do this then:

    EXEC sp_changepublication @publication = 'PublicationName', @property = 'allow_anonymous', @value = 'false' GO

    EXEC sp_changepublication 
    @publication = 'PublicationName',
    @property = 'immediate_sync', 
    @value = 'false'
    GO

    Monday, May 6, 2019 7:58 PM
    Moderator
  • Your scripts ran successfully, but snapshots are persisting. At this point I'm thinking I'll just make a powershell script and call it from task scheduler to kill the old snap shots every night. 

    EDIT: I've assigned SQL server to run the following Powershell Script: 

    #Days older than
    $HowOld = -3
    
    #Path to the root folder
    $Path = "myPath"
    
    #Deletion files task
    get-childitem $Path -recurse | where {$_.lastwritetime -lt (get-date).adddays($HowOld) -and -not $_.psiscontainer} |% {remove-item $_.fullname -force -verbose}
    
    #Deletion empty folders task
    do {
      $dirs = gci $Path -directory -recurse | Where { (gci $_.fullName -Force).count -eq 0 } | select -expandproperty FullName
      $dirs | Foreach-Object { Remove-Item $_ }
    } while ($dirs.count -gt 0)
    


    • Marked as answer by oldEquipmentGuy Tuesday, May 7, 2019 8:22 PM
    • Edited by oldEquipmentGuy Tuesday, May 7, 2019 8:23 PM added code block for formatting
    Tuesday, May 7, 2019 1:47 PM