Understanding Retention Periods for SQL Server Transactional Replication

By: Mohammed Moinudheen   |   Comments (11)   |   Related: > Replication

Problem

I have transactional replication configured in production. I have heard about the replication retention period, but what is the significance of this. Would there be any impact to my replication configuration if data is not synchronized with the subscriptions within the retention period?

Solution

There are two types of retention periods we deal with in transactional replication: publication retention period and distribution retention period. Let us try to understand both of them with examples and implications of what happens to the subscriptions once we exceed the defined retention periods. This tip assumes transactional replication is already setup in your database configuration.

Publication Retention Period

In order to view this setting through SQL Server Management Studio (SSMS), navigate to Replication, click on 'Local Publications', locate your 'Publication' and right click and select 'Properties'. Go to 'Subscription expiration' section in the 'General' page as shown in the screenshot below.

You can see the default publication retention period value of 336 hours. In the corresponding radio button, you also see this, 'Subscriptions expire and may be dropped if not synchronized in the following number of hours', which is self-explanatory.

In your test replication configuration, we could test this to verify the statement as displayed in the radio button following the below steps:

1) Modify the interval to a value of 0 hours and click OK.

2) However, when you view the retention period again you would notice that it only allows a minimum value of 1 hour.

3) Stop the distribution agent so that the replicated data does not go to the subscription database.

4) Insert some values into the replication tables in the publication database

5) Check again after an hour.

6) Check the schedule of the Expired subscription clean up job.  As described, this job checks for expired subscriptions and removes them. 

7) Run the Expired subscription clean up job and you will see this message in its job history

8) After this, you will notice that the subscription gets removed. You can check this through SSMS by navigating to 'Replication' , clicking on 'Local Publications' and then by expanding your publication. You will see that the subscription has been deleted and you will need to recreate it.  To recreate, you need to right click on your 'Publication' and then click on 'New Subscription' and follow the steps in the wizard.

These steps to modify the publication retention period could also be carried out using T-SQL commands. Use the sample script below, which needs to be run on your publication database to change the publication retention period to 1 hour.

use Publication_DB_Name 
go
sp_changepublication @publication = 'Your Publication Name', 
@property = 'retention', 
@value = '1'
go

If you set @value parameter to 0 and navigate to the publication properties as shown above to view the retention period, you will notice that the subscription expiration option is be set to 'Subscriptions never expire, but they can be deactivated until they are reinitialized'.

Distribution Retention Period

In order to view this setting through SQL Server Management Studio (SSMS), navigate to Replication and right click on it to view the 'Distributor Properties' . Refer to the screenshot below. 

Click on the button as shown in the above screenshot to view the details of the distribution retention period. Refer to the screenshot below.

From this screenshot, you can view the minimum distribution retention period (At least) and the maximum distribution retention period (But not more than).

In your test replication configuration, we can modify the maximum retention period to a low value to verify the impact it has on the available subscription following these steps.

1) Modify the 'But not more than' value to 0

2) Stop the distribution agent job so that replicated data does not go to the subscription database

3) Insert some values into the replication tables in the publication database

4) Check the schedule of the Distribution clean up: distribution job and make sure it runs after the above 3 steps. Once this job runs you would see this message in its history:

From the job history, you know that the subscription gets deactivated by the distribution clean up job.

5) Start the distribution agent job again

6) Navigate to the replication monitor to view the status of replication. In the replication monitor, the status message would be as what is shown below.

From the error message, the only option would be reinitialize the subscriptions. Refer to this tip which describes the options available to reinitialize subscriptions.

Likewise, the commands to change the distribution retention periods using T-SQL are shown below.  This needs to be run on the distributor and sets the minimum and maximum distribution retention period to 0 hours.

sp_changedistributiondb @database= 'distribution', 
@property= 'min_distretention', 
@value='0' 
go 
sp_changedistributiondb @database= 'distribution', 
@property= 'max_distretention', 
@value='0'
go

From the above examples we can understand the implications on subscriptions once the publication or distribution retention period is crossed.  The above steps were performed using SQL Server 2008 R2.

Next Steps

• Get familiar with replication concepts
• Try testing this tip in your own replication setup where transactional replication is configured
• Refer to other related replication tips

About the author

Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips