View transaction and Commands that are not read by Log Reader in Publisher DB

In Publisher DB, if sys.objects's is_published = 1, or Sys.tables's is_published=1 or is_replicated= 1, indicating that the table is publication's article, updates to these objects, the resulting transaction will be marked as replicated, which needs to be read into distribution by the log reader. At the same time, only one connection has permission to read transaction log. Call sp_replshowcmds and sp_replcmds to get permission to read transaction log, call Sp_replflush to have permission to release connection permission. Sp_replshowcmds is a diagnostic SP, used for troubleshooting, the first call to sp_replcmds session will be considered log Reader, Tag a batch of transaction is being read by the Log Reader Agent, and when the tagged transaction is dumped into distribution, log reader calls sp_repldone Mark transaction as distributed, and the transaction can be safely deleted.

One, using Stored Procedure to control the read of the transaction
1, view transaction

Returns a result set of all the transactions in the publication database transaction log that is marked for replication b UT has not been marked as distributed.

Sp_repltrans

Sp_repltrans Returns information about the publication database from which it's executed, allowing you to view T Ransactions currently not distributed (those transactions remaining on the transaction log that has not been sent to the Distributor). The result set displays the log sequence numbers of the first and last records for each transaction. Sp_repltrans is similar to SP_REPLCMDS (Transact-SQL) but does not return the commands for the transactions.

2, use SP to view command, but not read

Returns the commands for transactions marked to replication in readable format. Sp_replshowcmds can is run only if client connections (including the current connection) is not reading Replic ated transactions from the log.

[] Maxtrans

sp_replshowcmds is used in transactional replication. Using sp_replshowcmds, you can view transactions that currently is not distributed (those transactions Remaining in the transaction log that has not been sent to the distributor).

Clients that run sp_replshowcmds and sp_replcmds within the same database receive error 18752. To avoid this error, the first client must disconnect or the role of the client as Log reader must is released by Executing sp_replflush. After all clients has disconnected from the Log reader, sp_replshowcmds can be run successfully.

Sp_replshowcmds is a diagnostic procedure so returns information about the publication database from which it I S executed.

number:18752, State:1, Procedure:, linenumber:0, Server:, Source:. Net SqlClient Data Provider
Message:only one Log Reader Agent or log-related procedure (sp_repldone, sp_replcmds, and SP_REPLSHOWCMDS) can connect to A database at a time. If you executed a log-related procedure, drop the connection over which the procedure is executed or execute Sp_replflush Over that connection before starting the Log Reader Agent or executing another log-related procedure.

3,flushes the article cache

Sp_replflush

Article definitions is stored in the cache for efficiency. Sp_replflush is used by and replication stored procedures whenever an article definition is modified or dropped .

Only one client connection can has log reader access to a given database. If a client has Log Reader access to a database, executing sp_replflush causes the client to release its access. Other clients can then scan the transaction log using sp_replcmds or sp_replshowcmds.

Importance: You should don't have the to execute this procedure manually. Sp_replflush should only is used for troubleshooting replication as directed by an experienced replication Suppor T Professional.

4, read commands

Returns the commands for transactions marked for replication.

[] Maxtrans

sp_replcmds is used by the Log reader process in transactional replication.

Replication treats the first client that runs sp_replcmds within a given database as the log reader.

This procedure can generate commands for owner-qualified tables or not qualify the table name (the default). Adding qualified table names allows replication of data from tables owned by a specific user in one database to tables own Ed by the same user in another database.

Clients attempt to run sp_replcmds within the same database receive error 18752 until the first client Discon Nects. After the first client disconnects, another client can run sp_replcmds, and becomes the new log reader.

A warning message number 18759 is added to both the Microsoft SQL Server error log and the Microsoft Windows application L og if sp_replcmds is unable to replicate a text command because the text pointer were not retrieved in the same TR Ansaction.

5, mark transaction as distributed

sp_repldone was used by the log Reader process to track which transactions has been distributed. updates The record that identifies the last distributed transaction of the S Erver.

 sp_repldone [  @xactid =  ]   Xactid,  [  @xact_seqno =    xact_seqno  [    Numtrans]  [ , [@time =  ]   time  [ , [@reset =    reset]  

With sp_repldone, can manually tell the server that a transaction have been replicated (sent to the Distributo R). It also allows the transaction marked as the next one awaiting replication. You can move forward or backward in the list of replicated transactions. (All transactions less than or equal to that transaction is marked as distributed.) the required parameters Xactid and xact_seqno can be obtained by using Sp_repltrans or sp_replcmds.
Caution: If you execute sp_repldone manually, you can invalidate the order and consistency of delivered transactions. sp_repldone should only is used for troubleshooting replication as directed by an experienced replication Suppo RT professional.

Reference doc:

SP_REPLSHOWCMDS (Transact-SQL)

Replication Stored PROCEDURES (Transact-SQL)

View transaction and Commands that are not read by Log Reader in Publisher DB