4

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.

3

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.

  • Thanks for the script, it looks good. But i have a concern here. If we overwrite the file, how can we see the error that might would have happen in previous run for eg: job running every hour. That would overwrite the message and we would not be able to see the error details – BeginnerDBA Apr 3 '17 at 18:13
  • Also if we use flags=2 to append the file, how can we maintain the growth for those files. Just curious which option works here and how to manage – BeginnerDBA Apr 3 '17 at 18:13
  • @BeginnerDBA - if you overwrite the file you cannot see history. If you append to the file, you must manually monitor the size of the file to ensure it doesn't become so large as to (a) cause poor performance and (b) consume all available space on the drive. There are no automated methods for managing the output files directly in SQL Server Agent. – Max Vernon Apr 3 '17 at 18:18
  • thanks :) I have accepted other answers. I never noticed that. Moreover when i use the above script , it seems to give duplicate execute commands where the job has say multiple steps like below – BeginnerDBA Apr 3 '17 at 18:57
  • for eg: repeats` **EXEC msdb.dbo.sp_update_jobstep @job_id = N'{A380B4C9-1228-48F9-9719-08CA7E19E37A}' , @output_file_name = N'D:\JobLogs\syspolicy_purge_history.txt' , @flags = 0; ** `3 times for each different step – BeginnerDBA Apr 3 '17 at 18:59
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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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