Tuesday, October 13, 2009

Script to append the output file for SQL Agent job logging.

SQL Agent has a provision to log output to an external file. This is called "verbose logging" and this helps in proper management of multiple SQL Agent jobs.
Below is the script by which an output file can be pointed for logging as per the job is configured (either on failure, success or both).

script:

--Note: first create a folder called "Job_logs" in K:\

USE msdb
go

declare @bkpath varchar(300)
Set @bkpath = 'K:\job_Logs\' --- sets the path to the folder created

update sysjobsteps
set output_file_name = @bkpath + REPLACE(j.name, '''', '') + ' - ' + s.step_name + '.txt'
from sysjobsteps s, sysjobs j
where s.job_id = j.job_id and output_file_name is null


--Check if the path pointed is added correctly:

USE msdb ;
GO
EXEC dbo.sp_help_job
@job_name = N'', -- specify the job name
@job_aspect = 'steps' ; -- tells the output_file_name
GO

Hope this helps !

Thanks,

\\K
This blog is for SQL server and ArcSDE administration. Thanks for visiting !

Disclaimer: The contents provided on this blog are as is and the poster assumes no responsibility in case of any loss of data. Please test thoroughly the solution or codes provided in your environment.