Options to reinitialize subscriptions in SQL Server replication

By:   |   Updated: 2011-12-14   |   Comments (6)   |   Related: > Replication


Problem

I have transactional replication configured in my production environment with multiple subscribers.  The business team has requested that one of the subscriptions be reinitialized, because they think there is some missing data. In this tip we look at the different options that you can use to reinitialize a subscription for transactional replication.

Solution

If we come across this requirement there are different ways by which we could achieve this. In this tip, we will accomplish this by using SQL Server Management Studio (SSMS) and Replication Monitor. Note, both options assume transactional replication is already configured in your server.

Option 1 : Using SQL Server Management Studio (SSMS).

In SSMS go to Replication -> Local Publications -> Locate your publication and expand it. The below screenshot shows the details of the subscriptions.

sql replication publications

Suppose, you wish to reinitialize only the subscription [PHOENIX].[REP_S1].  To do this, right click that subscription and select the 'reinitialize' option and you will get a dialog window as shown in the below screenshot.

sql server reinitialize subscription

Here, we have the option to select either 'Use the current snapshot' or 'Use a new snapshot'. The use current snapshot will use the existing snapshot and the use a new snapshot will use a new current snapshot.  Based on your requirement, select the desired option and then click on 'Mark for Reinitialization' which will enable you to reinitialize the subscription.

If you wish to reinitialize all subscriptions, you would need to right click on the publication and select 'Reinitialize All Subscriptions' as shown in the screenshot below, which would enable you to reinitialize all your subscriptions.

sql reinitialize all subscriptions

Option 2: Using Replication Monitor

In SSMS go to Replication -> right click on replication and select 'Launch Replication Monitor', as shown below.

sql server launch replication monitor

The 'replication monitor' screen should open as shown below. On the left pane, under 'My publishers', click on the publisher node and expand to get a list of the subscriptions.

sql replication monitor reinitialize subscription

In the 'All Subscriptions' tab, you need to select the appropriate subscription and click on 'Reinitialize Subscription' which would enable you to reinitialize only that subscription in the list. Once done, you would encounter the same window (image 2)  as shown in option 1, when you reinitialize a subscription and you would need to either select the existing snapshot or opt for a new one.

If you wish to reinitialize all your subscriptions using replication monitor, you could just right click on the publication node and select 'Reinitialize All Subscriptions' as shown below.

sql replication monitor reinitialize all subscription

Both options could be tested easily by configuring a simple replication setup and performing the sequence of steps as shown above.

Things to note:

  • If you select "Use a new snapshot" the snapshot process will run automatically as long as the job is not disabled.
  • If you select the reinitialize option this will start automatically as long as the distribution agent is running continuously and the snapshot exists. If the distribution agent is scheduled to run at intervals then the snapshot would be applied to the subscription the next time this job runs.
  • It is important to know the implications of reinitializing a subscription.  When this is run all data at the subscriber will be replaced with the new data for all articles in the subscription.  Refer to this tip about implications of applying a new snapshot: http://www.mssqltips.com/sqlservertip/2375/space-impact-of-replication-snapshot-agent-job-in-sql-server
  • It is not possible to reinitialize a subscription using a backup.  If you try you will get this error: "This subscription already exists".
  • The above steps were performed using SQL Server 2008 R2, but should be similar for SQL Server 2005 and later.
Next Steps
  • Consider testing this scenario through a simple transactional replication setup
  • Refer to other related tips on replication to get familiar with the concepts


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

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

View all my tips


Article Last Updated: 2011-12-14

Comments For This Article




Tuesday, June 25, 2013 - 2:55:18 AM - Hemant Kapre Back To Top (25550)

Hello,

I am facing issue like in my sql server database log size is ITSRegidAttribute_log.ldf- 10,263,104KB whenever i shrink this file am not getting free space as I check on that db using command like ,

select*fromsys.databaseswhere name like'%ITSRegidAttribute'

I got column like ('log_reuse_wait_desc' = NOTHING) means what exactly.

Kindly help me out..

Thursday, April 11, 2013 - 2:06:12 PM - Mike Murphy Back To Top (23310)

I agree with bebimbop...Recreating a snapshot is great, but when columns are added to a table, they are in the gui of the article as columns.  Regen the snapshot....snapshot runs fine....pull the subscription to the receiving database.  No new columns for the table.  No errors in the monitor...

recreate the subscription

Monday, January 9, 2012 - 5:00:25 PM - bebimbop Back To Top (15571)

I've tried each of these methods multiple times and ALWAYS fall back to recreating the subscriptions.  I've wasted many an hour trying to figure out which piece is misbehaving and what to restart,  replication monitor is woefully inadequate in that regard.

 

If time is of the essence, just recreate the subscription. You will get back online faster.

 

Thursday, December 15, 2011 - 2:30:02 AM - Srinath Back To Top (15367)

Thats really good stuff, Moinu....Thanks much for this writing......Keep posting,.

Thursday, December 15, 2011 - 2:29:03 AM - bojanna mk Back To Top (15366)

Thanks Moinu.The article looks good and useful.

Wednesday, December 14, 2011 - 1:45:23 PM - Ludwig Guevara Back To Top (15355)

Hi:

This only works with SQL Server 2005, because I couldn't find the same dlls or even the same objects I used for this program.

I create a C# program using Replication DLL

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Replication;
using Microsoft.SqlServer.Server;

Object ReplicationMonitor method EnumLogReaderAgents to get Publishers data

public static DataSet GetPublishers(string ServerName)
{
// 1 per database replicated
DataTable dtLogReaderAgents = new DataTable();
ServerName = GetDistributionServer(ServerName);
ServerConnection cnn = new ServerConnection(ServerName);

cnn.Connect();
if (cnn.IsOpen)
{
ReplicationMonitor replicationMonitor = new ReplicationMonitor(cnn);
replicationMonitor.Load();
replicationMonitor.LoadProperties();
// dbname, name, status, publisher, publisher_db, start_time, time, duration, comments, delivery_time,
// delivered_transactions, delivered_commands, average_commands, delivery_rate, delivery_latency,
// error_id, job_id, local_job, profile_job, agent_id, local_timestamp
dtLogReaderAgents = replicationMonitor.EnumLogReaderAgents().Tables[0];
}
if (cnn.IsOpen)
cnn.Disconnect();
return dtLogReaderAgents;
}

Then look for these columns:

ServerName
string sLogReader = dr["Name"].ToString();
string sComments = dr["comments"].ToString()

Build an email Alert to know which Publisher Server and run T-SQL

[usp_SendLogAgentIsRetryingAfterAnErrorMessage] {0}= ServerName, {1} = LogAgent, {2} = sComments

To reinitialize subscription run job (pointed to msdb), used: sLogReader

 

Reference:

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.replication.replicationmonitor.enumlogreaderagents.aspx

Regards














get free sql tips
agree to terms