Implement SQL Server Agent Job Output Files

For SQL Server Agent Jobs we can setup text files to store the output of the job.

If we want to see the output of any log we normally use the below procedure for verification.

SSMS -- SQL Server Agent -- Job Name -- Properties -- Steps -- Edit --  Advanced

Click on View beside the log to table check box, It displays output of the job that is executed recently.

If there is any critical job we need to maintain daily job output in a separate file with date and time stamp, we can achieve with using SQL Server Tokens

Suppose we have job called DB_Integrity_Verification, follow the below steps to create a job output file using the below procedure.

SSMS -- SQL Server Agent -- DB_Integrity_Verification -- Steps -- Edit -- Advanced

In Output file Text Box paste the below syntax

C:\JobLogs\DB_Integrity_Verification_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt

Click on OK and close the job.

If you execute the job it creates a text file with date and time stamp in the C:\JobLogs folder, make sure JobLogs folder exists on C: Drive.