5

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.

2 Answers 2

4

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.

1
  • got that, thanks a much. This very much works for now with a concern for historical logs. Also, i just found this jasonstrate.com/2010/06/whyd-that-job-fail using token. This was something new , though not sure how can i use this with you're above approach to list out the scripts and then decide which jobs are good to make the changes. Apr 3, 2017 at 19:17
2

Given:

multiple jobs about 80-90 jobs per server.

and:

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

You should really look into Multiserver Administration, which allows for setting up a "master" server to be the one where you add / remove / change jobs (and schedules) and then any number of "targets" that get those definitions.

  1. Create a Multiserver Environment (on Microsoft.com)
  2. Stairway to SQL Server Agent - Level 12: Using MSX/TSX to scale out Job Management (on SQLServerCentral.com; free registration is required to read their content).
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.