replication error? (somewhat urgent)

Sorry, this is a long winded post I know. But all help is appreciated as me
and my managers need to explain all this to our CEO on Monday.
sql2k sp3
Transactional, non immediate Updating, continuous, no Transformations
Replication.
This last Tuesday morning a report against was run against the same
Replicated db that it is every morning. It is run automatically at the same
time Monday thru Friday. With the exception of errors on my end, Replication
has been purring along just fine with absolutely no problems for the 3
months its been up and running. However, this last Tuesday the report was
run and the numbers generated didn't match the numbers the end user was
expecting to see. They ran it again 10 minutes later and the numbers were
more to they're liking. As a group, our IT dept has ruled out pretty much
everything we can think of. So our only theory is that Replication was
having problems. We have narrowed down all possibilities to 1 table. This
table is imported into the Publisher from a file using BCP every morning
before this report is run. This table has a column with a GETDATE() function
on it so we know what time the data made it into the Publisher for sure. The
time it says is 45 minutes before the report was run. So the theory is that:
(finally)
There was about a 50 minute lag in Replication.
That the first time the report was run, not all of the data was in the
Subscriber.
That the second time it was run (10 minutes later) all of the data made it
in and life was good.
There is nothing supporting anything like this in the error log. Nor is
there anything supporting this theory in either the Log/ Distribution Agents
history. (although on Thursday I did stop/ restart both Agents for unrelated
reasons and I dont know if that clears them.)
So, does anyone have any way I can prove/ disprove these theories? It seems
a bit far stretched to me since Ive got a fair amount of Replication
experience under my belt and the only time I got anything like a "lag" was
along the lines of "cant exec sp_repldone" and then it blew up totally
untill fixed manually.
All ideas are greatly appreciated.
TIA, ChrisR
You might want to query msrepl_errors to find out if there was any errors in
this time period, like a general network error, or query the
msdistribution_history table to see how many commands were replicated in
this time period. Unfortunately these tables are pruned by default every 2
days so you might not see anything there.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:O6qegci2EHA.3244@.TK2MSFTNGP11.phx.gbl...
> Sorry, this is a long winded post I know. But all help is appreciated as
> me
> and my managers need to explain all this to our CEO on Monday.
> sql2k sp3
> Transactional, non immediate Updating, continuous, no Transformations
> Replication.
> This last Tuesday morning a report against was run against the same
> Replicated db that it is every morning. It is run automatically at the
> same
> time Monday thru Friday. With the exception of errors on my end,
> Replication
> has been purring along just fine with absolutely no problems for the 3
> months its been up and running. However, this last Tuesday the report was
> run and the numbers generated didn't match the numbers the end user was
> expecting to see. They ran it again 10 minutes later and the numbers were
> more to they're liking. As a group, our IT dept has ruled out pretty much
> everything we can think of. So our only theory is that Replication was
> having problems. We have narrowed down all possibilities to 1 table. This
> table is imported into the Publisher from a file using BCP every morning
> before this report is run. This table has a column with a GETDATE()
> function
> on it so we know what time the data made it into the Publisher for sure.
> The
> time it says is 45 minutes before the report was run. So the theory is
> that:
> (finally)
> There was about a 50 minute lag in Replication.
> That the first time the report was run, not all of the data was in the
> Subscriber.
> That the second time it was run (10 minutes later) all of the data made it
> in and life was good.
> There is nothing supporting anything like this in the error log. Nor is
> there anything supporting this theory in either the Log/ Distribution
> Agents
> history. (although on Thursday I did stop/ restart both Agents for
> unrelated
> reasons and I dont know if that clears them.)
> So, does anyone have any way I can prove/ disprove these theories? It
> seems
> a bit far stretched to me since Ive got a fair amount of Replication
> experience under my belt and the only time I got anything like a "lag" was
> along the lines of "cant exec sp_repldone" and then it blew up totally
> untill fixed manually.
> All ideas are greatly appreciated.
> TIA, ChrisR
>
|||Hillary do you know if theres adjust these settings to keep the history for
a longer time period? Im guessing the proc in the "dist cleanup" job?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:#Rigw1i2EHA.3468@.TK2MSFTNGP14.phx.gbl...
> You might want to query msrepl_errors to find out if there was any errors
in[vbcol=seagreen]
> this time period, like a general network error, or query the
> msdistribution_history table to see how many commands were replicated in
> this time period. Unfortunately these tables are pruned by default every 2
> days so you might not see anything there.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "ChrisR" <ChrisR@.noEmail.com> wrote in message
> news:O6qegci2EHA.3244@.TK2MSFTNGP11.phx.gbl...
as[vbcol=seagreen]
was[vbcol=seagreen]
were[vbcol=seagreen]
much[vbcol=seagreen]
This[vbcol=seagreen]
it[vbcol=seagreen]
was
>
|||Right click on Replication Monitor, select distributor properties. I believe
you set it there.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ChrisR" <bla@.noemail.com> wrote in message
news:e0vQb3j2EHA.304@.TK2MSFTNGP11.phx.gbl...
> Hillary do you know if theres adjust these settings to keep the history
> for
> a longer time period? Im guessing the proc in the "dist cleanup" job?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:#Rigw1i2EHA.3468@.TK2MSFTNGP14.phx.gbl...
> in
> as
> was
> were
> much
> This
> it
> was
>