To be able to design a robust Job in SQL Server, we need to have log for each step of the job. I know there are different options available which help us for troubleshooting a job, but what if we want to record the number of affected records during the step, as well as being able to see the error if anything goes wrong. We have tables below in msdb database which we have query to see the job history:
sysjobstepslogs sysjobhistory sysjobactivity
Also the option available in each job step (where we have specify a text file to record the logs):
But What is the best practice in this regard? How can we log the number of records affected during each step?
Thanks in advance.