There are already a number of data sources you can reference when investigating replication issues. One data source on my wish list was to have a one-stop shop in Extended Events similar to the AlwaysOn Health Session.
It turns out that SQL Server 2012 does have a few new replication related events, but don’t get too excited… Books Online manages our expectations in the following text (underlined text added by me):
“Replication supports Extended Events, however, this feature is for internal use only at this time. Replication extended events were added to help customer support engineers collect information to troubleshoot replication problems. The information collected is not useful for replication performance tuning or monitoring.”
There was a dash of hope in the “at this time” qualifier, but that was the only good news I could get from this. But even then, I wanted to be absolutely sure that there were truly no hidden diagnostic data sources that could be leveraged for replication issues.
I found the following potentially promising events in sys.dm_xe_objects:
repl_event
logreader_process_text_info
logreader_process_text_ptr
logreader_process_filestream_info
logreader_add_compensation_range
logreader_add_eor
logreader_apply_filter_proc
The first one was what I decided to investigate today was “repl_event”. It is described in sys.dm_xe_objects as “Occurs when sp_repl_generateevent is called. this event is an internal repl event for tracing repl stored procedures. The data that is returned from user_event includes the event_id that was specified in the call to sp_repl_generateevent. This can be a value between x and y.“ :
CREATE EVENT SESSION [repl_event] ON SERVER
ADD EVENT sqlserver.repl_event
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON);
GO
I started this event session and set up transactional replication (configured the distributor, simple publication, one subscriber). No events were triggered when I did this.
I then investigated the sp_repl_generateevent procedure itself. Looking at the definition (or trying), returned the message “replgenerateevent extended procedure”.
Which objects reference sp_repl_generateevent? All I could find was sys.sp_MSaddmergetriggers_internal. The referencing section of the system stored procedure was as follows:
select @command2 = ‘
— update any ppm row that already exist with this gen
update ppm set ppm.generation = case when @is_mergeagent = 1 then 0 else @newgen end
from ‘ + @quoted_past_mappings_viewname + ‘ ppm with (rowlock) inner join deleted v
on ppm.tablenick =@tablenick and ppm.rowguid = v.’ + @quoted_rgcol + ‘
— insert the past partition mapping into gen 0 if this is the merge agent
insert into ‘ + @quoted_past_mappings_viewname + ‘ with (rowlock) (publication_number, tablenick, rowguid, partition_id, generation,reason)
select distinct ‘ + convert(nvarchar(100), @publication_number) + ‘, @tablenick, v.’ + @quoted_rgcol + ‘, v.partition_id, case when @is_mergeagent = 1 then 0 else @newgen end, 1
from ( ‘ + @partition_deleted_view_rule + ‘ ) as v
if (@@ROWCOUNT <= 0)
begin
select @xe_message = CAST(”replica_id: ” + convert(nvarchar(100), @replnick) + ”, article_id: ” + convert(nvarchar(100), @tablenick) + ”, rowguid: ” + case when @article_rows_deleted = 1 then convert(nvarchar(100), @rowguid) else N”0” end + ”, generation: ” + case when @is_mergeagent = 1 then N”0” else convert(nvarchar(100), @newgen) end + ”, Reason: -1” AS varbinary(1000));
exec master..sp_repl_generateevent 1, N”Event : ppm_insert”, @xe_message
end
‘
Since I was on a test SQL Server instance, I thought I would test out a direct call to this procedure just to indeed see that this procedure was hooked to the repl_event:
DECLARE @xe_message varbinary(1000) =
CAST('Event payload' AS varbinary(1000));
EXEC sp_repl_generateevent 1, N'Event: Am I captured?', @xe_message;
Sure enough – the repl_event was fired:
Whether repl_event gets leveraged in the future, we’ll see. If Microsoft implements this in the future, my wish list would include the following events (and knowing that we can capture these in other areas – but again I’m interested in a consolidated session):
- Replication configuration events (creation, dropping, changes)
- Agent statistics, like periodic reader/writer thread latency statistics
- Subscription expirations
- Conflicts
- Failed replication jobs and retries
- Data sync warnings
- Interoperability events (for example – database mirroring failovers of the publication database)
I may investigate the logreader_* related events at some point, but based on the naming and descriptions of these events I don’t see significant use cases at this time.
If you run across any other replication related events that you find useful, please share your comments on this post. Thanks!
