In my last blog post, I provided a script to view replication latency. Ian Kirk took the script and ran with it, adding centralized execution and permanent logging. I’ve tweaked it a little bit further and deployed to production. So far, so good.
Here’s the latest and greatest for those interested:
If Object_ID('dbo.dba_replicationMonitor') Is Null Begin Create Table dbo.dba_replicationMonitor ( monitor_id int Identity(1,1) Not Null , monitorDate smalldatetime Not Null , publicationName sysname Not Null , publicationDB sysname Not Null , iteration int Null , tracer_id int Null , distributor_latency int Null , subscriber varchar(1000) Null , subscriber_db varchar(1000) Null , subscriber_latency int Null , overall_latency int Null ); End; If ObjectProperty(Object_ID('dbo.dba_replicationLatencyMonitor_sp'), N'IsProcedure') = 1 Begin Drop Procedure dbo.dba_replicationLatencyMonitor_sp; Print 'Procedure dba_replicationLatencyMonitor_sp dropped'; End; Go Set Quoted_Identifier On Go Set ANSI_Nulls On Go Create Procedure dbo.dba_replicationLatencyMonitor_sp /* Declare Parameters */ @publicationToTest sysname = N'yourPublicationName' , @publicationDB sysname = N'yourPublicationDB' , @replicationDelay varchar(10) = N'00:00:30' , @iterations int = 5 , @iterationDelay varchar(10) = N'00:00:30' , @displayResults bit = 0 , @deleteTokens bit = 1 As /********************************************************************************* Name: dba_replicationLatencyMonitor_sp Author: Michelle F. Ufford Purpose: Retrieves the amount of replication latency in seconds Notes: Default settings will run 1 test every minute for 5 minutes. @publicationToTest = defaults to yourPublicationName publication @publicationDB = the database that is the source for the publication. The tracer procs are found in the publishing DB. @replicationDelay = how long to wait for the token to replicate; probably should not set to anything less than 10 (in seconds) @iterations = how many tokens you want to test @iterationDelay = how long to wait between sending test tokens (in seconds) @displayResults = print results to screen when complete @deleteTokens = whether you want to retain tokens when done Called by: DBA Date Initials Description ---------------------------------------------------------------------------- 2008-11-20 MFU Initial Release 2008-11-24 ILK Tweaked to allow for centralized execution Replaced temp table with permanent table. 2008-11-25 MFU More tweaking, added publication data to dba_replicationMonitor, fixed NULL latency data, moved dba_replicationMonitor creation out of proc ********************************************************************************* Exec dbo.dba_replicationLatencyMonitor_sp @publicationToTest = N'myTestPublication' , @publicationDB = N'sandbox_publisher' , @replicationDelay = N'00:00:05' , @iterations = 1 , @iterationDelay = N'00:00:05' , @displayResults = 1 , @deleteTokens = 1; *********************************************************************************/ Set NoCount On; Set XACT_Abort On; Begin /* Declare Variables */ Declare @currentIteration int , @tokenID bigint , @currentDateTime smalldatetime , @sqlStatement nvarchar(200) , @parmDefinition nvarchar(500); Declare @tokenResults Table ( iteration int Null , tracer_id int Null , distributor_latency int Null , subscriber varchar(1000) Null , subscriber_db varchar(1000) Null , subscriber_latency int Null , overall_latency int Null ); /* Initialize our variables */ Select @currentIteration = 0 , @currentDateTime = GetDate(); While @currentIteration < @iterations Begin /* Prepare the stored procedure execution string */ Set @sqlStatement = N'Execute ' + @publicationDB + N'.sys.sp_postTracerToken ' + N'@publication = @VARpublicationToTest , ' + N'@tracer_token_id = @VARtokenID OutPut;' /* Define the parameters used by the sp_ExecuteSQL later */ Set @parmDefinition = N'@VARpublicationToTest sysname, ' + N'@VARtokenID bigint OutPut'; /* Insert a new tracer token in the publication database */ Execute sp_executesql @sqlStatement , @parmDefinition , @VARpublicationToTest = @publicationToTest , @VARtokenID = @TokenID OutPut; /* Give a few seconds to allow the record to reach the subscriber */ WaitFor Delay @replicationDelay; /* Prepare our statement to retrieve tracer token data */ Select @sqlStatement = 'Execute ' + @publicationDB + '.sys.sp_helpTracerTokenHistory ' + N'@publication = @VARpublicationToTest , ' + N'@tracer_id = @VARtokenID' , @parmDefinition = N'@VARpublicationToTest sysname, ' + N'@VARtokenID bigint'; /* Store our results for retrieval later */ Insert Into @tokenResults ( distributor_latency , subscriber , subscriber_db , subscriber_latency , overall_latency ) Execute sp_executesql @sqlStatement , @parmDefinition , @VARpublicationToTest = @publicationToTest , @VARtokenID = @TokenID; /* Assign the iteration and token id to the results for easier investigation */ Update @tokenResults Set iteration = @currentIteration + 1 , tracer_id = @tokenID Where iteration Is Null; /* Wait for the specified time period before creating another token */ WaitFor Delay @iterationDelay; /* Avoid endless looping... :) */ Set @currentIteration = @currentIteration + 1; End; /* Display our results */ If @displayResults = 1 Begin Select iteration , tracer_id , IsNull(distributor_latency, 0) As 'distributor_latency' , subscriber , subscriber_db , IsNull(subscriber_latency, 0) As 'subscriber_latency' , IsNull(overall_latency, IsNull(distributor_latency, 0) + IsNull(subscriber_latency, 0)) As 'overall_latency' From @tokenResults; End; /* Store our results */ Insert Into dbo.dba_replicationMonitor ( monitorDate , publicationName , publicationDB , iteration , tracer_id , distributor_latency , subscriber , subscriber_db , subscriber_latency , overall_latency ) Select @currentDateTime , @publicationToTest , @publicationDB , iteration , tracer_id , IsNull(distributor_latency, 0) , subscriber , subscriber_db , IsNull(subscriber_latency, 0) , IsNull(overall_latency, IsNull(distributor_latency, 0) + IsNull(subscriber_latency, 0)) From @tokenResults; /* Delete the tracer tokens if requested */ If @deleteTokens = 1 Begin Select @sqlStatement = 'Execute ' + @publicationDB + '.sys.sp_deleteTracerTokenHistory ' + N'@publication = @VARpublicationToTest , ' + N'@cutoff_date = @VARcurrentDateTime' , @parmDefinition = N'@VARpublicationToTest sysname, ' + N'@VARcurrentDateTime datetime'; Execute sp_executesql @sqlStatement , @parmDefinition , @VARpublicationToTest = @publicationToTest , @VARcurrentDateTime = @currentDateTime; End; Set NoCount Off; Return 0; End Go Set Quoted_Identifier Off; Go Set ANSI_Nulls On; Go |
Note: All of my stored procedures have standardized error handling that I remove before posting to avoid confusion; you may want to implement your own error handling.
Pingback: Log Buffer #125: a Carnival of the Vanities for DBAs
“Michelle Ufford, the SQL Fool, offers an upgraded version of her T-SQL to implement a replication monitor.”
Log Buffer #125
Hi i have like 20 publicatins , so i could i achive that using this stored procedure? please can you shed light on it.