Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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