SQL Server Agent Job and SQLCMD

Have you ever encountered missing database backup or maintenance plan didn't complete as it was scheduled, especially error was encountered? This post discusses a behavior difference between SQL Server agent using T-SQL type and SQLCMD with CmdExec type.

SQL Server agent job is widely used to execute scheduled SQL Server tasks like backup, maintenance, SQL stored procedures or scripts as well as non-SQL tasks like executing program.

When creating a SQL Server job, a job step(s) define the functionality or task it performs. SQL Server job step provides different types (subsystems). Here listed some of the commonly used types (subsystems),

Transact-SQL script (T-SQL)
Operating System (CmdExec)
PowerShell
SQL Server Analysis Service (SSAS) command / query
SQL Server Integration Services (SSIS) package

T-SQL type is the most common job step type. It is used to execute T-SQL Script or stored procedures. CmdExec is used to run program, utility. batch files and etc. PowerShell type is used to execute PowerShell script within the job step command. SSAS type is used to execute SSAS XMLA script to backup SSAS or batch processing. SSIS type is used to execute SSIS package including maintenance plan created through SSMS.

Let also discuss briefly about the SQLCMD utility. SQLCMD utility allows us to run T-SQL statement in command prompt. This nice little utility allows administrator to perform SQL Server execution without bringing up the SSMS. Often this tool is used for quick admin check or some automated tasks.

Now back to SQL Agent job. When executing T-SQL command or stored procedure (SP) through SQL Server Agent, most of time the T-SQL type in the job step is selected. This type is best for most of the functionality. However, there is one behavior executing T-SQL or SP this way when an error is encountered during the execution. Let's examine. Here is a simple example,

Create a stored procedure to perform backup for multiple databases.

USE [master];
GO

CREATE PROCEDURE dbo.backupsp
AS
BACKUP DATABASE [msdb] TO DISK = 'msdb.bak';
BACKUP DATABASE [nono] TO DISK = 'nono.bak';
BACKUP DATABASE [master] TO DISK = 'master.bak';
GO

There is no 'nono' database. The second backup statement should throw an error.

Now we create a SQL Agent job with job step type T-SQL to execute this stored procedure.

USE [msdb]
GO

EXEC dbo.sp_add_job
  @job_name=N'Test Job (TSQL)',
  @enabled=1;

EXEC dbo.sp_add_jobstep
  @job_name=N'Test Job (TSQL)',
  @step_name=N'Test Step (TSQL)',
  @subsystem=N'TSQL',
  @command=N'EXEC [master].dbo.backupsp;',
  @flags=4;

EXEC dbo.sp_add_jobserver
  @job_name=N'Test Job (TSQL)', @server_name = N'(LOCAL)';
GO

Start the job,

EXEC dbo.sp_start_job @job_name = N'Test Job (TSQL)';
GO

SQL Server 2014 and SQL Server 2012 job step detail
Executed as user: NT SERVICE\SQLSERVERAGENT. Processed 1728 pages for database 'msdb', file 'MSDBData' on file 3. [SQLSTATE 01000] (Message 4035)  Processed 7 pages for database 'msdb', file 'MSDBLog' on file 3. [SQLSTATE 01000] (Message 4035)  BACKUP DATABASE successfully processed 1735 pages in 3.124 seconds (4.337 MB/sec). [SQLSTATE 01000] (Message 3014)  Database 'nono' does not exist. Make sure that the name is entered correctly. [SQLSTATE 08004] (Error 911)  BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)  Processed 472 pages for database 'master', file 'master' on file 2. [SQLSTATE 01000] (Message 4035)  Processed 2 pages for database 'master', file 'mastlog' on file 2. [SQLSTATE 01000] (Message 4035)  BACKUP DATABASE successfully processed 474 pages in 0.534 seconds (6.933 MB/sec). [SQLSTATE 01000] (Message 3014).  The step failed.

SQL Server 2008 R2 and earlier job step detail
Executed as user: NT AUTHORITY\NETWORK SERVICE. Processed 1856 pages for database 'msdb', file 'MSDBData' on file 2. [SQLSTATE 01000] (Message 4035)  Processed 2 pages for database 'msdb', file 'MSDBLog' on file 2. [SQLSTATE 01000] (Message 4035)  BACKUP DATABASE successfully processed 1858 pages in 2.716 seconds (5.344 MB/sec). [SQLSTATE 01000] (Message 3014)  Database 'nono' does not exist. Make sure that the name is entered correctly. [SQLSTATE 08004] (Error 911)  BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.

You may have already notice that in SQL Server 2008 R2 and earlier version, there is no backup for master database. The SQL agent job is terminated when it encounters an error. This is likely not the intended goal in this scenario. All other database backup should be performed even if there an error was encountered.

In order to mitigate this issue, we could utilize the sqlcmd with job step CmdExec within the SQL Agent job.

USE [msdb]
GO

EXEC dbo.sp_add_job
  @job_name=N'Test Job (CmdExec)',
  @enabled=1;

EXEC dbo.sp_add_jobstep
  @job_name=N'Test Job (CmdExec)',
  @step_name=N'Test Step (CmdExec)',
  @subsystem=N'CmdExec',
  @command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -Q "EXEC [master].dbo.backupsp" -b';

EXEC dbo.sp_add_jobserver
  @job_name=N'Test Job (CmdExec)', @server_name = N'(LOCAL)';
GO

The -b option at the end of command returns a DOS ERRORLEVEL value when an error is encountered within the sqlcmd. This give us a better indication of the job failure. The SQL Server Agent token (SRVR) returns the computer and SQL Server instance name.

Start the job,

EXEC dbo.sp_start_job @job_name = N'Test Job (CmdExec)';
GO

SQL Server 2014, SQL Server 2012, SQL Server 2008 R2 job step detail
Executed as user: NT Service\SQLSERVERAGENT. Processed 1728 pages for database 'msdb', file 'MSDBData' on file 4.  Processed 7 pages for database 'msdb', file 'MSDBLog' on file 4.  BACKUP DATABASE successfully processed 1735 pages in 2.613 seconds (5.186 MB/sec).  Msg 911, Level 16, State 11, Server SQL2014, Procedure backupsp, Line 5  Database 'nono' does not exist. Make sure that the name is entered correctly.  Msg 3013, Level 16, State 1, Server SQL2014, Procedure backupsp, Line 5  BACKUP DATABASE is terminating abnormally.  Processed 472 pages for database 'master', file 'master' on file 3.  Processed 2 pages for database 'master', file 'mastlog' on file 3.  BACKUP DATABASE successfully processed 474 pages in 0.656 seconds (5.644 MB/sec).  Process Exit Code 1.  The step failed.

Except the executing user difference (WORKGROUP\SQL2008R2$ for SQL Server 2008 R2), master database backup is performed for all SQL Server versions. Also, if you pay close attention, with sqlcmd, there is no [SQLSTATE 08004] (Error 911) funky stuff at the end of each execution like T-SQL type does.

Observation
For earlier SQL Server version, SQL Server agent job terminates immediately when it encounters error. This may produce unintended results as all the subsequent statements are not executed. This may become potential issue for backup, integrity check or index maintenance operations as it misses the rest of the executions.

One way to mitigate this behavior is to utilize the sqlcmd with CmdExec type within the SQL Server agent job. Sqlcmd complete the entire executions even if it encounters error.

From SQL Server 2012 and later version, SQL Server agent job appears to complete all execution regardless if it encounters error.