Rahman Agoro, the frontline SQL DBA

Just another WordPress.com weblog

Archive for March, 2011

Monitoring SQL replication using the tracer tokens TSQL dynamically

Posted by rahmanagoro on March 17, 2011

I recently wanted to monitor the latency between publisher/subscriber within a SQL transactional replication setup. This can be done quite easily within the GUI, but I wanted to automate the process and make it run as a frequent check on the database.

I have written a script and stored procedure as to how I managed to achieve this.



Author:  Rahman Agoro
Description This stored procedure will insert tracer tokens and monitor SQL replication
Date  14/02/2011
USE    declare @alarm int exec spCheckReplicationLatency '00:02:00', '%SUBSCRIBER%',@alarm output select @alarm */
CREATE procedure [dbo].[spCheckReplicationLatency] (@DelayLength char(8)= '00:00:00', @Exclusion varchar(100),@Alarm int output )

DECLARE @LoopStart int
DECLARE @LoopEnd int
DECLARE @Database varchar(50)
DECLARE @Publication varchar(100)
DECLARE @ReplicationIssue int
DECLARE @Error int
DECLARE @Seperator char(10)
DECLARE @Message varchar(1000)
DECLARE @operatorEmail varchar(100)

SET @Seperator = char(9)
--Check to see if the distribution database is present.
IF DB_ID('distribution') is null
RAISERROR ('distribution database not present',16,1)
IF OBJECT_ID('tempdb..##replicationpublicationlatency') is not null
drop table ##replicationpublicationlatency

create table ##replicationpublicationlatency
 pubID   int identity(1,1),
 databasename varchar(50),
 publication varchar(100)

insert into ##replicationpublicationlatency (databasename,publication)
select publisher_db,publication
from distribution..MSpublications

SET @LoopStart = 1
SET @LoopEnd = (SELECT COUNT(*) from ##replicationpublicationlatency)

WHILE (@LoopStart <= @LoopEnd)

 select @Database = databasename, @Publication = publication from ##replicationpublicationlatency
 WHERE pubID = @LoopStart

 SET @SQL = ''
 SET @SQL = @SQL + CHAR(10) + 'USE ' + @Database + '  exec sp_posttracertoken ' + '''' + @Publication + '''' + char(10) + char(13)

 SET @LoopStart = @LoopStart + 1
 exec master..sp_executesql @SQL
DECLARE @ReturnInfo varchar(255)
IF ISDATE('2000-01-01 ' + @DelayLength + '.000') = 0
        SELECT @ReturnInfo = 'Invalid time ' + @DelayLength
        + ',hh:mm:ss, submitted.';
        -- This PRINT statement is for testing, not use in production.
        PRINT @ReturnInfo
        GOTO ErrorHandler
    WAITFOR DELAY @DelayLength

IF OBJECT_ID('tempdb..##TokenHistory') IS NOT NULL
DROP TABLE ##TokenHistory

;with CTE_Latest_Tracer_Token_History(publication,publication_id,agent_id,agent_name,publisher_commit,distributor_commit,subscriber_commit,publisher_distributor,distributor_subscriber,Total_latency, RANKING)
SELECT a.publication,publication_id, agent_id, a.name,
       t.publisher_commit, t.distributor_commit, h.subscriber_commit,datediff(ss,t.publisher_commit,t.distributor_commit )publisher_distributor,
       datediff(ss,t.distributor_commit, h.subscriber_commit)distributor_subscriber,datediff(ss,t.publisher_commit,t.distributor_commit) + datediff(ss,t.distributor_commit, h.subscriber_commit) Total_latency,
       ROW_NUMBER() OVER (PARTITION BY publication_id, agent_id ,a.name ORDER BY t.publisher_commit DESC) AS RANKING

FROM distribution..MStracer_tokens t
JOIN distribution..MStracer_history h
ON t.tracer_id = h.parent_tracer_id
  JOIN distribution..MSdistribution_agents a
on a.id = agent_id
and a.name not like @Exclusion
select *
into ##TokenHistory
from CTE_Latest_Tracer_Token_History
where RANKING in (1)
order by publication

SET @ReplicationIssue = (select count(*) from ##TokenHistory where Total_latency is null)
SET @Message = 'Replication Latency Report ' + @@SERVERNAME
 if ISNULL(@ReplicationIssue,0) > 0         
    Set @Alarm = 1        
    print cast(@ReplicationIssue as nvarchar)+N' replication agents has errors'         
 select @operatorEmail = email_address from msdb..sysoperators where name = 'DBA'       

     EXEC msdb.dbo.sp_send_dbmail @recipients=@operatorEmail, 
     @subject = @Message, 
     @body = 'Please see attached replication latency report', 
     @body_format = 'HTML', 
     @query ='SELECT agent_name,publisher_distributor,distributor_subscriber,Total_latency FROM ##TokenHistory  ' , 
     @query_attachment_filename = 'Replication_Latency_Report.txt', 
     @attach_query_result_as_file = 'TRUE',   
     @query_result_separator = @Seperator

--WE have an issue if the issue flag  = 1
 Insert into tblReplicationLatencyLog (AgentName,LogDate,Issue)
 SELECT agent_name,GETDATE(),1
 from ##TokenHistory where Total_latency is NULL

 Set @Error = @@Error if @Error <> 0 GoTo ErrorHandler    

 Insert into tblReplicationLatencyLog (AgentName,LogDate,Issue)
 SELECT 'No Issue',GETDATE(),0

    Set @Alarm = 0         

IF  @Error <> 0 
 Print 'Error Occured !' 


In this example, I wanted to log cases where there had been replication latency issues. Calling the stored procedure also gives an option to specify a wait for period, this is because when you insert a tracer token, you need to allow for some time for the token to get onto the subscribers and report back to the publisher. This is an example of how the stored procedure is actually used.


declare @alarm int exec spCheckReplicationLatency '00:02:00', '%SUBSCRIBER%',@alarm output select @alarm 

The reason why I had the subscriber to exclude was because in my case, there was a particular subscriber across the atlantic which was like a DR database server and according to SLA’s, its allowed a few minutes lag behind, and I didnt want to raise alarms all the time due to this particular server.

Posted in SQL 2008, SQL replication, Tips | Leave a Comment »

Troubleshooting using default trace information

Posted by rahmanagoro on March 11, 2011

The default trace is built into SQL server, its one that often tells you operations that are happening on the database server which is often not provided within the SQL error logs.

I regularly query the default trace to pick up issues that might be arising on the database server, the scripts that I use to query the default trace can be found below. The default trace also allows me to create policies using policy based management within SQL 2008, we once had a deployment using Visual studio and for those who have used visual studio, it creatred databases as a project and this is used in storing database schema, database deployment, integration with SVN etc. At the end of the day, the repository was not upto date and Auto skrink was turned on with visual studio, I found this had been turned on with visual studio. This was revealed within the default trace and I was able to create a policy to check for this across the database estate.

To get the default trace file currently in use, run the query below.

FROM fn_trace_getinfo(default);

To group the various events to see what has been happening on the database server, you can run the query below.

--Auditing various event types in the trace file.

trc_evnt.name, trc_evnt.category_id
,count(*) as occurence
FROM fn_trace_gettable('y:\databases\log_5170.trc', NULL) AS dflt_trc
INNER JOIN sys.trace_events AS trc_evnt
ON dflt_trc.EventClass = trc_evnt.trace_event_id
group by trc_evnt.name, trc_evnt.category_id
order by trc_evnt.name

You can change the default trace file to query the last five default traces, so for example y:\databases\log_5170.trc is the current trace, to check the one before this simply decrement the number by one, so run the query y:\databases\log_5169.trc.

I get information similar to the one below

Audit DBCC Event    8    9006
Audit Server Alter Trace Event    8    2
Hash Warning    3    2
Object:Created    5    644
Object:Deleted    5    201
Sort Warnings    3    1

I have used this method to troubleshoot various issues on the database such as the following:

missing statistics: I subesequently created and index or statistics on the column.

transaction logs growing every night: I changed the growth settings and expanded the size so as to remove the need for the growth.

Posted in Management, Scripts, SQL 2008, SQL Administration, Tips, Uncategorized | Leave a Comment »