Agent no longer terminates on error

It is funny how you keep going, assuming things are as they used to be. Sometimes.

I had this thing with SQL Server agent and T-SQL job step types. It used to be that such a job step would just stop if an error occurs. This isn’t how other tools like SSMS or SQLCMD behaves. So to get the behavior that we are used to, we would schedule our SQL using a CmdExec job step and call out to SQLCMD.EXE.

I usually point to Ola Hallengren’s great maintenance scripts as an example of this. I recently noticed that he nowadays uses T-SQL jobstep when he call his stored procedures. That made me curious, and he told me in an email conversation that Agent doesn’t stop on error anymore. Since SQL Server 2012! Hehe, so it has been a while.

I wanted to test this, of course. Not that I doubt Ola, but seeing is believing. So here’s the T-SQL I use in a job step to test this:

RAISERROR('10', 10, 1)
RAISERROR('11', 11, 1)
RAISERROR('12', 12, 1)
RAISERROR('13', 13, 1)
RAISERROR('14', 14, 1)
RAISERROR('15', 15, 1)
RAISERROR('16', 16, 1)
RAISERROR('17', 17, 1)
RAISERROR('18', 18, 1)
PRINT 'Do we get to here?'
SELECT 1/0
PRINT 'What about after division by zero?'

If you run above from SSMS you probably see the messages out of sequence. You don’t see that in the output from the agent job step. I don’t know why that is. I added WITH NOWAIT for the RAISERROR messages (the old trick to flush the output buffer), but that din’t change the order SSMS displays the messages. SQL Server agent do show the messages in the expected order, however, so I won’t dig further in that.

Bottom line is that Agent doesn’t stop on (normal) errors anymore. I tested this on SQL Server 2017, but I don’t doubt Ola when he says that he tested this back to 2012.

Leave a Reply

Your email address will not be published.