August 28, 2009 at 2:49 pm
I have SQL Server 2005 and one of my daily job fails everyday and i need to see the kob log. can someone tell me where are the job logs save or steps to see the job logs? please i am a newbie for SQL Server.
August 28, 2009 at 5:37 pm
SSMS
-Object Explorer
--SQL Server Agent
---Jobs
---RightClik the Job
-----View History for the Logs
-----Properties to see/edit the Steps
-MarkO
"You do not really understand something until you can explain it to your grandmother" - Albert Einstein
August 29, 2009 at 2:22 pm
thanks a lot. and is there any other way to see the log, or aqny other location where the logs are saved?
August 29, 2009 at 2:41 pm
HI
MSDB is the database where all this info is saved in.
you may use sp_help_job to see job info or use similar procedures, or you can stroll through the different tables in MSDB to see what you're seeking.
Can you be more percise and define what you are trying to achieve so I can help you with a code sample?
August 30, 2009 at 5:43 am
You can get job logs from msdb..sysjobhistory table
Modify the below code with your need
select b.name, run_date, run_time , message from msdb..sysjobhistory a, msdb..sysjobs b
where a.job_id = b.job_id
and b.name like 'JOB NAME'
order by run_date desc , run_time desc
In sysjobhistory table step_id = 0 represent final output, so if you are interested in that output alone put it in where clause, or mostly we are not interested in final output but other step output in that case put step_id 0
You can also put a "top 2" or any number to see just the last output.
August 30, 2009 at 10:17 am
but when i run that script it displays 0 rows. but is there any other place where i can see the detailed logs. I mean the actual l,ogs. please help me...............
August 30, 2009 at 10:33 am
Those are the actual logs. MSDB is where SQL Agent writes all job-related logs. If there's nothing in the table, it means that either somone's deleted them or SQL Agent is set with a low retention for job history.
I'd avoid directly querying MSDB, the structure's a little weird in places. Use the job history window from Management Studion. Right click the job and select Job History.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 30, 2009 at 10:38 am
I did a right click job--then view history and saw the job. below is the failed description.
Error message
Executed as user: IHS\SqlAgent. Invalid length parameter passed to the SUBSTRING function. [SQLSTATE 42000] (Error 536). The step failed.
but was looking for the actual log and then i ran the script given above
select b.name, run_date, run_time , message from msdb..sysjobhistory a, msdb..sysjobs b
where a.job_id = b.job_id
and b.name like 'JOB NAME'
order by run_date desc , run_time desc
(no modifications were made to the script ran as it is above)
and returned 0 rows.
and i need this history very badly. if someone can help out.
August 30, 2009 at 10:54 am
espanolanthony (8/30/2009)
select b.name, run_date, run_time , message from msdb..sysjobhistory a, msdb..sysjobs bwhere a.job_id = b.job_id
and b.name like 'JOB NAME'
order by run_date desc , run_time desc
(no modifications were made to the script ran as it is above)
and returned 0 rows.
Is the job in question really named 'JOB NAME'? If not (and I highly doubt it is) you need to replace 'JOB NAME' with the actual name of the job in question.
and i need this history very badly. if someone can help out.
You got it from the job history window. Querying MSDB isn't going to return anything more. You have the reason the job failed, what more are you looking for?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 30, 2009 at 10:59 am
yes sir when i changed the job name from the real job name that i had it gave me the same result that i see in the job history. so that is the only one way am i right?
August 30, 2009 at 11:10 am
Yes. The job history window reads from the MSDB database. There's only one place the logs are stored. Hence you'll get the same results from the query as from the job history window.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 30, 2009 at 1:41 pm
You are getting error while trying to see the history or this is the error logged in job history. Both are different scenarios . Please clarify. I think your job is failing and you are getting that error as output of job step.
August 30, 2009 at 11:00 pm
Mind that you're calling the job by its name while management tools refer to the job by its GUID....
One way to overcome this difference is:
1. Run a profiler on MSDB
2. Use the management tool to view job history
3. Stop the profiler trace and see what sql commands were fired.
This will help you get going
Regards,
August 31, 2009 at 1:07 am
For a more detailed log, this is what you can do:
- Right click on the job, select properties
- In the "Select a page" window, select "Steps"
- Click "Edit"
- In the "Select a page" window, select "Advanced"
- In the window on the right there is a phrase: "Output file:"
- Fill in a path and name and after executing the job the step will log information to this file.
If you have more steps, repeat this for every step.
HTH
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply