Clean up agent

is there a way to tell the status of a clean up agent?
Nothing is in the history for him , but when I right click on him my
option is "stop agent".. start agent is grey'd out which tells me he is
running. I do a sp_who2 on the dist. and don't see anything about him
there.
What else can i look for.
Thanks,
-comb
What about if you run sp_help_job and look at 'current_execution_status'?
Rgds,
Paul Ibison
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.replication:63692
In article <OsgLtBJXFHA.2420@.TK2MSFTNGP12.phx.gbl>,
Paul.Ibison@.Pygmalion.Com says...
> What about if you run sp_help_job and look at 'current_execution_status'?
> Rgds,
> Paul Ibison
>
>
yea i just did that and it does tell me that much.
thanks though paul. I appreciate your help as always.
-comb
sqlsql

CHEKING SQL AGENT IN A LAN

Hi we have a LAN of 400 stations and sometimes the agent stop in several
machines, causing jobs not to run, so i have to check manually if the agent
is running. The question is: There's a fast way to check if all the agents in
my network are started?
* my version is sql server2000 with SP4
Thanks
Have a look at "Mutiserver Adminstration" in BOL.
"amejiae" wrote:

> Hi we have a LAN of 400 stations and sometimes the agent stop in several
> machines, causing jobs not to run, so i have to check manually if the agent
> is running. The question is: There's a fast way to check if all the agents in
> my network are started?
> * my version is sql server2000 with SP4
> Thanks
|||You might benefit greatly from a product available at:
http://www.sqlsentry.net/
Andrew J. Kelly SQL MVP
"amejiae" <amejiae@.discussions.microsoft.com> wrote in message
news:3B73EBF5-D28D-4DCA-9672-35BD27BA26B9@.microsoft.com...
> Hi we have a LAN of 400 stations and sometimes the agent stop in several
> machines, causing jobs not to run, so i have to check manually if the
> agent
> is running. The question is: There's a fast way to check if all the agents
> in
> my network are started?
> * my version is sql server2000 with SP4
> Thanks
|||you could write a script swith SQLDMO
"amejiae" <amejiae@.discussions.microsoft.com> wrote in message
news:3B73EBF5-D28D-4DCA-9672-35BD27BA26B9@.microsoft.com...
> Hi we have a LAN of 400 stations and sometimes the agent stop in several
> machines, causing jobs not to run, so i have to check manually if the
> agent
> is running. The question is: There's a fast way to check if all the agents
> in
> my network are started?
> * my version is sql server2000 with SP4
> Thanks
|||Or you can take a look at IDERA's Diagnostic Manager.
"amejiae" wrote:

> Hi we have a LAN of 400 stations and sometimes the agent stop in several
> machines, causing jobs not to run, so i have to check manually if the agent
> is running. The question is: There's a fast way to check if all the agents in
> my network are started?
> * my version is sql server2000 with SP4
> Thanks

CHEKING SQL AGENT IN A LAN

Hi we have a LAN of 400 stations and sometimes the agent stop in several
machines, causing jobs not to run, so i have to check manually if the agent
is running. The question is: There's a fast way to check if all the agents i
n
my network are started?
* my version is sql server2000 with SP4
ThanksHave a look at "Mutiserver Adminstration" in BOL.
"amejiae" wrote:

> Hi we have a LAN of 400 stations and sometimes the agent stop in several
> machines, causing jobs not to run, so i have to check manually if the agen
t
> is running. The question is: There's a fast way to check if all the agents
in
> my network are started?
> * my version is sql server2000 with SP4
> Thanks|||You might benefit greatly from a product available at:
http://www.sqlsentry.net/
Andrew J. Kelly SQL MVP
"amejiae" <amejiae@.discussions.microsoft.com> wrote in message
news:3B73EBF5-D28D-4DCA-9672-35BD27BA26B9@.microsoft.com...
> Hi we have a LAN of 400 stations and sometimes the agent stop in several
> machines, causing jobs not to run, so i have to check manually if the
> agent
> is running. The question is: There's a fast way to check if all the agents
> in
> my network are started?
> * my version is sql server2000 with SP4
> Thanks|||you could write a script swith SQLDMO
"amejiae" <amejiae@.discussions.microsoft.com> wrote in message
news:3B73EBF5-D28D-4DCA-9672-35BD27BA26B9@.microsoft.com...
> Hi we have a LAN of 400 stations and sometimes the agent stop in several
> machines, causing jobs not to run, so i have to check manually if the
> agent
> is running. The question is: There's a fast way to check if all the agents
> in
> my network are started?
> * my version is sql server2000 with SP4
> Thanks|||Or you can take a look at IDERA's Diagnostic Manager.
"amejiae" wrote:

> Hi we have a LAN of 400 stations and sometimes the agent stop in several
> machines, causing jobs not to run, so i have to check manually if the agen
t
> is running. The question is: There's a fast way to check if all the agents
in
> my network are started?
> * my version is sql server2000 with SP4
> Thanks

CHEKING SQL AGENT IN A LAN

Hi we have a LAN of 400 stations and sometimes the agent stop in several
machines, causing jobs not to run, so i have to check manually if the agent
is running. The question is: There's a fast way to check if all the agents in
my network are started?
* my version is sql server2000 with SP4
ThanksHave a look at "Mutiserver Adminstration" in BOL.
"amejiae" wrote:
> Hi we have a LAN of 400 stations and sometimes the agent stop in several
> machines, causing jobs not to run, so i have to check manually if the agent
> is running. The question is: There's a fast way to check if all the agents in
> my network are started?
> * my version is sql server2000 with SP4
> Thanks|||You might benefit greatly from a product available at:
http://www.sqlsentry.net/
Andrew J. Kelly SQL MVP
"amejiae" <amejiae@.discussions.microsoft.com> wrote in message
news:3B73EBF5-D28D-4DCA-9672-35BD27BA26B9@.microsoft.com...
> Hi we have a LAN of 400 stations and sometimes the agent stop in several
> machines, causing jobs not to run, so i have to check manually if the
> agent
> is running. The question is: There's a fast way to check if all the agents
> in
> my network are started?
> * my version is sql server2000 with SP4
> Thanks|||you could write a script swith SQLDMO
"amejiae" <amejiae@.discussions.microsoft.com> wrote in message
news:3B73EBF5-D28D-4DCA-9672-35BD27BA26B9@.microsoft.com...
> Hi we have a LAN of 400 stations and sometimes the agent stop in several
> machines, causing jobs not to run, so i have to check manually if the
> agent
> is running. The question is: There's a fast way to check if all the agents
> in
> my network are started?
> * my version is sql server2000 with SP4
> Thanks|||Or you can take a look at IDERA's Diagnostic Manager.
"amejiae" wrote:
> Hi we have a LAN of 400 stations and sometimes the agent stop in several
> machines, causing jobs not to run, so i have to check manually if the agent
> is running. The question is: There's a fast way to check if all the agents in
> my network are started?
> * my version is sql server2000 with SP4
> Thanks

checking the status of merge agent

My merge agent is getting stopped at midnight. because of which i have large
number of records which needs to be merged when i come in the morning.
Is it possible To create a job which will run every 3-4 hours, which will
check whether the merge agent is running or not? If the merge agent is
stopped then start the merge agent.
Is there a better way? Any suggestions?
just reschedule your merge agent to run every 5 minutes, or have step 4 on
failure return to step 1.
Hilary Cotter
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
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:F63A7EB7-31BF-44CE-A966-5089BF241D3C@.microsoft.com...
> My merge agent is getting stopped at midnight. because of which i have
large
> number of records which needs to be merged when i come in the morning.
> Is it possible To create a job which will run every 3-4 hours, which will
> check whether the merge agent is running or not? If the merge agent is
> stopped then start the merge agent.
> Is there a better way? Any suggestions?
>
|||> just reschedule your merge agent to run every 5 minutes,
This is a cool option...

> or have step 4 on failure return to step 1.
There is no step 4. I am using continous merge replication. There is only
one step. Hence i can't go to the first step. Or am I understanding it in a
different way?
"Hilary Cotter" wrote:

> just reschedule your merge agent to run every 5 minutes, or have step 4 on
> failure return to step 1.
> --
> Hilary Cotter
> 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
> "ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
> news:F63A7EB7-31BF-44CE-A966-5089BF241D3C@.microsoft.com...
> large
>
>
|||right click on your agent in the merge agents folder, select agent
properties, and then steps. Change Step 3 (not step 4 - my mistake) to wrap
around to step 1 on failure. Click on the advanced tab to do this.
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:4258F2A9-F495-40E6-ACBC-784AFDCBA4F9@.microsoft.com...
> This is a cool option...
> There is no step 4. I am using continous merge replication. There is only
> one step. Hence i can't go to the first step. Or am I understanding it in
a[vbcol=seagreen]
> different way?
>
> "Hilary Cotter" wrote:
on[vbcol=seagreen]
will[vbcol=seagreen]
|||I know what you are saying.
But in my agent property i have only one step. No 3 or 4 steps.
I am using merge replication. What does the 2nd and 3rd step contain?
"Hilary Cotter" wrote:

> right click on your agent in the merge agents folder, select agent
> properties, and then steps. Change Step 3 (not step 4 - my mistake) to wrap
> around to step 1 on failure. Click on the advanced tab to do this.
> "ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
> news:4258F2A9-F495-40E6-ACBC-784AFDCBA4F9@.microsoft.com...
> a
> on
> will
>
>
|||is this an ActiveX script you are running? Or are you pulling from SQL CE?
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:ACF2F24C-4715-4DFE-B6EB-FA12E06CD3B0@.microsoft.com...[vbcol=seagreen]
> I know what you are saying.
> But in my agent property i have only one step. No 3 or 4 steps.
> I am using merge replication. What does the 2nd and 3rd step contain?
> "Hilary Cotter" wrote:
wrap[vbcol=seagreen]
only[vbcol=seagreen]
in[vbcol=seagreen]
step 4[vbcol=seagreen]
have[vbcol=seagreen]
morning.[vbcol=seagreen]
which[vbcol=seagreen]
agent is[vbcol=seagreen]
|||I am not using ActiveX script
Also not SQL CE
"Hilary Cotter" wrote:

> is this an ActiveX script you are running? Or are you pulling from SQL CE?
> "ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
> news:ACF2F24C-4715-4DFE-B6EB-FA12E06CD3B0@.microsoft.com...
> wrap
> only
> in
> step 4
> have
> morning.
> which
> agent is
>
>

checking sql agent status

Is there any possible way to use T-sql checking sql agent status
thanx for your repliesHi
Assuming that you require to know that the SQLSERVERAGENT process is
running!You could try something like:
CREATE TABLE Running ( Service varchar(100) )
INSERT INTO Running ( Service ) EXEC master..xp_cmdshell 'NET START'
IF EXISTS ( SELECT 1 FROM Running WHERE Service LIKE '%SQLSERVERAGENT' )
PRINT 'Agent is running'
John
"I.O" <anonymous@.discussions.microsoft.com> wrote in message
news:1EB89D23-BE37-4F3A-98D0-8DACB1874090@.microsoft.com...
> Is there any possible way to use T-sql checking sql agent status?
> thanx for your replies

checking sql agent status

Is there any possible way to use T-sql checking sql agent status?
thanx for your repliesHi
Assuming that you require to know that the SQLSERVERAGENT process is
running!You could try something like:
CREATE TABLE Running ( Service varchar(100) )
INSERT INTO Running ( Service ) EXEC master..xp_cmdshell 'NET START'
IF EXISTS ( SELECT 1 FROM Running WHERE Service LIKE '%SQLSERVERAGENT' )
PRINT 'Agent is running'
John
"I.O" <anonymous@.discussions.microsoft.com> wrote in message
news:1EB89D23-BE37-4F3A-98D0-8DACB1874090@.microsoft.com...
> Is there any possible way to use T-sql checking sql agent status?
> thanx for your replies

checking sql agent status

Is there any possible way to use T-sql checking sql agent status?
thanx for your replies
Hi
Assuming that you require to know that the SQLSERVERAGENT process is
running!You could try something like:
CREATE TABLE Running ( Service varchar(100) )
INSERT INTO Running ( Service ) EXEC master..xp_cmdshell 'NET START'
IF EXISTS ( SELECT 1 FROM Running WHERE Service LIKE '%SQLSERVERAGENT' )
PRINT 'Agent is running'
John
"I.O" <anonymous@.discussions.microsoft.com> wrote in message
news:1EB89D23-BE37-4F3A-98D0-8DACB1874090@.microsoft.com...
> Is there any possible way to use T-sql checking sql agent status?
> thanx for your replies

Checking Merge agent status in SQL2005

Hi sql gurus,
Is there any way to check the status of the merge agent job per subscriber
in SQL 2005?
Below is the same check for SQL 2000:
use distribution
select runstatus from MSmerge_history as h
where h.timestamp in (select max(timestamp) from MSmerge_history where
agent_id =
( select top 1 id from msmerge_agents where subscriber_id =
(select srvid from master..sysservers where srvname =
'<subscriber_server_name>')) )
I discovered the solution
use distribution
select mh.*, isnull(ms.runstatus,0) as runstatus
from dbo.MSmerge_history mh with (READPAST), dbo.MSmerge_sessions ms with
(READPAST)
where mh.timestamp in (select max(timestamp) from MSmerge_history where
agent_id in
( select top 1 id from msmerge_agents where subscriber_id =
(select srvid from master..sysservers where srvname =
'subscriber_server_name'))) and ms.agent_id = mh.agent_id

Checking for success/failure in TSQL SQL Agent job step

I am relatively new to SQL Agent job creation. I am trying to create a job that will check for disabled triggers and notify an operator. The job step I have created is Transact-SQL. I am checking sys.triggers for disabled triggers. My question is:

How do I indicate whether the job step was a success or failure in the TSQL, so I can trigger the correct response to it?

Can I check the return code from a stored procedure to determine success or failure?

Thanks,

Larry

Just raise an error in the proc like:

if exists (select * from sys.triggers where is_disabled <> 0)
raiserror('disabled triggers found',16,1)

this will cause the job step to fail

|||

Actually, I've tried using the DDL trigger to catch it. However, when you enter the DISABLE TRIGGER..... command, it does not fire the ALTER TRIGGER or ALTER TABLE event and thus can't be detected by a DDL trigger. Currently, my DDL trigger prevents a DROP trigger or an ALTER TABLE DISABLE TRIGGER event from happening. If anyone can tell me how to use the DDL trigger to prevent a plain DISABLE TRIGGER, I'm all ears.

Thanks,

Larry

|||

If, in your database/server, you are concerned with disabled Triggers, you most likely should NOT provide anyone the level of permissions that would allow them to disable Triggers.

Checking for success/failure in TSQL SQL Agent job step

I am relatively new to SQL Agent job creation. I am trying to create a job that will check for disabled triggers and notify an operator. The job step I have created is Transact-SQL. I am checking sys.triggers for disabled triggers. My question is:

How do I indicate whether the job step was a success or failure in the TSQL, so I can trigger the correct response to it?

Can I check the return code from a stored procedure to determine success or failure?

Thanks,

Larry

Just raise an error in the proc like:

if exists (select * from sys.triggers where is_disabled <> 0)
raiserror('disabled triggers found',16,1)

this will cause the job step to fail

|||

Actually, I've tried using the DDL trigger to catch it. However, when you enter the DISABLE TRIGGER..... command, it does not fire the ALTER TRIGGER or ALTER TABLE event and thus can't be detected by a DDL trigger. Currently, my DDL trigger prevents a DROP trigger or an ALTER TABLE DISABLE TRIGGER event from happening. If anyone can tell me how to use the DDL trigger to prevent a plain DISABLE TRIGGER, I'm all ears.

Thanks,

Larry

|||

If, in your database/server, you are concerned with disabled Triggers, you most likely should NOT provide anyone the level of permissions that would allow them to disable Triggers.

Checking for stored procedure return code in SQL Agent job

I am relatively new to SQL Agent job creation. I am trying to create a job that will check for disabled triggers and notify an operator. The job step I have created is Transact-SQL. I am checking sys.triggers for disabled triggers. My question is:

How do I indicate whether the job step was a success or failure in the TSQL, so I can trigger the correct response to it?

Can I check the return code from a stored procedure to determine success or failure?

Thanks,

Larry

I would think that it could be a bit more 'robust' to have a DDL trigger that would set off your alert process upon an ALTER TRIGGER event...

Otherwise, use RAISERROR when a row with disabled trigger if found.

check the status of SQL Agent

Hi all

in my project, I need to access SQL job to finish something. But sometimes, the status of SQL Agent is not running, which needs me to check the status first. I am wonder are there some functions or some ways to check the status. If you know, please response me.

I appreciate your response !

You don't need to check for status if you create a proxy account for the Agent, that is clone an Admin level permissions to run the Agent, run a search for SQL Server Agent Proxy account in the BOL(books online). The reason is when you run a Job manually it runs in the context of your permission as a Job it becomes the Agent's which means it needs your level of permissions to run it. Hope this helps.|||

Hi Caddre

Thanks for your response. I project workd is like this:

it need to restar the machine in which SQL settled. After it checked the Sql Connection is re-established.(means the machine is restarteed), then it call some jobs. but an exception was thrown out, say,"Cannot execute ...., Sql Agent is running". so I make my thread sleeping for about 10 seconds. the exeception is not thrown out. but after I translated the project in other sql groups machines. the exception is out again. so I think I should check the status of the SQL Agent. do you have some suggestion?

Thank you very much !

|||

Liu_andi:

Hi Caddre

Thanks for your response. I project workd is like this:

it need to restar the machine in which SQL settled. After it checked the Sql Connection is re-established.(means the machine is restarteed), then it call some jobs. but an exception was thrown out, say,"Cannot execute ...., Sql Agent is running". so I make my thread sleeping for about 10 seconds. the exeception is not thrown out. but after I translated the project in other sql groups machines. the exception is out again. so I think I should check the status of the SQL Agent. do you have some suggestion?

Thank you very much !

When you start SQL Server you are starting the SQL Server service it is different from the Agent without the proxy account the Agent will not be running in an application as you expect it, threads are not relevant to the issue it is related to the Agent having enough permission to run, it takes a lot of people time to understand but fix it or your jobs will not run as you expect. Hope this helps.

|||

Hi Caddre

you are so nice to give me response.

you mentioned a proxy account in the response, I forgot to tell you that I use a domain account which have admin permission, to access SQL. I found a way to check whether the Service runs or not, by following sql querying sentence:

exec master..xpcmdshell 'net start'.

the query will return a table in which includes all the service which status is running. we can check whether SQL Agent is running or not by finding the key word"SQLSERVERAGENT" in the table. How about your idea?

|||

Other way I think is more better to use:

declare @.service nvarchar(100)

select @.service = case when charindex('\',@.@.servername)>0
thenN'SQLAgent$'+@.@.servicename
else N'SQLSERVERAGENT' end

exec master..xp_servicecontrol N'QUERYSTATE', @.service

the return is "Running.", which expresses the status of SqlAge is running.

|||

Liu_andi:

Hi Caddre

you are so nice to give me response.

you mentioned a proxy account in the response, I forgot to tell you that I use a domain account which have admin permission, to access SQL. I found a way to check whether the Service runs or not, by following sql querying sentence:

exec master..xpcmdshell 'net start'.

the query will return a table in which includes all the service which status is running. we can check whether SQL Agent is running or not by finding the key word"SQLSERVERAGENT" in the table. How about your idea?

This is a query against one database that exposes all your SQL Server that is not prudent, what I am telling you to do is a simple process running under a service clean and valid. I am telling you to do it the correct way what you actually do is your employer's business. Hope this helps.

check the status of SQL Agent

Hi all

In my project, i need to call some jobs to finish some things. But sometimes, Sql Agent is not run. I need to check the status of it. Are there some functions or other ways to check it. if you know, please response me.

thank you !

You can use this script

declare @.service nvarchar(100)

select @.service = case when charindex('\',@.@.servername)>0
then N'SQLAgent$'+@.@.servicename
else N'SQLSERVERAGENT' end

exec master..xp_servicecontrol N'QUERYSTATE', @.service|||Great answer, thank you !

Check Sysobjects Error during Snapshot

During the snapshot generation, I get this eror while the snapshot agent is
generating the schema script.
Here's my setup (all win2k sql2k servers 3 diffrent machines) all pull
snapshot/transactional replications.
On my sole publisher on its own server, I have 4 publications, two per
published databases (A and B), each publication is slightly different (row
filtering).
One distributor on a different server, One subscriber (1) on this same server.
Another subscriber(2) on a third server.
I can replicate database A publication A1 to to Subscriber1 and publication
A2 to subscriber 2 without a problem.
Next, I can replicate database b publication B2 to Subscriber2 without a
problem.
When I try running the snapshot for database b publication B1 to Subscriber1
it start running along, and then gets the checksysobjects error. There are no
other snapshots running concurrently and all other agents are idle.
Any ideas here?
can you post the entire error message here?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Need more Zzzz" <NeedmoreZzzz@.discussions.microsoft.com> wrote in message
news:8F9965DC-0049-4C21-BB34-B211F1FE531D@.microsoft.com...
> During the snapshot generation, I get this eror while the snapshot agent
is
> generating the schema script.
> Here's my setup (all win2k sql2k servers 3 diffrent machines) all pull
> snapshot/transactional replications.
> On my sole publisher on its own server, I have 4 publications, two per
> published databases (A and B), each publication is slightly different (row
> filtering).
> One distributor on a different server, One subscriber (1) on this same
server.
> Another subscriber(2) on a third server.
> I can replicate database A publication A1 to to Subscriber1 and
publication
> A2 to subscriber 2 without a problem.
> Next, I can replicate database b publication B2 to Subscriber2 without a
> problem.
> When I try running the snapshot for database b publication B1 to
Subscriber1
> it start running along, and then gets the checksysobjects error. There are
no
> other snapshots running concurrently and all other agents are idle.
> Any ideas here?
>
|||Here are the Snapshot Agent Error Details (I substituted the actual
servername with "<My Server Name>":
'. Check sysobjects.
(Source: <My Server Name>(Data source); Error number: 2501)
"Hilary Cotter" wrote:

> can you post the entire error message here?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Need more Zzzz" <NeedmoreZzzz@.discussions.microsoft.com> wrote in message
> news:8F9965DC-0049-4C21-BB34-B211F1FE531D@.microsoft.com...
> is
> server.
> publication
> Subscriber1
> no
>
>
|||does this post help?
http://groups.google.com/groups?hl=e...GP10. phx.gbl
It seems that when you apply the snapshot one of the objects might already
exist on the subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Need more Zzzz" <NeedmoreZzzz@.discussions.microsoft.com> wrote in message
news:E7E74D1F-C27D-4E4E-B767-0F57A7689D98@.microsoft.com...[vbcol=seagreen]
> Here are the Snapshot Agent Error Details (I substituted the actual
> servername with "<My Server Name>":
> '. Check sysobjects.
> (Source: <My Server Name>(Data source); Error number: 2501)
>
> "Hilary Cotter" wrote:
message[vbcol=seagreen]
agent[vbcol=seagreen]
(row[vbcol=seagreen]
a[vbcol=seagreen]
are[vbcol=seagreen]
|||I used the MS Online Assisted Report to help me resolve the problem very
quickly (their initial response within 24hrs pointed me in the right
direction).
Basically, each article's filter name and view name must be DATABASE-UNIQUE
in addition to publication-unique. The names are used to create stored
procedures and views in the publication database. If you create only one
publication there is no problem, but if you create two publications, then
there is name overlap and then you'll probably see the problem during
snapshot generation of the 2nd publication.
So when calling sp_articlefilter and sp_articleview, make sure that
@.filter_name and @.view_name are database-unique!
I hope this saves somebody else from the headache I went through.
- Cynthia
"Need more Zzzz" wrote:

> During the snapshot generation, I get this eror while the snapshot agent is
> generating the schema script.
> Here's my setup (all win2k sql2k servers 3 diffrent machines) all pull
> snapshot/transactional replications.
> On my sole publisher on its own server, I have 4 publications, two per
> published databases (A and B), each publication is slightly different (row
> filtering).
> One distributor on a different server, One subscriber (1) on this same server.
> Another subscriber(2) on a third server.
> I can replicate database A publication A1 to to Subscriber1 and publication
> A2 to subscriber 2 without a problem.
> Next, I can replicate database b publication B2 to Subscriber2 without a
> problem.
> When I try running the snapshot for database b publication B1 to Subscriber1
> it start running along, and then gets the checksysobjects error. There are no
> other snapshots running concurrently and all other agents are idle.
> Any ideas here?
>

Check SQL Agent History for additional details

Where do I check for the additional details when the job fails? This is
from both the msdb.dbo.sysjobhistory table and the SSMS Log File Viewer. On
a related note, is there a way to determine how many records were affected
by a job without modifying the stored procedure that was called?
Thanks.
Message
Executed as user: USER. 1 SQL Agent jobs have failed on Server SERVER.
Check SQL Agent History for additional details. [SQLSTATE 42000] (Error
50000). The step failed.I don't know where the logs are for SQL Server, but some of the stuff
gets posted into the server's event logs which can be seen with the
event log viewer on the server:
start->programs->administrative tools->event viewer|||Open up SSMS and go to SQL Server Agent node and expand Jobs, right click on
your job and select View History.
--
Ekrem Önsoy
<a> wrote in message news:uKFxvQDDIHA.972@.TK2MSFTNGP05.phx.gbl...
> Where do I check for the additional details when the job fails? This is
> from both the msdb.dbo.sysjobhistory table and the SSMS Log File Viewer.
> On a related note, is there a way to determine how many records were
> affected by a job without modifying the stored procedure that was called?
> Thanks.
> Message
> Executed as user: USER. 1 SQL Agent jobs have failed on Server SERVER.
> Check SQL Agent History for additional details. [SQLSTATE 42000] (Error
> 50000). The step failed.
>|||Thanks for the reply, that is where I got that message from re: the SSMS Log
File Viewer
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:580D43DC-2CDF-49D0-AB8D-B5D005CFBB42@.microsoft.com...
> Open up SSMS and go to SQL Server Agent node and expand Jobs, right click
> on your job and select View History.
> --
> Ekrem Önsoy
>
> <a> wrote in message news:uKFxvQDDIHA.972@.TK2MSFTNGP05.phx.gbl...
>> Where do I check for the additional details when the job fails? This is
>> from both the msdb.dbo.sysjobhistory table and the SSMS Log File Viewer.
>> On a related note, is there a way to determine how many records were
>> affected by a job without modifying the stored procedure that was called?
>> Thanks.
>> Message
>> Executed as user: USER. 1 SQL Agent jobs have failed on Server SERVER.
>> Check SQL Agent History for additional details. [SQLSTATE 42000] (Error
>> 50000). The step failed.
>|||Did you navigate into the details for the job step?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<a> wrote in message news:eB$l6BEDIHA.4712@.TK2MSFTNGP04.phx.gbl...
> Thanks for the reply, that is where I got that message from re: the SSMS Log File Viewer
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:580D43DC-2CDF-49D0-AB8D-B5D005CFBB42@.microsoft.com...
>> Open up SSMS and go to SQL Server Agent node and expand Jobs, right click on your job and select
>> View History.
>> --
>> Ekrem Önsoy
>>
>> <a> wrote in message news:uKFxvQDDIHA.972@.TK2MSFTNGP05.phx.gbl...
>> Where do I check for the additional details when the job fails? This is from both the
>> msdb.dbo.sysjobhistory table and the SSMS Log File Viewer. On a related note, is there a way to
>> determine how many records were affected by a job without modifying the stored procedure that
>> was called?
>> Thanks.
>> Message
>> Executed as user: USER. 1 SQL Agent jobs have failed on Server SERVER. Check SQL Agent History
>> for additional details. [SQLSTATE 42000] (Error 50000). The step failed.
>>
>|||Yes I should have mentioned that was the details for the job step:
Date 10/11/2007 4:00:00 PM
Log Job History (DBA Scheduled Tasks - SQL Agent Failure Notifications)
Step ID 1
Server SERVERR
Job Name DBA Scheduled Tasks - SQL Agent Failure Notifications
Step Name Notify via MOM when SQL Agent Jobs Fail
Duration 00:00:01
Sql Severity 16
Sql Message ID 50000
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: USER. 1 SQL Agent jobs have failed on Server SERVERR.
Check SQL Agent History for additional details. [SQLSTATE 42000] (Error
50000). The step failed.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:751A094C-8F05-476B-920D-F1CAD8F1F8F4@.microsoft.com...
> Did you navigate into the details for the job step?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> <a> wrote in message news:eB$l6BEDIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Thanks for the reply, that is where I got that message from re: the SSMS
>> Log File Viewer
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:580D43DC-2CDF-49D0-AB8D-B5D005CFBB42@.microsoft.com...
>> Open up SSMS and go to SQL Server Agent node and expand Jobs, right
>> click on your job and select View History.
>> --
>> Ekrem Önsoy
>>
>> <a> wrote in message news:uKFxvQDDIHA.972@.TK2MSFTNGP05.phx.gbl...
>> Where do I check for the additional details when the job fails? This
>> is from both the msdb.dbo.sysjobhistory table and the SSMS Log File
>> Viewer. On a related note, is there a way to determine how many records
>> were affected by a job without modifying the stored procedure that was
>> called?
>> Thanks.
>> Message
>> Executed as user: USER. 1 SQL Agent jobs have failed on Server SERVER.
>> Check SQL Agent History for additional details. [SQLSTATE 42000] (Error
>> 50000). The step failed.
>>
>>
>|||Did you check Event Viewer for any clue?
--
Ekrem Önsoy
<a> wrote in message news:uJXuKDFDIHA.4196@.TK2MSFTNGP04.phx.gbl...
> Yes I should have mentioned that was the details for the job step:
> Date 10/11/2007 4:00:00 PM
> Log Job History (DBA Scheduled Tasks - SQL Agent Failure Notifications)
> Step ID 1
> Server SERVERR
> Job Name DBA Scheduled Tasks - SQL Agent Failure Notifications
> Step Name Notify via MOM when SQL Agent Jobs Fail
> Duration 00:00:01
> Sql Severity 16
> Sql Message ID 50000
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> Executed as user: USER. 1 SQL Agent jobs have failed on Server SERVERR.
> Check SQL Agent History for additional details. [SQLSTATE 42000] (Error
> 50000). The step failed.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:751A094C-8F05-476B-920D-F1CAD8F1F8F4@.microsoft.com...
>> Did you navigate into the details for the job step?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> <a> wrote in message news:eB$l6BEDIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Thanks for the reply, that is where I got that message from re: the SSMS
>> Log File Viewer
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:580D43DC-2CDF-49D0-AB8D-B5D005CFBB42@.microsoft.com...
>> Open up SSMS and go to SQL Server Agent node and expand Jobs, right
>> click on your job and select View History.
>> --
>> Ekrem Önsoy
>>
>> <a> wrote in message news:uKFxvQDDIHA.972@.TK2MSFTNGP05.phx.gbl...
>> Where do I check for the additional details when the job fails? This
>> is from both the msdb.dbo.sysjobhistory table and the SSMS Log File
>> Viewer. On a related note, is there a way to determine how many
>> records were affected by a job without modifying the stored procedure
>> that was called?
>> Thanks.
>> Message
>> Executed as user: USER. 1 SQL Agent jobs have failed on Server SERVER.
>> Check SQL Agent History for additional details. [SQLSTATE 42000]
>> (Error 50000). The step failed.
>>
>>
>|||From event viewer:
Error: 50000 Severity: 16 State: 1 1 SQL Agent jobs have failed on Server
Server. Check SQL Agent History for additional details.
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:A62F0127-7F07-4EB1-A4E3-F482D55C8D85@.microsoft.com...
> Did you check Event Viewer for any clue?
> --
> Ekrem Önsoy
>
> <a> wrote in message news:uJXuKDFDIHA.4196@.TK2MSFTNGP04.phx.gbl...
>> Yes I should have mentioned that was the details for the job step:
>> Date 10/11/2007 4:00:00 PM
>> Log Job History (DBA Scheduled Tasks - SQL Agent Failure Notifications)
>> Step ID 1
>> Server SERVERR
>> Job Name DBA Scheduled Tasks - SQL Agent Failure Notifications
>> Step Name Notify via MOM when SQL Agent Jobs Fail
>> Duration 00:00:01
>> Sql Severity 16
>> Sql Message ID 50000
>> Operator Emailed
>> Operator Net sent
>> Operator Paged
>> Retries Attempted 0
>> Message
>> Executed as user: USER. 1 SQL Agent jobs have failed on Server SERVERR.
>> Check SQL Agent History for additional details. [SQLSTATE 42000] (Error
>> 50000). The step failed.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:751A094C-8F05-476B-920D-F1CAD8F1F8F4@.microsoft.com...
>> Did you navigate into the details for the job step?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> <a> wrote in message news:eB$l6BEDIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Thanks for the reply, that is where I got that message from re: the
>> SSMS Log File Viewer
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:580D43DC-2CDF-49D0-AB8D-B5D005CFBB42@.microsoft.com...
>> Open up SSMS and go to SQL Server Agent node and expand Jobs, right
>> click on your job and select View History.
>> --
>> Ekrem Önsoy
>>
>> <a> wrote in message news:uKFxvQDDIHA.972@.TK2MSFTNGP05.phx.gbl...
>> Where do I check for the additional details when the job fails? This
>> is from both the msdb.dbo.sysjobhistory table and the SSMS Log File
>> Viewer. On a related note, is there a way to determine how many
>> records were affected by a job without modifying the stored procedure
>> that was called?
>> Thanks.
>> Message
>> Executed as user: USER. 1 SQL Agent jobs have failed on Server
>> SERVER. Check SQL Agent History for additional details. [SQLSTATE
>> 42000] (Error 50000). The step failed.
>>
>>
>>
>|||I guess you need to investigate what this job does and how to interpret the output from the job
through the one who wrote the job. The output states some information for you:
> Sql Severity 16
> Sql Message ID 50000
Above show you that the TSQL code executed by the job step returned error 50000 and severity 16. The
some text from whatever it was you executed:
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
Above is probably the error message from the executed TSQL code. The name of the job step:
> Step Name Notify via MOM when SQL Agent Jobs Fail
suggests that it has to do with MOM, so you probably want to read MOM documentation and/or ask in a
MOM group. A wild guess from my part is that whatever it is you execute tried to do a NET SEND which
fails (messagenr service is disabled by default nowadays).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<a> wrote in message news:uJXuKDFDIHA.4196@.TK2MSFTNGP04.phx.gbl...
> Yes I should have mentioned that was the details for the job step:
> Date 10/11/2007 4:00:00 PM
> Log Job History (DBA Scheduled Tasks - SQL Agent Failure Notifications)
> Step ID 1
> Server SERVERR
> Job Name DBA Scheduled Tasks - SQL Agent Failure Notifications
> Step Name Notify via MOM when SQL Agent Jobs Fail
> Duration 00:00:01
> Sql Severity 16
> Sql Message ID 50000
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> Executed as user: USER. 1 SQL Agent jobs have failed on Server SERVERR. Check SQL Agent History
> for additional details. [SQLSTATE 42000] (Error 50000). The step failed.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:751A094C-8F05-476B-920D-F1CAD8F1F8F4@.microsoft.com...
>> Did you navigate into the details for the job step?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> <a> wrote in message news:eB$l6BEDIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Thanks for the reply, that is where I got that message from re: the SSMS Log File Viewer
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:580D43DC-2CDF-49D0-AB8D-B5D005CFBB42@.microsoft.com...
>> Open up SSMS and go to SQL Server Agent node and expand Jobs, right click on your job and
>> select View History.
>> --
>> Ekrem Önsoy
>>
>> <a> wrote in message news:uKFxvQDDIHA.972@.TK2MSFTNGP05.phx.gbl...
>> Where do I check for the additional details when the job fails? This is from both the
>> msdb.dbo.sysjobhistory table and the SSMS Log File Viewer. On a related note, is there a way
>> to determine how many records were affected by a job without modifying the stored procedure
>> that was called?
>> Thanks.
>> Message
>> Executed as user: USER. 1 SQL Agent jobs have failed on Server SERVER. Check SQL Agent History
>> for additional details. [SQLSTATE 42000] (Error 50000). The step failed.
>>
>>
>|||Thanks Tibor. This is just a sample error, I would think the details would
be in the msdb database somewhere. I am working on a report to query the
jobjistory tables for success and failure, and provide the details on the
job so even if it says success, I need further verification. For instance
if a job runs an SSIS package from the command line and the package fails,
the job still reports success. I can find absolutely nothing on the web for
the phrase "Check SQL Agent history for additional details" and thought I
would try here.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:472E2179-D5A8-4D3A-8875-77EAA77FD0FF@.microsoft.com...
>I guess you need to investigate what this job does and how to interpret the
>output from the job through the one who wrote the job. The output states
>some information for you:
>> Sql Severity 16
>> Sql Message ID 50000
> Above show you that the TSQL code executed by the job step returned error
> 50000 and severity 16. The some text from whatever it was you executed:
>> Operator Emailed
>> Operator Net sent
>> Operator Paged
>> Retries Attempted 0
> Above is probably the error message from the executed TSQL code. The name
> of the job step:
>> Step Name Notify via MOM when SQL Agent Jobs Fail
> suggests that it has to do with MOM, so you probably want to read MOM
> documentation and/or ask in a MOM group. A wild guess from my part is that
> whatever it is you execute tried to do a NET SEND which fails (messagenr
> service is disabled by default nowadays).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> <a> wrote in message news:uJXuKDFDIHA.4196@.TK2MSFTNGP04.phx.gbl...
>> Yes I should have mentioned that was the details for the job step:
>> Date 10/11/2007 4:00:00 PM
>> Log Job History (DBA Scheduled Tasks - SQL Agent Failure Notifications)
>> Step ID 1
>> Server SERVERR
>> Job Name DBA Scheduled Tasks - SQL Agent Failure Notifications
>> Step Name Notify via MOM when SQL Agent Jobs Fail
>> Duration 00:00:01
>> Sql Severity 16
>> Sql Message ID 50000
>> Operator Emailed
>> Operator Net sent
>> Operator Paged
>> Retries Attempted 0
>> Message
>> Executed as user: USER. 1 SQL Agent jobs have failed on Server SERVERR.
>> Check SQL Agent History for additional details. [SQLSTATE 42000] (Error
>> 50000). The step failed.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:751A094C-8F05-476B-920D-F1CAD8F1F8F4@.microsoft.com...
>> Did you navigate into the details for the job step?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> <a> wrote in message news:eB$l6BEDIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Thanks for the reply, that is where I got that message from re: the
>> SSMS Log File Viewer
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:580D43DC-2CDF-49D0-AB8D-B5D005CFBB42@.microsoft.com...
>> Open up SSMS and go to SQL Server Agent node and expand Jobs, right
>> click on your job and select View History.
>> --
>> Ekrem Önsoy
>>
>> <a> wrote in message news:uKFxvQDDIHA.972@.TK2MSFTNGP05.phx.gbl...
>> Where do I check for the additional details when the job fails? This
>> is from both the msdb.dbo.sysjobhistory table and the SSMS Log File
>> Viewer. On a related note, is there a way to determine how many
>> records were affected by a job without modifying the stored procedure
>> that was called?
>> Thanks.
>> Message
>> Executed as user: USER. 1 SQL Agent jobs have failed on Server
>> SERVER. Check SQL Agent History for additional details. [SQLSTATE
>> 42000] (Error 50000). The step failed.
>>
>>
>>
>|||This is a customer error, not a SQL Server agent error. Thanks for all the
replies.
<a> wrote in message news:uKFxvQDDIHA.972@.TK2MSFTNGP05.phx.gbl...
> Where do I check for the additional details when the job fails? This is
> from both the msdb.dbo.sysjobhistory table and the SSMS Log File Viewer.
> On a related note, is there a way to determine how many records were
> affected by a job without modifying the stored procedure that was called?
> Thanks.
> Message
> Executed as user: USER. 1 SQL Agent jobs have failed on Server SERVER.
> Check SQL Agent History for additional details. [SQLSTATE 42000] (Error
> 50000). The step failed.
>|||I see. I ran a Profiler trace when expanding jobhistory (incl details) in SSMS, and SSMS executes
msdb.dbo.sp_help_jobhistory. This in turn executes sp_help_jobhistory_full, in which you can see
what joins and tables it is using. It seems that both overall and also step details are in the
jobhistory table...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<a> wrote in message news:OOybpRODIHA.4228@.TK2MSFTNGP02.phx.gbl...
> Thanks Tibor. This is just a sample error, I would think the details would be in the msdb
> database somewhere. I am working on a report to query the jobjistory tables for success and
> failure, and provide the details on the job so even if it says success, I need further
> verification. For instance if a job runs an SSIS package from the command line and the package
> fails, the job still reports success. I can find absolutely nothing on the web for the phrase
> "Check SQL Agent history for additional details" and thought I would try here.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:472E2179-D5A8-4D3A-8875-77EAA77FD0FF@.microsoft.com...
>>I guess you need to investigate what this job does and how to interpret the output from the job
>>through the one who wrote the job. The output states some information for you:
>> Sql Severity 16
>> Sql Message ID 50000
>> Above show you that the TSQL code executed by the job step returned error 50000 and severity 16.
>> The some text from whatever it was you executed:
>> Operator Emailed
>> Operator Net sent
>> Operator Paged
>> Retries Attempted 0
>> Above is probably the error message from the executed TSQL code. The name of the job step:
>> Step Name Notify via MOM when SQL Agent Jobs Fail
>> suggests that it has to do with MOM, so you probably want to read MOM documentation and/or ask in
>> a MOM group. A wild guess from my part is that whatever it is you execute tried to do a NET SEND
>> which fails (messagenr service is disabled by default nowadays).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> <a> wrote in message news:uJXuKDFDIHA.4196@.TK2MSFTNGP04.phx.gbl...
>> Yes I should have mentioned that was the details for the job step:
>> Date 10/11/2007 4:00:00 PM
>> Log Job History (DBA Scheduled Tasks - SQL Agent Failure Notifications)
>> Step ID 1
>> Server SERVERR
>> Job Name DBA Scheduled Tasks - SQL Agent Failure Notifications
>> Step Name Notify via MOM when SQL Agent Jobs Fail
>> Duration 00:00:01
>> Sql Severity 16
>> Sql Message ID 50000
>> Operator Emailed
>> Operator Net sent
>> Operator Paged
>> Retries Attempted 0
>> Message
>> Executed as user: USER. 1 SQL Agent jobs have failed on Server SERVERR. Check SQL Agent History
>> for additional details. [SQLSTATE 42000] (Error 50000). The step failed.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:751A094C-8F05-476B-920D-F1CAD8F1F8F4@.microsoft.com...
>> Did you navigate into the details for the job step?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> <a> wrote in message news:eB$l6BEDIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Thanks for the reply, that is where I got that message from re: the SSMS Log File Viewer
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:580D43DC-2CDF-49D0-AB8D-B5D005CFBB42@.microsoft.com...
>> Open up SSMS and go to SQL Server Agent node and expand Jobs, right click on your job and
>> select View History.
>> --
>> Ekrem Önsoy
>>
>> <a> wrote in message news:uKFxvQDDIHA.972@.TK2MSFTNGP05.phx.gbl...
>>> Where do I check for the additional details when the job fails? This is from both the
>>> msdb.dbo.sysjobhistory table and the SSMS Log File Viewer. On a related note, is there a way
>>> to determine how many records were affected by a job without modifying the stored procedure
>>> that was called?
>>>
>>> Thanks.
>>>
>>> Message
>>> Executed as user: USER. 1 SQL Agent jobs have failed on Server SERVER. Check SQL Agent
>>> History for additional details. [SQLSTATE 42000] (Error 50000). The step failed.
>>>
>>
>>
>>
>