SQL Server Job logging best practice

Question

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):

Output file 

But What is the best practice in this regard? How can we log the number of records affected during each step?

Thanks in advance.

Answer 1

Each job step has a setting that allows it's output to be sent to a text file. This can be cumbersome because it's a text file located in the file system and not in a table and might be hard to get to. There is also an option to send the output of a job step to a table. You can query the table, but it's still clunky. (It is hard to write a query to pick the "X rows affected" string out of a blob of text, then pull the number out so that you can aggregate it, etc.)

I would recommend that you build a simple table in your user database (not in msdb or master) to keep that information. IMO, this is the best way to keep close track of the number of rows affected by each command, particularly if you want to be able to write simple queries to do historical reporting. This way, you can decide to log at the job step level, or if the step calls a stored procedure with multiple commands, you can log a audit record after each command. As always, @@ROWCOUNT is your friend.