Replication Commands

/*

Run all the below commands in Distribution database. When the distribution Agent job is stopped

*/

USE distribution

go

SELECT * from MSrepl_transactions

order by entry_time desc

/*It gives the transaction and command details

Don't give xact_seqno in single quotes

*/

select * From MSrepl_commands

where xact_seqno=0x0000003000000010001A

GO

/*

Column value here coming from the column of command

in MSRepl_commands table,give here xact_seqno_start in

single quotes

*/

exec sp_browsereplcmds

@command_id=1,

@xact_seqno_start='0x0000003000000010001A',

@xact_seqno_end='0x0000003000000010001A',

@publisher_database_id=2

go

EXEC sp_replmonitorsubscriptionpendingcmds

@publisher='RAMESH',@publisher_db='ADHOC',

@Publication='Adhoc',

@Subscriber='RAMESH\TEST',

@Subscriber_db='Adhoc',

@subscription_type=0

/*

Run the below command in Publisher database

*/

use DatabaseName

go

exec sp_repltrans

sp_helpdistributor--By Running this command in the Publisher serer we can find the Distributor server name

sp_browsereplcmds--run this command under Distribution database,Under distributor server
@publication='MyPub',
@property='immediate_sync',
@value='false';
GO
SELECT * FROM [dbo].[MSsubscriptions]
GO
EXEC sp_replmonitorsubscriptionpendingcmds
@publisher ='IN-MAMILLR1',--'Enter publisher server name',
@publisher_db = 'NorthWind',--'Enter publisher database name',
@publication ='TransactionalREPL_NorthWind',--'Enter name of publication',
@subscriber ='IN-MAMILLR1\THIRDREPLICA',--'Enter subscriber server name',
@subscriber_db ='NorthWind',
@subscription_type ='0' --0 for push and 1 for pull 
 

sp_changePublication

If we expect that replication should not generate a snapshot for all the articles except the one that we have added. We can run the below queries.

exec sp_changepublication

        @publication=N'PublicationName'

      , @property=N'allow_anonymous'

      , @value='false';

go

exec sp_changepublication

        @publication=N'PublicationName'

      , @property=N'immediate_sync'

      , @value='false';

/*

Run this commnad in publisher server and in publisher database database,Un distributed command to distributor from Publisher

Here log reader agent is stopped

*/

exec sp_replcmds

GO

/*

Run this commnad in publisher server and in publisher database database,Un distributed command to distributor from Publisher

Here log reader agent is stopped

*/

USE Test

go

exec sp_replshowcmds @maxtrans =1(  @maxtrans count should be equal to the number rows that are affected in the above sp_replcmds count)

/*

 Run this command in distribution database, when the distributiion agent

 got stopped to tell you howmany commands need to go to subscriber

*/

use distribution

go

sp_browsereplcmds

/*

 Run this command in distribution database, when the distributiion agent

 got stopped

*/

exec sp_replmonitorsubscriptionpendingcmds

@publisher='ramesh',

@publisher_db='test',

@publication='Transactional_Test',

@subscriber='ramesh\test',

@subscriber_db='test',

@subscription_type=1

/*

  run this command in publisher database to find the path of

  snapshot

*/

exec sp_browsesnapshotfolder

@publication='Transactional_Test'
GO

use distribution
GO
SELECT * FROM MSpublisher_databases
go
select db_name(publisher_database_id),* from MSRepl_Commands
where publisher_database_id=1051
GO
exec sp_browsereplcmds
@publisher_database_id=1051,
@xact_seqno_start='0x0000886A0001658801B700000384',
@xact_seqno_end='0x0000886A0001658801B700000384',

@command_id=1983