October 29, 2009 at 8:08 am
I need to create a store proc which will monitor my sql jobs and write those auditing information on a sql table.
here is the senario, I created a table with five fields
servername, dbname, jobname, action, datetime
I need to insert 2 records one when job starts and one when job ends. action column is a datetime field which will track the job start time and job end time. and datetime field is just a get date. everytime my job is success I need to run this store proc on next step to gather the job log information on the sql table. how do i create such store proc
October 29, 2009 at 8:15 am
Why are you planning to do this, when there are tables in MSDB that store the same information (Infact more Info)
What are you planning to do with this information you want to store?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 29, 2009 at 8:25 am
well this is my manager requirement, he want to track the jobs record from that table, if he need an information of one month ago then it would be easy from him
October 29, 2009 at 8:27 am
Read these pages From Books On Line (BOL) and see if these techniques will provide you with the information you are seeking.
How to: Set Up the Job History Log (SQL Server Management Studio)
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/018e5c49-d3a0-4504-851a-f70996a34bb7.htm
How to: View the Job History (SQL Server Management Studio)
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3bbd1556-abdb-48a3-b249-546eace76343.htm
How to: Clear the Job History Log (SQL Server Management Studio)
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/34b9398a-c409-4040-8ea1-0deceb18f961.htm
How to: Resize the Job History Log (SQL Server Management Studio)
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ddee1ce8-9d1b-4017-9894-bf7256aed95d.htm
You may also want to read these:
sys.dm_exec_background_job_queue_stats
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/27f62ab5-46c4-417e-814d-8d6437034d1c.htm
sys.dm_exec_background_job_queue
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/05d9884f-b74c-4e3c-a23b-c90c1ea5ef02.htm
October 29, 2009 at 9:54 am
thanks for those links but I need to write a store proc to put those job info on my sql table.
October 29, 2009 at 10:33 am
You mean you want the entire Stored Procedure script ? or you need some help? if so what is that you are looking for?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 29, 2009 at 11:29 am
It would be great if you provide me a script. thanks
October 29, 2009 at 11:56 am
Since this is not an urgency, I guess you can wait and I can try posting a script in later in the day. I am running short of time to do it, as I am working on many other issues at work.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 29, 2009 at 12:02 pm
Great I would appriciate if you could provide me the script till tommorow at 5pm
October 30, 2009 at 1:59 am
Format your select and put where clause as you want. All the information that you want is returned. Build a sproc around this. (using insert and select)
Select *
from msdb.dbo.sysjobs SJ
JOIN sysjobsteps SJS ON SJ.job_id = SJS.job_id
JOIN sysjobschedules SJSC ON SJSC.Job_id = SJ.Job_ID
JOIN SYSSchedules SS ON SJSC.Schedule_id = SS.Schedule_id
WHERE SJ.Enabled = 1
Order by SJ.NAME DESC
---------------------------------------------------------------------------------
October 30, 2009 at 5:38 am
Hi Abhishek,
As I had promised you Here is the Script, I am sorry if I was late in replying, as it was very late last night by the time I touched your work.
SELECT SJ.Name AS JOBNAME, SJH.STEP_NAME, SJH.run_date AS DATE, SJH.run_time AS [TIME],SJH.RUN_STATUS,
CASE SJH.RUN_STATUS
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
ELSE 'In progress'
END
FROM MSDB.dbo.sysjobs SJ
INNER JOIN MSDB.dbo.sysjobhistory SJH
ON SJ.JOB_ID = SJH.JOB_ID
WHERE SJH.STEP_ID <> 0 ---- Always StepID is the Final Step of the Job, which in not a step created but a mere summary sort of record in the table.
This is the script for your Stored Proc to start with and on top of this I know you may want some thing else, let me know that and I would be glad to assist.
In fact as I already told you yesterday, I had done more than just storing the execution timings in my previous / Current Projects.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 30, 2009 at 11:59 am
thank you very much sir, I need lil bit diffrent, let me explain you my senario, I have created a table with 4 field, i.e servername, jobname, starttime, endtime
now i need to insert on those four filed as soon as my job is successfull, i dont care about failure.
suppose i have one job name 'abcd' if this job will successfully run it will go to next step and call that store procedure and we provide jobname as a parameter to the job so after executing this store proc it will insert the required logs on my table on all 4 fields. And I am running all jobs on a same server so i think we dont require declaring parameter for servername. but the one you send me before is giving me a good idea a learning process. thanks once again.
thank you
October 30, 2009 at 12:15 pm
Use this and write something on top of it if needed.
SELECT DISTINCT JH.[SERVER] SERVER_NAME, SJ.[Name] AS JOBNAME,
SJA.start_execution_date, SJA.stop_execution_date
FROM MSDB.dbo.sysjobhistory JH
INNER JOIN MSDB.dbo.sysjobs SJ
ON JH.JOB_ID = SJ.JOB_ID
INNER JOIN MSDB.dbo.sysjobhistory SJH
ON SJ.JOB_ID = SJH.JOB_ID
INNER JOIN msdb.dbo.sysjobactivity SJA
ON SJA.JOB_ID = SJH.JOB_ID
WHERE SJH.RUN_STATUS = 1
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 30, 2009 at 1:08 pm
here i am trying to create a store proc, I am getting an error, please let me know where i am wrong.
I need to pass @p_ijob_name parameter as job name to execute the store proc.
alter PROC P_Job_Audit(@p_ijob_name varchar(50))
AS
declare @abhisek varchar (50)
set @abhisek=@p_ijob_name
BEGIN
SET NOCOUNT ON
INSERT INTO job_audit
SELECT DISTINCT JH.[SERVER] SERVER_NAME, SJ.[Name] AS JOBNAME,
SJA.start_execution_date, SJA.stop_execution_date
FROM MSDB.dbo.sysjobhistory JH
INNER JOIN MSDB.dbo.sysjobs SJ
ON JH.JOB_ID = SJ.JOB_ID
INNER JOIN MSDB.dbo.sysjobhistory SJH
ON SJ.JOB_ID = SJH.JOB_ID
INNER JOIN msdb.dbo.sysjobactivity SJA
ON SJA.JOB_ID = SJH.JOB_ID
WHERE SJH.RUN_STATUS = 1
October 30, 2009 at 1:16 pm
Please post the Create Table script for the table which you are using to insert the values and also the error what you are getting for quicker responses
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply