Adding SQL Agent output to text file for all agent jobs in one go

As a best practice, i am trying to schedule an activity where all SQL agent jobs should log output to text file.

Manually i can do this for single job. But i am looking for a better way either by script or any good method i can make this change for multiple jobs about 80-90 jobs per server.

Moreover i have to do this in QA, UAT and prod, covering over 100 servers.

Please share you're experience or any helpful links how can this be achieved in best way as path of log files where output will be generated should be common across servers/jobs.

The msdb database contains a stored procedure that allows modifications to job steps in a wide variety of ways, including the ability to modify only the output properties.

The following code will output the commands required to modify all job steps that have no "output file" specified, such that job step output will be written into C:\temp\job_name_here.txt. Output will be overwritten each time the job step runs.

USE msdb;
DECLARE @OutputPath nvarchar(260);
DECLARE @cmd nvarchar(max);
SET @OutputPath = N'C:\temp';
DECLARE @msg nvarchar(1000);
SET @msg = N'
/*
flags: 
    0 - overwrite output file
    2 - append to output file
    4 - Write T-SQL output to step history in MSDB
    8 - Write log to table (overwrite existing)
    16 - Write log to table (append)
*/

';
PRINT @msg;
DECLARE cur CURSOR LOCAL FORWARD_ONLY LOCAL
FOR
SELECT N'EXEC msdb.dbo.sp_update_jobstep @job_id = N''{' + CONVERT(varchar(50), (sj.job_id)) + N'}''
    , @step_id = ' + CONVERT(nvarchar(30), sjs.step_id) + '
    , @output_file_name = N''' + @OutputPath + N'\' + sj.name + N'_Step_' + CONVERT(nvarchar(10), sjs.step_id) + N'.txt''
    , @flags = 0;

'
FROM dbo.sysjobs sj
    INNER JOIN dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id
WHERE sjs.output_file_name IS NULL OR sjs.output_file_name = ''
ORDER BY sj.name
    , sjs.step_id;
OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF LEN(@cmd)>4000 
    BEGIN
        SET @msg = 'Output of the next command is truncated.';
        RAISERROR (@msg, 14, 1);
    END
    PRINT @cmd;
    --EXEC sys.sp_executesql @cmd; --uncomment this line to actually run the command
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;

You could run this against multiple servers by creating a local server group in SSMS using the "Registered Servers" functionality, adding the desired servers to that group, then executing a query against the group.

StackExchange Database Administrators Q#168971, answer score: 4