Tag Archives: Replication

Replication – Script – How to add a table to an existing publication in SQL Server

Scripts, SQL Server Replication August 30, 2013 Leave a comment

In this article i will share the script that I use when I add a table to an existing publication in SQL Server.
The below script would help us to add any table to an existing publication. The most important thing about adding a table to replication is that the table must have a primary key.

Below is the TSQL script to achieve the same.

use [databasename]--Name of the table involved in Replication
Go
exec sp_addarticle @publication = N'NameOfPublication',
@article = N'TestTable', @source_owner = N'dbo',
@source_object = N'TestTable', 
@type = N'logbased',@description = null,
@creation_script = null,
@pre_creation_cmd = N'drop', @schema_option = 0x0000000008035DDB,
@force_invalidate_snapshot = 1,
@identityrangemanagementoption = N'manual',
@destination_table = N'TestTable',
@destination_owner = N'dbo', @status = 16,
@vertical_partition = N'false',
@ins_cmd = N'CALL sp_MSins_dboTestTable',
@del_cmd = N'CALL sp_MSdel_dboTestTable',
@upd_cmd = N'SCALL sp_MSupd_dboTestTable'
Go

More details on the used stored procedure [sp_addarticle] can be found on the below mentioned technet article

http://technet.microsoft.com/en-us/library/ms173857.aspx

Advertisement

SQL Server Replication – Troubleshooting – LogReader Timeout executing sp_replcmds

SQL Server Replication August 15, 2013 Leave a comment

I came across this excellent article from Chris Skorlinski which talks about logreader timeout errors. All DBA’s who have managed a replicated environment must have encountered this error. This article talks about the Root-Cause of this issue and advises a resolution. Worth a read.

http://blogs.msdn.com/b/repltalk/archive/2010/10/25/troubleshooting-logreader-timeout-executing-sp-replcmds.aspx

SQL Server Replication – Troubleshooting – Using verbose history agent profile

SQL Server Replication March 30, 2013 Leave a comment

Read this very good article authored by Chris Skorlinski and hence sharing the same in my blog.

The article talks about the efficient use of “-HistoryVerboseLevel 2“. This allows additional diagnostic information while we troubleshoot replication in SQL Server.

Please find the link to the article: HistoryVerboseLevel

SQL Server Replication – Find the number of undelivered commands from a Publisher

Scripts, SQL Server Replication March 23, 2013 Leave a comment

The below mentioned script helps us to find the number of Undelivered Commands from the publisher.
Courtesy: http://www.sqlsoldier.com/wp/sqlserver/countingunreplicatedcommandsandtransactions

With MaxXact (ServerName, PublisherDBID, XactSeqNo)
 As (Select S.name, DA.publisher_database_id, max(H.xact_seqno)
 From distribution.dbo.MSdistribution_history H with(nolock)
 Inner Join distribution.dbo.MSdistribution_agents DA with(nolock)
 On DA.id = H.agent_id
 Inner Join master.sys.servers S with(nolock)
 On S.server_id = DA.subscriber_id
 Group By S.name, DA.publisher_database_id)
 Select MX.ServerName, MX.PublisherDBID, COUNT(*)
 As CommandsNotReplicated
 From distribution.dbo.MSrepl_commands C with(nolock)
 Right Join MaxXact MX On
 MX.XactSeqNo < C.xact_seqno And
 MX.PublisherDBID = C.publisher_database_id
 Group By MX.ServerName, MX.PublisherDBID;

SQL Server Replication – Find the number of undelivered transactions from a Publisher

Scripts, SQL Server Replication March 23, 2013 Leave a comment

The below mentioned script helps us to find the number of undelivered transactions from the publisher.
Courtesy: http://www.sqlsoldier.com/wp/sqlserver/countingunreplicatedcommandsandtransactions

With MaxXact (ServerName, PublisherDBID, XactSeqNo)
 As (Select S.name, DA.publisher_database_id,
 max(H.xact_seqno)
 From distribution.dbo.MSdistribution_history H with(nolock)
 Inner Join distribution.dbo.MSdistribution_agents DA with(nolock)
On DA.id = H.agent_id
 Inner Join master.sys.servers S with(nolock)
 On S.server_id = DA.subscriber_id
 Group By S.name, DA.publisher_database_id)
 Select MX.ServerName, MX.PublisherDBID,
COUNT(*) As TransactionsNotReplicated
 From distribution.dbo.msrepl_transactions T with(nolock)
 Right Join MaxXact MX On
MX.XactSeqNo < T.xact_seqno And
MX.PublisherDBID = T.publisher_database_id
 Group By MX.ServerName, MX.PublisherDBID;
%d bloggers like this: