Checking to see if a sqlagent job is running?

Hi all,
I am starting with the very basics to check to see if a job is running
before I kick off another job. I go to my dev server, see that a job is
running through EM, open query analyzer and run:
Use msdb
go
select run_status from sysjobhistory
where run_status = 4
and it returns 0 rows. I go back to EM and refresh the job and it is still
running. It's history says it runs for 10 minutes or so each run. According
to BOL I should see at least one row for this job that is running. Can
someone show me the error of my ways? Thanks. BTW, I have tried searching on
this topic, but have not come up with anything very helpful.
Jackie
Jackie,
Try:
EXECUTE MSDB.DBO.SP_HELP_JOB @.EXECUTION_STATUS = 1
HTH
Jerry
"Jackie Brophy" <jbrophy@.mathworks.com> wrote in message
news:u3ekuDnzFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I am starting with the very basics to check to see if a job is running
> before I kick off another job. I go to my dev server, see that a job is
> running through EM, open query analyzer and run:
> Use msdb
> go
> select run_status from sysjobhistory
> where run_status = 4
> and it returns 0 rows. I go back to EM and refresh the job and it is still
> running. It's history says it runs for 10 minutes or so each run.
> According to BOL I should see at least one row for this job that is
> running. Can someone show me the error of my ways? Thanks. BTW, I have
> tried searching on this topic, but have not come up with anything very
> helpful.
> Jackie
>

Checking to see if a sqlagent job is running?

Hi all,
I am starting with the very basics to check to see if a job is running
before I kick off another job. I go to my dev server, see that a job is
running through EM, open query analyzer and run:
Use msdb
go
select run_status from sysjobhistory
where run_status = 4
and it returns 0 rows. I go back to EM and refresh the job and it is still
running. It's history says it runs for 10 minutes or so each run. According
to BOL I should see at least one row for this job that is running. Can
someone show me the error of my ways? Thanks. BTW, I have tried searching on
this topic, but have not come up with anything very helpful.
JackieJackie,
Try:
EXECUTE MSDB.DBO.SP_HELP_JOB @.EXECUTION_STATUS = 1
HTH
Jerry
"Jackie Brophy" <jbrophy@.mathworks.com> wrote in message
news:u3ekuDnzFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I am starting with the very basics to check to see if a job is running
> before I kick off another job. I go to my dev server, see that a job is
> running through EM, open query analyzer and run:
> Use msdb
> go
> select run_status from sysjobhistory
> where run_status = 4
> and it returns 0 rows. I go back to EM and refresh the job and it is still
> running. It's history says it runs for 10 minutes or so each run.
> According to BOL I should see at least one row for this job that is
> running. Can someone show me the error of my ways? Thanks. BTW, I have
> tried searching on this topic, but have not come up with anything very
> helpful.
> Jackie
>

Checking to see if a sqlagent job is running?

Hi all,
I am starting with the very basics to check to see if a job is running
before I kick off another job. I go to my dev server, see that a job is
running through EM, open query analyzer and run:
Use msdb
go
select run_status from sysjobhistory
where run_status = 4
and it returns 0 rows. I go back to EM and refresh the job and it is still
running. It's history says it runs for 10 minutes or so each run. According
to BOL I should see at least one row for this job that is running. Can
someone show me the error of my ways? Thanks. BTW, I have tried searching on
this topic, but have not come up with anything very helpful.
JackieJackie,
Try:
EXECUTE MSDB.DBO.SP_HELP_JOB @.EXECUTION_STATUS = 1
HTH
Jerry
"Jackie Brophy" <jbrophy@.mathworks.com> wrote in message
news:u3ekuDnzFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I am starting with the very basics to check to see if a job is running
> before I kick off another job. I go to my dev server, see that a job is
> running through EM, open query analyzer and run:
> Use msdb
> go
> select run_status from sysjobhistory
> where run_status = 4
> and it returns 0 rows. I go back to EM and refresh the job and it is still
> running. It's history says it runs for 10 minutes or so each run.
> According to BOL I should see at least one row for this job that is
> running. Can someone show me the error of my ways? Thanks. BTW, I have
> tried searching on this topic, but have not come up with anything very
> helpful.
> Jackie
>

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 Jobs Status through Query

I am a Junior DBA and i have to checks the various jobs on different servers.Please help me with a T-SQL way by which i can check the Job status through a Query.

Thanks in Advance

Jacx

you can use opennrowset or openquery to

query other servers

job information are stored in msdb and you can invoke the

following to query job information

use msdb
select * from sysjobs
select * from sysjobhistory

|||

tually i wanted a code to find the urrent job status of a particular job which i am inerested in. Kindly help me with that.

Thanks

Jacx

|||

use msdb
select * from sysjobs sj join --<change the * to get only the columns you need
sysjobhistory sjh
on sj.job_id=sjh.job_id
where name like 'W%' <modify this for the job name

check the run status

check this link

http://msdn2.microsoft.com/en-gb/library/ms174997.aspx

Checking Job Status using T-SQL

Hi.

How can i check the job status using SQL Query?

The following query may help you...

Select
*
From
msdb..sysjobhistory as sysjobhistory
Join msdb..sysjobs as sysjobs on sysjobhistory.job_id=sysjobhistory.job_id
Where
Name='Your Schd. Package Name'
Order By
Run_Date Desc,
run_time Desc

|||Look up using [sp_help_job] in Books Online.|||

by the way....

What is meant by JOB STATUS ?

|||

Raja, I meant If the job (last execution/historical) executed successfully or not..From the above query..

KangKang, use RUN_Status column to find the status

1=Executed Successfuly

0=Failed with error

When Failed you can find the details from the Message Column

|||

I get too many recrod when i use this method

Select
DISTINCT run_status
From
msdb..sysjobhistory as sysjobhistory
Join msdb..sysjobs as sysjobs on sysjobhistory.job_id=sysjobhistory.job_id
Where
name = 'STR Balance'

I just want to get the current run_stauts and store it in a variable. I have refer to SP_Help_Job but i do not know how can i return the run_Status

|||

actually i just want to return the current status, i tried but it return the new and old execute status.

|||I tryto change the SP_HELP_JOB but its too complicated, Is there any other method so i can insert all things into temp table and select current_status from the temp table.|||

I guess I don't understand why you think sp_help_job is so complicated.

You give it a job name, and it gives you a status. Actually, very simple.

|||

YA actually is very easy.....I should try more before post...:).

|||

The problem with using sp_help_job in a batch is that you can't return the results to a table. This is because sp_help_job itself returns results to a table as part of its processing, and

An INSERT EXEC statement cannot be nested.

|||

I have modify the sp_help_job and the sp_get_composite_job_info to retrun only 1 value.

:)

Checking job status using SQL-DMO

I have a question I hope someone can help me with.

My situation:
I have a single-step job in SQL Server, which runs a stored procedure, A. This stored procedure invokes another stored procedure, B. In B, one of the statements is a 'BACKUP DATABASE' command, and a database is backed up to a file.

The job is started by an application. Once started, the application then uses the SQL-DMO property CurrentRunStatus to periodically check the status of the job. When the property returns the value SQLDMOJobExecution_Idle (indicating the job has completed), the application code then continues processing, and attempts to access the .dat file produced by the 'BACKUP DATABASE' command.

My problem:
On occasion, the application will hit the problem where either the backup file cannot be located, or the file is still being locked by another process (Error=The process cannot access the file because it is being used by another process).

Is anyone able to shed some light on this?

I assume that the job will only return a completed status after:
(a) both A and B have completed execution, and
(b) the BACKUP operation has completed

Is it possible that even though SQL Server indicates the job has finished, that the BACKUP operation still hasn't completely ended?

Thanks for any help,
AndrewDoesn't anyone have any ideas?

Should I be posting this elsewhere instead?|||Will VB/VBA (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_ex02_1gv7.asp) do?

-PatP

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.

checking for control files in SSIS

have a job that loads data from a data file into a table.

Is there a "task" that can be used to check to see if a file exists

and put error handling around it without programming ?

have you considered using the file watcher task? http://www.sqlis.com/default.aspx?23

Checking at the end of the Cursor if error ocuured

I have a scheduled job which runs daily at 7:00 am. Now, this job has a
cursor in it whcih updates and inserts some tables depending on the conditions
i want to make an entry into a table called monitor (which tracks if the job
ran successfully or not) checking if the job ran successfully or not. Please
advice if their is a way to check if an error occurred or not
thanks
samay
You could create one job step that would only execute if the job step that
runs the statement fails. That job step (lets call it "run on failure")
could do whatever processing or notification that you desire if the job step
fails.
Keith
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:6C8C2715-F6BB-49AF-A5CE-C69260E666CF@.microsoft.com...
> I have a scheduled job which runs daily at 7:00 am. Now, this job has a
> cursor in it whcih updates and inserts some tables depending on the
conditions
> i want to make an entry into a table called monitor (which tracks if the
job
> ran successfully or not) checking if the job ran successfully or not.
Please
> advice if their is a way to check if an error occurred or not
> thanks
> samay

Checking at the end of the Cursor if error ocuured

I have a scheduled job which runs daily at 7:00 am. Now, this job has a
cursor in it whcih updates and inserts some tables depending on the conditions
i want to make an entry into a table called monitor (which tracks if the job
ran successfully or not) checking if the job ran successfully or not. Please
advice if their is a way to check if an error occurred or not
thanks
samayYou could create one job step that would only execute if the job step that
runs the statement fails. That job step (lets call it "run on failure")
could do whatever processing or notification that you desire if the job step
fails.
--
Keith
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:6C8C2715-F6BB-49AF-A5CE-C69260E666CF@.microsoft.com...
> I have a scheduled job which runs daily at 7:00 am. Now, this job has a
> cursor in it whcih updates and inserts some tables depending on the
conditions
> i want to make an entry into a table called monitor (which tracks if the
job
> ran successfully or not) checking if the job ran successfully or not.
Please
> advice if their is a way to check if an error occurred or not
> thanks
> samay

Checking at the end of the Cursor if error ocuured

I have a scheduled job which runs daily at 7:00 am. Now, this job has a
cursor in it whcih updates and inserts some tables depending on the conditio
ns
i want to make an entry into a table called monitor (which tracks if the job
ran successfully or not) checking if the job ran successfully or not. Please
advice if their is a way to check if an error occurred or not
thanks
samayYou could create one job step that would only execute if the job step that
runs the statement fails. That job step (lets call it "run on failure")
could do whatever processing or notification that you desire if the job step
fails.
Keith
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:6C8C2715-F6BB-49AF-A5CE-C69260E666CF@.microsoft.com...
> I have a scheduled job which runs daily at 7:00 am. Now, this job has a
> cursor in it whcih updates and inserts some tables depending on the
conditions
> i want to make an entry into a table called monitor (which tracks if the
job
> ran successfully or not) checking if the job ran successfully or not.
Please
> advice if their is a way to check if an error occurred or not
> thanks
> samay

Check Whether Or not a File is a New One

Hi,

I need to set up a package to copy a file from a network share to a server. Before the copy job, I need to check whether the file, e.g. test.txt, is created within one day, if not, then check again an hour later.

Did you search this forum? I know this question has been asked a couple times before; I just could fine this one:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=789950&SiteID=1

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 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.
>>>
>>
>>
>>
>

Check Server Disk Space Daily

I know can execute perfmon program to determine disk space on the server.
I would like a simple SQL Server job (SQL Server 2000) I can set up to run
once a day. Check all drives on the server if unused disk space is less than
10% then send alert message.
Please help me with this task.
See if this helps.
Using xp_fixeddrives to Monitor Free Space
http://www.databasejournal.com/features/mssql/article.php/3080501
AMB
"Joe K." wrote:

> I know can execute perfmon program to determine disk space on the server.
> I would like a simple SQL Server job (SQL Server 2000) I can set up to run
> once a day. Check all drives on the server if unused disk space is less than
> 10% then send alert message.
> Please help me with this task.
|||Grab sp_diskspace from here http://www.sqldbatips.com/showcode.asp?ID=4 and
simply insert results into a table and run your check against it.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:1C11A458-4317-40F0-A4E5-0C07520C88D5@.microsoft.com...
> I know can execute perfmon program to determine disk space on the server.
> I would like a simple SQL Server job (SQL Server 2000) I can set up to run
> once a day. Check all drives on the server if unused disk space is less
> than
> 10% then send alert message.
> Please help me with this task.

Check Server Disk Space Daily

I know can execute perfmon program to determine disk space on the server.
I would like a simple SQL Server job (SQL Server 2000) I can set up to run
once a day. Check all drives on the server if unused disk space is less than
10% then send alert message.
Please help me with this task.Grab sp_diskspace from here http://www.sqldbatips.com/showcode.asp?ID=4 and
simply insert results into a table and run your check against it.
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:1C11A458-4317-40F0-A4E5-0C07520C88D5@.microsoft.com...
> I know can execute perfmon program to determine disk space on the server.
> I would like a simple SQL Server job (SQL Server 2000) I can set up to run
> once a day. Check all drives on the server if unused disk space is less
> than
> 10% then send alert message.
> Please help me with this task.

Check Server Disk Space Daily

I know can execute perfmon program to determine disk space on the server.
I would like a simple SQL Server job (SQL Server 2000) I can set up to run
once a day. Check all drives on the server if unused disk space is less tha
n
10% then send alert message.
Please help me with this task.See if this helps.
Using xp_fixeddrives to Monitor Free Space
http://www.databasejournal.com/feat...cle.php/3080501
AMB
"Joe K." wrote:

> I know can execute perfmon program to determine disk space on the server.
> I would like a simple SQL Server job (SQL Server 2000) I can set up to run
> once a day. Check all drives on the server if unused disk space is less t
han
> 10% then send alert message.
> Please help me with this task.|||Grab sp_diskspace from here http://www.sqldbatips.com/showcode.asp?ID=4 and
simply insert results into a table and run your check against it.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:1C11A458-4317-40F0-A4E5-0C07520C88D5@.microsoft.com...
> I know can execute perfmon program to determine disk space on the server.
> I would like a simple SQL Server job (SQL Server 2000) I can set up to run
> once a day. Check all drives on the server if unused disk space is less
> than
> 10% then send alert message.
> Please help me with this task.