Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've got Snapshot replication set up on my SQL Server 2008 production server, and I just noticed that there are snapshots ranging back to a year ago in the snapshot folder. How can I change the retention for these snapshots? Specifically I'd like it to retain snapshots for 5 days.

Here's a screenshot of the folder I'm looking at: Replication folder

share|improve this question
    
I have tried all the possible ways you have explained but still having same problem as some one explained very earlier. –  user6579 Feb 9 '12 at 19:23
add comment

3 Answers

This is usually an issue where the permissions on xp_cmdshell have been modified for security reasons. You can also have this issue if the SQL Agent account does not have the required network permissions to delete data from your replication folder.

  • Verify xp_cmdshell is enabled and that the account executing it has proper permissions.

    USE master
    GO
    EXEC sp_configure 'show advanced option', '1';
    reconfigure;
    GO
    exec sp_configure 'xp_cmdshell';
    

Output should be:

name         minimum    maximum config_value run_value
xp_cmdshell  0          1       1            1
share|improve this answer
    
One more thing to check is that your cleanup jobs are enabled. I have seen cases where the job was simply disabled. –  Kenneth Sep 23 '11 at 16:24
    
Well, i have a job called "Agent hisotry clean up: distribution", and it's been executing just fine (and it's enabled). –  DForck42 Sep 23 '11 at 17:22
    
See edits...... –  Kenneth Sep 23 '11 at 17:37
    
I enabled xm_cmdshell earlier, and it now shows those results. –  DForck42 Sep 23 '11 at 18:20
add comment

In my experience, snapshot folder retention works like this:

If anonymous subscribers are not enabled for that publication and if all subscribers have consumed the snapshot, the next run of the clean up agent will remove the snapshot.

If anonymous subscribers are enabled and the distribution retention period has elapsed, the next run of the clean up agent will remove the snapshot.

In your situation, where you want to retain snapshots for 2 months, you could accomplish this by allowing anonymous subscriptions and setting the distribution retention to 2 months, if feasible.

If your distribution database is used for transactional replication, as well, you could potentially see the distribution database grow a lot.

Hope this helps,

Matt

share|improve this answer
    
where do i set the distribution retention? –  DForck42 Oct 5 '11 at 14:56
2  
@DForck42 In SSMS, Right-Click Replication and Left-Click Distributor Properties. From there, you can adjust the transactional retention period. –  Matt M Oct 5 '11 at 19:49
add comment

Try this:

Go to the properties of the Replication Monitor (right click on Replication Monitory -> Properties) and you should see a History Retention period there. You should be able to set it there.

share|improve this answer
    
I don't think that applies to snapshot replication. –  Kenneth Oct 2 '11 at 14:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.