Replication Threads Overview:
LogReader Reader thread is reading the Transaction Log via stored procedure sp_replcmds, a wrapper for xp_replcmds. It scans the transaction log for transactions marked for replication, skipping not replicated transactions.
ex. In SQL Server Profiler: exec sp_replcmds 500,0,-1,0x,5000,0,500000
LogReader Writer thread writes queued transaction from the Reader thread into the Distribution database using sp_MSadd_replcmds.
Distribution Reader thread executes sp_MSget_repl_commands query to retrieve pending commands from the Distribution database and storing in an internal queue.
ex. exec sp_MSget_repl_commands 9,0x002597880005AE13000200000000,0,10000000
Distribution Writer thread writing queue commands to the Subscriber via parameterized stored procedures prefixed with sp_MSupd..., sp_MSins..., sp_MSdel... to apply individual row changes to each article at the subscriber.
Log Reader and Distributor are also executing a “history” thread writing summary data to MSlogreader_history and MSdistribution_history system tables of the distribution database.
ex. exec sp_MSadd_distribution_history 9,4,N'No replicated transactions are available.',0x002597880005AE13000200000000,19565,27244,0.00,0,1,0x0000000000000000000000000000,0,1,1
*Performance Note: Use SQL Profiler to check the duration of those internal replication stored procedures.
textdata like %sp_MS%
textdata like %sp_replcmds%
Distribution DB - Transactions & Commands:
Total Transactions in DISTRO DB
- Does not indicate Transactions not sent to Subscribers. Run Distribution Cleanup Job to get more accurate dataset.
- SQL Job: Distribution clean up: distribution (Removes replicated transactions from the distribution database.)
Select p.id, p.publisher_db, count(*) as 'Total Transactions'
from distribution.dbo.MSpublisher_databases p
inner join distribution.dbo.MSrepl_transactions t
on p.id = t.publisher_database_id
group by p.id, p.publisher_db
Total Transactions in DISTRO DB
- Does not indicate Commands not sent to Subscribers. Run Distribution Cleanup Job to get more accurate dataset.
- SQL Job: Distribution clean up: distribution (Removes replicated transactions from the distribution database.)
Select p.id, p.publisher_db, count(*) as 'Total Commands'
from distribution.dbo.MSpublisher_databases p
inner join distribution.dbo.MSrepl_commands c
on p.id = c.publisher_database_id
group by p.id, p.publisher_db
Execute in Publisher DB:
exec publisherdbname.dbo.sp_repltrans
View transactions currently not distributed (transactions remaining in the Transaction Log that have NOT been
sent to the Distributor). Result set displays the log sequence numbers of the first and last records for each transaction.
sp_repltrans is similar to sp_replcmds but does not return the commands for the transactions.
Note:
The sp_replcmds procedure should be run only to troubleshoot problems with replication. Replication treats the first client that runs sp_replcmds within a given database as the log reader. Clients who attempt to run sp_replcmds within the same database receive error 18752 until the first client disconnects. After the first client disconnects, another client can run sp_replcmds, and becomes the new log reader
Execute in Distribution DB:
exec distribution.dbo.sp_browsereplcmds @publisher_database_id = 3
exec distribution.dbo.sp_browsereplcmds @publisher_database_id = 3, @article_id = 12
--Get @publisher_database_id here: select ID, * from [dbo].[MSpublisher_databases]
--Get Articles here: Select * from distribution.dbo.MSarticles order by publisher_db asc, article asc
Returns a result set in a readable version of the replicated commands stored in the distribution database - is used as a
diagnostic tool. This stored procedure is executed at the Distributor on the distribution database. View commands in the
Distribution DB. Long commands can be split across several rows in the result sets. The result set here is dependent on the Distribution
Properties config for Transaction and Historical Retention periods. (View these properties in SSMS..Replication..right click..Distributor Properties)
SQl Job "Distribution clean up: distribution" - Removes replicated transactions from the distribution database.
You can execute the above SQL Job and notice changes here:
exec distribution.dbo.sp_browsereplcmds @publisher_database_id = 3
select count(*) from distribution.dbo.MSrepl_commands
sp_browsereplcmds results set in the "Command" column:
Example call: {CALL [sp_MSins_dboSEARCH_SALES] (16757096,2016-05-22 13:00:00.000)}
Thus 1 Command in Distribution DB is the same an Insert Into dbo.Tbl (command) (same for INS's & DEL's)
Publisher DB to Distribution DB to Subscriber via Command Calls using Replication Stored Procedures is the data flow process.
Below is that actual Transactional Replication Stored Procedure located in the Subscriber DB:
ALTER procedure [dbo].[sp_MSins_dboSEARCH_SALES]
@c1 int,
@c2 datetime
as
begin
insert into [dbo].[SEARCH_SALES](
[ListID],
[SaleEndDateTime]
) values (
@c1,
@c2 )
end
Execute in Distribution DB (with sub-query to Subscriber DB to get latest applied transaction):
select * from Distribution.dbo.MSrepl_commands Where xact_seqno >
(select transaction_timestamp from AZUREMSSQL.AzureMSSQL1.dbo.MSreplication_subscriptions)
View Transactions waiting to be replicated from Distribution DB to Subscription DB. Same as Undistributed Commmands seen in Replication Monitor.
AZUREMSSQL = Linked Server to the Subscription DB
xact_seqno > transaction_timestamp shows all transactions that have not been applied yet to the Subscriber DB
Note: dbo.MSreplication_subscriptions - contains 1 records for each Distribution Agent in the Subscriber. If more than one
Distribution Agent in the Subscriber DB then adjust the query accordingly.
Automated Solution to Monitor Replicated Transactions from Distribution DB to Subscription DB:
UPDATE: New version here
#1. Create Table:
CREATE TABLE [dbo].[LOG_TRANSACTIONAL_REPLICATION_TRX_DISTRO_TO_SUB](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[Instance] [varchar](150) NULL,
[TRXDistroToSub] [int] NULL,
[InsertedDate] [datetime] NULL CONSTRAINT [DF_LOG_TRANSACTIONAL_REPLICATION_TRX_DISTRO_TO_SUB_InsertedDate] DEFAULT (getdate()),
CONSTRAINT [PK_LOG_TRANSACTIONAL_REPLICATION_TRX_DISTRO_TO_SUB] PRIMARY KEY CLUSTERED
(
[LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
#2. Create Stored Procedures:
CREATE Procedure [dbo].[spa_Log_Transactional_Replication_TRX_Distro_to_Sub]
as
set nocount on
/*
Select * from dbo.Log_Transactional_Replication_TRX_Distro_to_Sub
*/
Create Table #TMP(Instance varchar(150), TRXDistroToSub int)
Insert into #TMP
Exec Master.dbo.sp_Transactional_Replication_TRX_Distro_to_Sub
If Exists (Select 1 from #TMP where TRXDistroToSub <> 0)
Begin
Insert into dbo.LOG_TRANSACTIONAL_REPLICATION_TRX_DISTRO_TO_SUB (Instance, TRXDistroToSub)
Select * from #TMP
End
Drop Table #TMP
GO
Create Procedure [dbo].[sp_Transactional_Replication_TRX_Distro_to_Sub]
as
--dbo.MSreplication_subscriptions - located in Subscriber DB - contains 1 record for each Distrubition Agent in the Subscriber
--In this case with DB Subscriber there is only one Distribution Agent so it only returns 1 record. If more thatn 1 Distribution Agent
--in the Subscription DB then adjust the query accordingly.
select 'DB01' as 'Instance', count(*) as 'TRX - Disto to Sub' from distribution.dbo.MSrepl_commands with (nolock) where xact_seqno >
(select transaction_timestamp from [DB01-SubscriberDB].SubscriberDB.dbo.MSreplication_subscriptions with (nolock))
union
select 'DB02' as 'Instance', count(*) as 'TRX in Distro to Sub' from distribution.dbo.MSrepl_commands with (nolock) where xact_seqno >
(select transaction_timestamp from [DB02-SubscriberDB].SubscriberDB.dbo.MSreplication_subscriptions with (nolock))
union
select 'DB03' as 'Instance', count(*) as 'TRX in Distro to Sub' from distribution.dbo.MSrepl_commands with (nolock) where xact_seqno >
(select transaction_timestamp from [DB03-SubscriberDB].SubscriberDB.dbo.MSreplication_subscriptions with (nolock))
union
select 'DB04' as 'Instance', count(*) as 'TRX in Distro to Sub' from distribution.dbo.MSrepl_commands with (nolock) where xact_seqno >
(select transaction_timestamp from [DB04-SubscriberDB].SubscriberDB.dbo.MSreplication_subscriptions with (nolock))
union
select 'DB05' as 'Instance', count(*) as 'TRX in Distro to Sub' from distribution.dbo.MSrepl_commands with (nolock) where xact_seqno >
(select transaction_timestamp from [DB05-SubscriberDB].SubscriberDB.dbo.MSreplication_subscriptions with (nolock))
union
select 'DB06' as 'Instance', count(*) as 'TRX in Distro to Sub' from distribution.dbo.MSrepl_commands with (nolock) where xact_seqno >
(select transaction_timestamp from [DB06-SubscriberDB].SubscriberDB.dbo.MSreplication_subscriptions with (nolock))
#3. Create SQL Job to execute Stored Procedure:
Exec dbo.spa_Log_Transactional_Replication_TRX_Distro_to_Sub
Misc:
To view Log Space: (Large Transaction Log size can cause long read times by the Log Reader thread)
DBCC SQLPERF(LOGSPACE)
To view Total Records in the Log:
SELECT count(*) FROM ::fn_dblog(NULL, NULL)
To view Records marked for REPLICATION:
SELECT count(*) FROM ::fn_dblog(NULL, NULL) WHERE Description='REPLICATE'
To view the performance of internal replication stored procedures executing on Subscriber:
Open SQL Profiler, RPC:Completed, filter on the your replication login and textdata like %sp_MS%
To view all Replication Jobs:
SELECT Name, Description, Enabled from MSDB..sysjobs WHERE category_id > 10 and category_id < 20
To view Replication Agents/Threads output status:
Add this to the Replication Agent Log Reader SQL Job: -Output Q:\ReplOutput\repl_log_reader_output.txt -OutputVerboseLevel 2
(You can add this from the Job Activity Monitor)
(You will need to stop & start this Log Reader job for the output to be written OR wait 5 minutes for an update but this varies per MSSQL Build)
Replication Log Reader Agent
******************** STATISTICS SINCE AGENT STARTED ***************************
10-10-2015 15:06:53
Execution time (ms): 301125
Work time (ms): 362
Distribute Repl Cmds Time(ms): 94
Fetch time(ms): 156 --Log Reader Read time 156ms
Repldone time(ms): 47
Write time(ms): 32 --Log Reader Write time 32ms
Num Trans: 1 Num Trans/Sec: 2.762431
Num Cmds: 20 Num Cmds/Sec: 55.248619
*******************************************************************************
Organization of Transaction Log:
Log is divided into Virtual Log Files (VLFs)
VLF can be in one of 4 states:
1. Active
2. Inactive and not backed up (or not truncated) - Recoverable
3. Inactive and backed up or truncated - Recyclable
4. Unused
DBCC LOGINFO Returns One Row Per VLF
Status = 2 means you cannot use that VDF. Still active or still needs to be backed up.
Status = 0 means VLF is recyclable
Resources:
Transactional Replication
View and Modify Distributor and Publisher Properties
|
|
|
|
|
|