Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Determining Tranasctional Replication Latency thru TSQL

16 views
Skip to first unread message

Jason Wilson

unread,
Nov 20, 2007, 11:56:56 AM11/20/07
to
I have an ASP.NET applicaiton that relies heavily on a replicated
database. I would like to report within the application but I've been
unable to find a way to determin replicaiton latency.

Does anyone know of a way to programmically determine the time
difference between the time a transaction is wriitten at the publisher
and at the subscriber?

Thanks,

Jason

Jason Wilson

unread,
Nov 21, 2007, 10:00:34 AM11/21/07
to
I realized my typing wasn't best in my original post. I am looking
for a way to determine latency for transactional replication.
Anybody?


Jason Wilson

unread,
Nov 21, 2007, 10:05:09 AM11/21/07
to
one last note: both servers are running SQL 2000

Chris

unread,
Nov 21, 2007, 3:18:01 PM11/21/07
to

I rely on my own method of dropping a dummy record in a replicated table
every 1 minute - record entry datetime. Then use getdate() default at
subscriber to record arrival. Then do a select datediff on subscriber.

ChrisB MCDBA
MSSQLConsulting.com

Jason Wilson

unread,
Nov 21, 2007, 4:31:04 PM11/21/07
to
Thanks Chris,

Let me make sure that I understand. You created a table on the
publisher that has 2 datetime columns. 1 defaults to GETDATE(). When
the table changes to the subscriber the field with the default doesn't
come with the record doesn't replicate so that when it arrives it gets
the datetime at the subscriber? Is the standard behavior for the
columns with defaults or do you filter the that columns in the
article? Or do did you alter the table at the subscriber?

Thanks,

Jason

Chris

unread,
Nov 21, 2007, 5:00:02 PM11/21/07
to

Exactly. Here is my actual setup -

The table at publisher has 2 columns:
id int identity,
pub_datetime datetime

The same table name at subscriber, but with extra column:
id int identity,
pub_datetime datetime,
subscr_datetime datetime default getdate()

My insert stmt at pub is in a job that fires every 1 min:
insert into repl_latency
select getdate()

When it arrives at subscriber, the default on subscr_datetime column will
populate w/ arrival time.

you could have same columns at both - but w/ default constraint only at
subscriber.

Caveat - clock time on servers must be in sync to get latency calculated
accurately - will be in seconds if you are on lan.

ChrisB MCDBA
MSSQLConsulting.com

Paul Ibison

unread,
Nov 21, 2007, 6:02:52 PM11/21/07
to
How about using sp_posttracertoken and sp_helptracertokenhistory ?

Cheers,

Paul Ibison


Hilary Cotter

unread,
Nov 22, 2007, 7:40:49 AM11/22/07
to
ur back!!!

--
http://www.zetainteractive.com - Shift Happens!

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Ibison" <Paul....@Pygmalion.Com> wrote in message
news:%23R8UQLJ...@TK2MSFTNGP05.phx.gbl...

Hilary Cotter

unread,
Nov 22, 2007, 7:40:28 AM11/22/07
to
do this
declare @stuff varbinary(16)
select @stuff =transaction_timestamp from
subDB.dbo.MSreplication_subscriptions
select publisher_database_id,xact_id,xact_seqno, entry_time From
distribution.dbo.msrepl_transactions where xact_seqno =@stuff

This will give you a list of rows queued in the distributor making their way
to the subscriber. Do a top 20 or something and order by entry time to
figure out latency.

Note that you may have to configure your subscriber for data access.

--
http://www.zetainteractive.com - Shift Happens!

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

"Jason Wilson" <wil...@ausrad.com> wrote in message
news:e2365fcd-d6c2-4bd1...@c30g2000hsa.googlegroups.com...

Paul Ibison

unread,
Nov 23, 2007, 4:26:14 PM11/23/07
to
If only! Too much work and too little time :)


Jason Wilson

unread,
Nov 26, 2007, 8:54:41 AM11/26/07
to

Thanks Paul, but the servers are SQL 2000.

Jason Wilson

unread,
Nov 26, 2007, 8:55:19 AM11/26/07
to
On Nov 21, 4:00 pm, Chris <Ch...@discussions.microsoft.com> wrote:
> Exactly. Here is my actual setup -
>
> The table at publisher has 2 columns:
> id int identity,
> pub_datetime datetime
>
> The same table name at subscriber, but with extra column:
> id int identity,
> pub_datetime datetime,
> subscr_datetime datetime default getdate()
>
> My insert stmt at pub is in a job that fires every 1 min:
> insert into repl_latency
> select getdate()
>
> When it arrives at subscriber, the default on subscr_datetime column will
> populate w/ arrival time.
>
> you could have same columns at both - but w/ default constraint only at
> subscriber.
>
> Caveat - clock time on servers must be in sync to get latency calculated
> accurately - will be in seconds if you are on lan.
>
> ChrisB MCDBA
> MSSQLConsulting.com

Thanks Chris. I'll try that today.

Jason Wilson

unread,
Nov 26, 2007, 8:59:49 AM11/26/07
to
On Nov 22, 6:40 am, "Hilary Cotter" <hilary.cot...@gmail.com> wrote:
> do this
> declare @stuff varbinary(16)
> select @stuff =transaction_timestamp from
> subDB.dbo.MSreplication_subscriptions
> select publisher_database_id,xact_id,xact_seqno, entry_time From
> distribution.dbo.msrepl_transactions where xact_seqno =@stuff
>
> This will give you a list of rows queued in the distributor making their way
> to the subscriber. Do a top 20 or something and order by entry time to
> figure out latency.
>
> Note that you may have to configure your subscriber for data access.
>
> --http://www.zetainteractive.com- Shift Happens!
>
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
>
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com"Jason Wilson" <wils...@ausrad.com> wrote in message

>
> news:e2365fcd-d6c2-4bd1...@c30g2000hsa.googlegroups.com...
>
>
>
> >I have an ASP.NET applicaiton that relies heavily on a replicated
> > database. I would like to report within the application but I've been
> > unable to find a way to determin replicaiton latency.
>
> > Does anyone know of a way to programmically determine the time
> > difference between the time a transaction is wriitten at the publisher
> > and at the subscriber?
>
> > Thanks,
>
> > Jason- Hide quoted text -
>
> - Show quoted text -

Saw that when I started investigating the distribution architecture.
problem is that only gives me the delay from the time it arrives at
the distribution database, not the time difference between committing
at the publisher and subscriber.

Thanks anyway Hillary -- your input is always great.

Hilary Cotter

unread,
Nov 26, 2007, 2:19:27 PM11/26/07
to
It is extremely rare for the log reader to be the bottleneck or for it
not to be caught up.

The only time when it is not caught up is when you do a large batch
update on the publisher.


On Nov 26, 8:59 am, Jason Wilson <wils...@ausrad.com> wrote:
> On Nov 22, 6:40 am, "Hilary Cotter" <hilary.cot...@gmail.com> wrote:
>
>
>
> > do this
> > declare @stuff varbinary(16)
> > select @stuff =transaction_timestamp from
> > subDB.dbo.MSreplication_subscriptions
> > select publisher_database_id,xact_id,xact_seqno, entry_time From
> > distribution.dbo.msrepl_transactions where xact_seqno =@stuff
>
> > This will give you a list of rows queued in the distributor making their way
> > to the subscriber. Do a top 20 or something and order by entry time to
> > figure out latency.
>
> > Note that you may have to configure your subscriber for data access.
>

> > --http://www.zetainteractive.com-Shift Happens!

0 new messages