Understanding Retention Periods for SQL Server Transactional Replication
By: Mohammed Moinudheen | Updated: 2012-04-09 | Comments (11) | Related: More > Replication
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?
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
Executed as user: P\Moinu. The subscription created by Subscriber 'P' to publication 'REP_P' has expired and has been dropped. [SQLSTATE 01000] (Message 14157) The subscription created by Subscriber 'P' to publication 'REP_P' has expired and has been dropped. [SQLSTATE 01000] (Message 14157) The subscription created by Subscriber 'PHOENIX' to publication 'REP_P' has expired and has been dropped. [SQLSTATE 01000] (Message 14157) The Subscriber was dropped. [SQLSTATE 01000] (Message 14062) The subscription created by Subscriber 'P' to publication 'REP_P' has expired and has been dropped. [SQLSTATE 01000] (Message 14157). The step succeeded.
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:
Executed as user: P\Moinu. Deactivated subscriptions. [SQLSTATE 01000] (Message 21011) Deactivated initial snapshot for anonymous publication(s). New subscriptions must wait for the next scheduled snapshot. [SQLSTATE 01000] (Message 21077) Removed 8 replicated transactions consisting of 87 statements in 0 seconds (0 rows/sec). [SQLSTATE 01000] (Message 21010). The step succeeded.
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.
- 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
Last Updated: 2012-04-09
About the author
View all my tips