Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

Understanding Retention Periods for SQL Server Transactional Replication

MSSQLTips author Mohammed Moinudheen By:   |   Read Comments (10)   |   Related Tips: More > 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.

Publication Retention period

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. 

Description of expired subscription job
 

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

Message
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. 

Distributor properties

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

Distributor retention details

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:

Message
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.

Error message in replication monitor

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


Last Update: 4/9/2012


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

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Monday, February 04, 2013 - 7:10:24 AM - Aradhya Read The Tip

Hi,

 

   Any idea if I change the subscription expiration value at any time cause reinitialization?

 

Thanks,

Aradhya

 


Friday, April 13, 2012 - 5:05:12 AM - Srinath Read The Tip

Enjoyed a lot reading this article....Keep posting articles Moinu... Thank you!!!


Thursday, April 12, 2012 - 2:49:27 PM - deepak Read The Tip

Hi Sir,

Actually these are the error message, which I always got in my environment. Not sure how to resolve them, as I am new to replication. But I have learned a lot from your tips and Mr. Lee's tips and all other renowed and intellectual author's tips like you. I am very much thankful to you that you are sharing lot of knowledge through MSSqlTips. and all other authors too. Please provide the below said answer in the form of tips whenever you have time. These are the error from Transactional Replication. As I know in your company there is also setup of Transactional Replication. I hope It would not  too much difficult for you to provide answers in the form of tips.

(1) DESCRIPTION:      Replication-Replication Distribution Subsystem: agent MachineName-Publishing DB Name- Subscriber Machine Name failed. Cannot insert duplicate key row in object 'dbo.ABC' with unique index 'AK_ABCRoleName'.

(2)

DESCRIPTION:      Replication-Replication Distribution Subsystem: agent Publisher Machine Name -Publishing DB Name-Subscriber Machine Name failed. The row was not found at the Subscriber when applying the replicated command.

 

(3)

Replication-Replication Distribution Subsystem: agent Publisher Machine Name -Publishing DB Name- Subscriber Machine Name failed. The Distribution Agent reached the end of current command (transaction: 0x0x00002ad50000cc8a00ce00000000 command 28), but the number of bytes read (157) is less than the total size (3211318) indicated in command header. When troubleshooting, backup the di

 

(4)

DESCRIPTION:      Replication-Replication Distribution Subsystem: agent Machine Name-Publishing DB Name-Subscriber Machine Name scheduled for retry. TCP Provider: An existing connection was forcibly closed by the remote host.

 

 
(5)

DESCRIPTION:      Replication-Replication Distribution Subsystem: agent Publisher Machine Name-DB Name- Tables Name - Subscriber DB Name-Subscriber Machine Name-35 failed. Could not find stored procedure 'sp_MSupd_dboInsuranceEntity'.

 

 

Thanks

Deepak

 

 


Thursday, April 12, 2012 - 2:30:32 PM - Abdul Mohammed Read The Tip

Thanks Mohiuddin. I get it now.


Thursday, April 12, 2012 - 12:20:49 PM - Mohammed Moinudheen Read The Tip

@Deepak,

As we can see these are different replication error messages, I would consider it is a separate tip for the future.

Thanks.


Thursday, April 12, 2012 - 12:13:23 PM - Mohammed Moinudheen Read The Tip

@Abdul,

Distribution retention period is not irrelevant. If the immediate sync property is set to false, then the transactions within the minium distribution retention period would be retained and the rest of them would be deleted if they are replicated to the subscribers. Even the link you posted describes the same.

You could test this scenario by performing following steps on a transactional replication set up
1) Set immediate sync property to false on your publication db
2) Change the distribution minumum retention period to atleast 1 hour
3) Leave the distribution maximum retention period to default 72 hours.
4) Insert some values into the publication db
5) Ensure it is replicated to the subscriber
6) Query the distribution database to get the count of the msrepl_commands and msrepl_transactions tables
7) Run the distribution cleanup job
8) Get the count again as in step (6)..you would notice no change...none of the records are deleted
9) As in step(2), change the distribution minimum retention period to 0 hour
10) Then follows steps (4) to step (8)....you would notice that the records gets deleted from the msrepl_commands and msrepl_transactions tables

Hope this clarifies.

Thanks.

 


Thursday, April 12, 2012 - 8:28:12 AM - Deepak Kumar Read The Tip

Hi Sir,

Please answer the following question, step-by-step.

(1) DESCRIPTION:      Replication-Replication Distribution Subsystem: agent MachineName-Publishing DB Name- Subscriber Machine Name failed. Cannot insert duplicate key row in object 'dbo.ABC' with unique index 'AK_ABCRoleName'.

(2)

DESCRIPTION:      Replication-Replication Distribution Subsystem: agent Publisher Machine Name -Publishing DB Name-Subscriber Machine Name failed. The row was not found at the Subscriber when applying the replicated command.

 

(3)

Replication-Replication Distribution Subsystem: agent Publisher Machine Name -Publishing DB Name- Subscriber Machine Name failed. The Distribution Agent reached the end of current command (transaction: 0x0x00002ad50000cc8a00ce00000000 command 28), but the number of bytes read (157) is less than the total size (3211318) indicated in command header. When troubleshooting, backup the di

 

(4)

DESCRIPTION:      Replication-Replication Distribution Subsystem: agent Machine Name-Publishing DB Name-Subscriber Machine Name scheduled for retry. TCP Provider: An existing connection was forcibly closed by the remote host.

 

 
(5)

DESCRIPTION:      Replication-Replication Distribution Subsystem: agent Publisher Machine Name-DB Name- Tables Name - Subscriber DB Name-Subscriber Machine Name-35 failed. Could not find stored procedure 'sp_MSupd_dboInsuranceEntity'.

 

 

 


Wednesday, April 11, 2012 - 4:09:37 PM - Abdul Mohammed Read The Tip

Hi Mohiuddin,

What happens to the distribution commands when the immediate sync property is set to false. From the link below if we set the immediate sync property to false all the transactions from the distribution database are deleted once they are replicated regardless of retention period. So this makes the retention period irrelevant right?

http://blogs.msdn.com/b/chrissk/archive/2009/07/27/how-replication-setting-immediate-sync-may-cause-transactional-replication-distribution-database-growth.aspx

I am bit confused between retention period and immediate sync property what implications they have on how commands are stored in the distribution database.

In my environment the retention period is 10 days but the immediate sync is set to false on all the publications. So does this mean that the data in the distribution database is not stored untill the retention period?.


Monday, April 09, 2012 - 11:01:10 AM - Mohammed Moinudheen Read The Tip

'But not more than' value was set to 0 in distributor property just to show the impact it would have on replication. Otherwise, you could leave it as it is (72 hours) and perform step 4 in 'Distribution Retention Period' section after 72 hours.


Monday, April 09, 2012 - 7:41:36 AM - Rajasekhar Read The Tip

What happen if we set not more than option set to zero i feel some what clarity missing here.can u plz explain in brief




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.