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
ChrisB MCDBA
MSSQLConsulting.com
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
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
Cheers,
Paul Ibison
--
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...
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...
Thanks Paul, but the servers are SQL 2000.
Thanks Chris. I'll try that today.
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.
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!