|
|
You're in the
sql server replication
group:msreplication_monitordata
sql server replication:
This seems to only have non null values for the merge agent and then I only see if with a value of null or 0, even when my merge agent is running. I think you should query the job status in msdb. use msdb select sysjobs.name,sysjobs.job_id,rundate, runtime ,case when run_status =1 then 'Started' when run_status =2 then 'Succeeded' when run_status =3 then 'In progress' when run_status =4 then 'Idle' when run_status =5 then 'Retrying' when run_status =6 then 'Failed' else 'unknown' end from sysjobs join sysjobsteps on sysjobsteps.job_id= sysjobs.job_id and sysjobsteps.command like 'sp_msadd_logreader%' join (select sysjobhistory.job_id,runtime=max(run_time),rundate=max(k.rundate) from sysjobhistory join (select sysjobhistory.job_id, rundate=max(run_date)from sysjobhistory group by job_id ) as k on k.job_id=sysjobhistory.job_id and k.rundate=sysjobhistory.run_date group by sysjobhistory.job_id) as l on l.job_id=sysjobs.job_id join sysjobhistory on sysjobhistory.job_id=sysjobs.job_id and sysjobhistory.Run_date=l.rundate and sysjobhistory.Run_time=l.runtime -- Hilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. 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 [quoted text, click to view] "stephanie" <stephanie@discussions.microsoft.com> wrote in message news:554D6F3B-E265-412A-BE8B-F98BFA919ABF@microsoft.com... > Hi all, > I want to create a agent job to monitor the log agent and distribution > agent > regularly and I found system table msreplication_monitordata. According > to > BOL, it contains 'isagentrunningnow' which indicates the agent status. > However, the field 'isagentrunningnow' is all 'NULL' in my server. Please > advise. Thanks in advance
Hi all,
I want to create a agent job to monitor the log agent and distribution agent regularly and I found system table msreplication_monitordata. According to BOL, it contains 'isagentrunningnow' which indicates the agent status. However, the field 'isagentrunningnow' is all 'NULL' in my server. Please
I have tried the query in test env. I found an log agent are shown in
different pattern Running Agent1: started, idle Running Agent2: idle Stopped agent1: Idle,Idle, In progress Stopped agent2: started, Idle,Idle, In progress Is there any standard pattern in the query which showing the job in running? Thanks in advance [quoted text, click to view] "Hilary Cotter" wrote:
> You're right Stephanie > > This seems to only have non null values for the merge agent and then I only > see if with a value of null or 0, even when my merge agent is running. I > think you should query the job status in msdb. > > use msdb > select sysjobs.name,sysjobs.job_id,rundate, runtime ,case when run_status =1 > then 'Started' > when run_status =2 then 'Succeeded' > when run_status =3 then 'In progress' > when run_status =4 then 'Idle' > when run_status =5 then 'Retrying' > when run_status =6 then 'Failed' > else 'unknown' end > from sysjobs join sysjobsteps > on sysjobsteps.job_id= sysjobs.job_id and sysjobsteps.command like > 'sp_msadd_logreader%' > join (select > sysjobhistory.job_id,runtime=max(run_time),rundate=max(k.rundate) from > sysjobhistory > join > (select sysjobhistory.job_id, rundate=max(run_date)from sysjobhistory > group by job_id ) as k > on k.job_id=sysjobhistory.job_id and k.rundate=sysjobhistory.run_date > > group by sysjobhistory.job_id) as l > on l.job_id=sysjobs.job_id > join sysjobhistory on sysjobhistory.job_id=sysjobs.job_id > and sysjobhistory.Run_date=l.rundate > and sysjobhistory.Run_time=l.runtime > > > -- > Hilary Cotter > Director of Text Mining and Database Strategy > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. > > This posting is my own and doesn't necessarily represent RelevantNoise's > positions, strategies or opinions. > > 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 > > > > "stephanie" <stephanie@discussions.microsoft.com> wrote in message > news:554D6F3B-E265-412A-BE8B-F98BFA919ABF@microsoft.com... > > Hi all, > > I want to create a agent job to monitor the log agent and distribution > > agent > > regularly and I found system table msreplication_monitordata. According > > to > > BOL, it contains 'isagentrunningnow' which indicates the agent status. > > However, the field 'isagentrunningnow' is all 'NULL' in my server. Please > > advise. Thanks in advance > >
How to show the agent was stopped??
I found the stopped agents have status Idle,Idle, In progress started, Idle,Idle, In progress [quoted text, click to view] "Hilary Cotter" wrote:
> They all mean the log reader agent is running, starting means its starting, > in progress means it is reading transactions in the log, idle means there is > nothing in the log reader to read. > > -- > Hilary Cotter > Director of Text Mining and Database Strategy > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. > > This posting is my own and doesn't necessarily represent RelevantNoise's > positions, strategies or opinions. > > 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 > > > > "stephanie" <stephanie@discussions.microsoft.com> wrote in message > news:D0195B13-A1ED-47C8-833B-ACD39C4CF241@microsoft.com... > >I have tried the query in test env. I found an log agent are shown in > > different pattern > > Running Agent1: started, idle > > Running Agent2: idle > > Stopped agent1: Idle,Idle, In progress > > Stopped agent2: started, Idle,Idle, In progress > > > > > > Is there any standard pattern in the query which showing the job in > > running? > > Thanks in advance > > > > "Hilary Cotter" wrote: > > > >> You're right Stephanie > >> > >> This seems to only have non null values for the merge agent and then I > >> only > >> see if with a value of null or 0, even when my merge agent is running. I > >> think you should query the job status in msdb. > >> > >> use msdb > >> select sysjobs.name,sysjobs.job_id,rundate, runtime ,case when run_status > >> =1 > >> then 'Started' > >> when run_status =2 then 'Succeeded' > >> when run_status =3 then 'In progress' > >> when run_status =4 then 'Idle' > >> when run_status =5 then 'Retrying' > >> when run_status =6 then 'Failed' > >> else 'unknown' end > >> from sysjobs join sysjobsteps > >> on sysjobsteps.job_id= sysjobs.job_id and sysjobsteps.command like > >> 'sp_msadd_logreader%' > >> join (select > >> sysjobhistory.job_id,runtime=max(run_time),rundate=max(k.rundate) from > >> sysjobhistory > >> join > >> (select sysjobhistory.job_id, rundate=max(run_date)from sysjobhistory > >> group by job_id ) as k > >> on k.job_id=sysjobhistory.job_id and k.rundate=sysjobhistory.run_date > >> > >> group by sysjobhistory.job_id) as l > >> on l.job_id=sysjobs.job_id > >> join sysjobhistory on sysjobhistory.job_id=sysjobs.job_id > >> and sysjobhistory.Run_date=l.rundate > >> and sysjobhistory.Run_time=l.runtime > >> > >> > >> -- > >> Hilary Cotter > >> Director of Text Mining and Database Strategy > >> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. > >> > >> This posting is my own and doesn't necessarily represent RelevantNoise's > >> positions, strategies or opinions. > >> > >> 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 > >> > >> > >> > >> "stephanie" <stephanie@discussions.microsoft.com> wrote in message > >> news:554D6F3B-E265-412A-BE8B-F98BFA919ABF@microsoft.com... > >> > Hi all, > >> > I want to create a agent job to monitor the log agent and distribution > >> > agent > >> > regularly and I found system table msreplication_monitordata. > >> > According > >> > to > >> > BOL, it contains 'isagentrunningnow' which indicates the agent status. > >> > However, the field 'isagentrunningnow' is all 'NULL' in my server. > >> > Please > >> > advise. Thanks in advance > >> > >> > >> > >
They all mean the log reader agent is running, starting means its starting,
in progress means it is reading transactions in the log, idle means there is nothing in the log reader to read. -- Hilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. 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 [quoted text, click to view] "stephanie" <stephanie@discussions.microsoft.com> wrote in message news:D0195B13-A1ED-47C8-833B-ACD39C4CF241@microsoft.com... >I have tried the query in test env. I found an log agent are shown in > different pattern > Running Agent1: started, idle > Running Agent2: idle > Stopped agent1: Idle,Idle, In progress > Stopped agent2: started, Idle,Idle, In progress > > > Is there any standard pattern in the query which showing the job in > running? > Thanks in advance > > "Hilary Cotter" wrote: > >> You're right Stephanie >> >> This seems to only have non null values for the merge agent and then I >> only >> see if with a value of null or 0, even when my merge agent is running. I >> think you should query the job status in msdb. >> >> use msdb >> select sysjobs.name,sysjobs.job_id,rundate, runtime ,case when run_status >> =1 >> then 'Started' >> when run_status =2 then 'Succeeded' >> when run_status =3 then 'In progress' >> when run_status =4 then 'Idle' >> when run_status =5 then 'Retrying' >> when run_status =6 then 'Failed' >> else 'unknown' end >> from sysjobs join sysjobsteps >> on sysjobsteps.job_id= sysjobs.job_id and sysjobsteps.command like >> 'sp_msadd_logreader%' >> join (select >> sysjobhistory.job_id,runtime=max(run_time),rundate=max(k.rundate) from >> sysjobhistory >> join >> (select sysjobhistory.job_id, rundate=max(run_date)from sysjobhistory >> group by job_id ) as k >> on k.job_id=sysjobhistory.job_id and k.rundate=sysjobhistory.run_date >> >> group by sysjobhistory.job_id) as l >> on l.job_id=sysjobs.job_id >> join sysjobhistory on sysjobhistory.job_id=sysjobs.job_id >> and sysjobhistory.Run_date=l.rundate >> and sysjobhistory.Run_time=l.runtime >> >> >> -- >> Hilary Cotter >> Director of Text Mining and Database Strategy >> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. >> >> This posting is my own and doesn't necessarily represent RelevantNoise's >> positions, strategies or opinions. >> >> 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 >> >> >> >> "stephanie" <stephanie@discussions.microsoft.com> wrote in message >> news:554D6F3B-E265-412A-BE8B-F98BFA919ABF@microsoft.com... >> > Hi all, >> > I want to create a agent job to monitor the log agent and distribution >> > agent >> > regularly and I found system table msreplication_monitordata. >> > According >> > to >> > BOL, it contains 'isagentrunningnow' which indicates the agent status. >> > However, the field 'isagentrunningnow' is all 'NULL' in my server. >> > Please >> > advise. Thanks in advance >> >> >>
Look for Succeeded. You might also want to query select * from
distribution.dbo.MSlogreader_history where runstatus=2 -- Hilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. 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 [quoted text, click to view] "stephanie" <stephanie@discussions.microsoft.com> wrote in message news:58B4FB85-9AF8-428F-8609-A789E86CA1AD@microsoft.com... > How to show the agent was stopped?? > I found the stopped agents have status > Idle,Idle, In progress > started, Idle,Idle, In progress > > > > > "Hilary Cotter" wrote: > >> They all mean the log reader agent is running, starting means its >> starting, >> in progress means it is reading transactions in the log, idle means there >> is >> nothing in the log reader to read. >> >> -- >> Hilary Cotter >> Director of Text Mining and Database Strategy >> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. >> >> This posting is my own and doesn't necessarily represent RelevantNoise's >> positions, strategies or opinions. >> >> 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 >> >> >> >> "stephanie" <stephanie@discussions.microsoft.com> wrote in message >> news:D0195B13-A1ED-47C8-833B-ACD39C4CF241@microsoft.com... >> >I have tried the query in test env. I found an log agent are shown in >> > different pattern >> > Running Agent1: started, idle >> > Running Agent2: idle >> > Stopped agent1: Idle,Idle, In progress >> > Stopped agent2: started, Idle,Idle, In progress >> > >> > >> > Is there any standard pattern in the query which showing the job in >> > running? >> > Thanks in advance >> > >> > "Hilary Cotter" wrote: >> > >> >> You're right Stephanie >> >> >> >> This seems to only have non null values for the merge agent and then I >> >> only >> >> see if with a value of null or 0, even when my merge agent is running. >> >> I >> >> think you should query the job status in msdb. >> >> >> >> use msdb >> >> select sysjobs.name,sysjobs.job_id,rundate, runtime ,case when >> >> run_status >> >> =1 >> >> then 'Started' >> >> when run_status =2 then 'Succeeded' >> >> when run_status =3 then 'In progress' >> >> when run_status =4 then 'Idle' >> >> when run_status =5 then 'Retrying' >> >> when run_status =6 then 'Failed' >> >> else 'unknown' end >> >> from sysjobs join sysjobsteps >> >> on sysjobsteps.job_id= sysjobs.job_id and sysjobsteps.command like >> >> 'sp_msadd_logreader%' >> >> join (select >> >> sysjobhistory.job_id,runtime=max(run_time),rundate=max(k.rundate) from >> >> sysjobhistory >> >> join >> >> (select sysjobhistory.job_id, rundate=max(run_date)from sysjobhistory >> >> group by job_id ) as k >> >> on k.job_id=sysjobhistory.job_id and k.rundate=sysjobhistory.run_date >> >> >> >> group by sysjobhistory.job_id) as l >> >> on l.job_id=sysjobs.job_id >> >> join sysjobhistory on sysjobhistory.job_id=sysjobs.job_id >> >> and sysjobhistory.Run_date=l.rundate >> >> and sysjobhistory.Run_time=l.runtime >> >> >> >> >> >> -- >> >> Hilary Cotter >> >> Director of Text Mining and Database Strategy >> >> RelevantNOISE.Com - Dedicated to mining blogs for business >> >> intelligence. >> >> >> >> This posting is my own and doesn't necessarily represent >> >> RelevantNoise's >> >> positions, strategies or opinions. >> >> >> >> 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 >> >> >> >> >> >> >> >> "stephanie" <stephanie@discussions.microsoft.com> wrote in message >> >> news:554D6F3B-E265-412A-BE8B-F98BFA919ABF@microsoft.com... >> >> > Hi all, >> >> > I want to create a agent job to monitor the log agent and >> >> > distribution >> >> > agent >> >> > regularly and I found system table msreplication_monitordata. >> >> > According >> >> > to >> >> > BOL, it contains 'isagentrunningnow' which indicates the agent >> >> > status. >> >> > However, the field 'isagentrunningnow' is all 'NULL' in my server. >> >> > Please >> >> > advise. Thanks in advance >> >> >> >> >> >> >> >> >>
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |