SQL Replication

-- to get what are the errors that are in the server
select  * from distribution..MSrepl_errors with(nolock) where time > '20120221' order by time desc
select  top 10 * from distribution..MSrepl_errors with(nolock)  order by time desc
---------------------------------------------------------------------------------

-- to get what was the command that was cauing the issue. From here we get the hint what is the table that is involved.
exec distribution..sp_browsereplcmds @xact_seqno_start='0x0001492D0000299B000600000000', @xact_seqno_end='0x0001492D0000299B000600000000'

{CALL [sp_MSdel_dbot_server_status] (53966)}

msdb table  which has replication issues
-------
select * from sysreplicationalerts
select distinct subscriber,article from sysreplicationalerts
select distinct publisher,publisher_db,article from sysreplicationalerts
select distinct 'select count(*) '+''''+article+''''+'from ' +publisher_db + '..'+ article + ' with (nolock)' from sysreplicationalerts



-- to run a job from command line.
EXEC msdb.dbo.sp_start_job N'FESCOVAWSA04W3-Aarow_acp-aarow_acp-11' ;
GO

--Adding transactional article in an existing publication. Here a lot of parameters are environment specific and t_aaflc_fund_fmly_lkup
--is the table that is being added to the existing publishing.
use [Aarow_acp]
exec sp_addarticle @publication = N'aarow_acp',
@article = N't_aaflc_fund_fmly_lkup',
@source_owner = N'dbo',
@source_object = N't_aaflc_fund_fmly_lkup',
@type = N'logbased',
@description = N'',
@creation_script = N'',
@pre_creation_cmd = N'drop',
@schema_option = 0x000000000803509F,
@identityrangemanagementoption = N'manual',
@destination_table = N't_aaflc_fund_fmly_lkup',
@destination_owner = N'dbo',
@status = 24,
@vertical_partition = N'false',
@ins_cmd = N'CALL [sp_MSins_dbot_aaflc_fund_fmly_lkup]',
@del_cmd = N'CALL [sp_MSdel_dbot_aaflc_fund_fmly_lkup]',
@upd_cmd = N'SCALL [sp_MSupd_dbot_aaflc_fund_fmly_lkup]'
GO


-- to compare data on row basis. using -c to compare table data on column basis.
tablediff -sourceserver PWIBOSSQLA10V3 -sourcedatabase RSOWDistribution2 -sourcetable t_server_status -destinationserver PWIBOSSQLA10V3 -destinationdatabase RSOWDistribution1 -destinationtable t_server_status -q -o


select log_reuse_wait_stats,name from sys.databases --- check if replication appears in the description

distribution.dbo.msrepl_commands

----------------------------
to find the ditributor/subscriber/publisher detail

select article,publisher_db from distribution..msarticles
select * from distribution..MSpublisher_databases
select * from distribution..MSsync_states
select * from distribution..MSsubscriber_info

IHsubscriptions
MSsubscriber_info

http://saveadba.blogspot.com/2011/08/adding-new-article-without-generating.html


Thanks,
Tushar Kanti