You are currently viewing SQL Agent job logging with tokens
Microsoft SQL Server

SQL Agent job logging with tokens

A useful feature in the SQL Agent is the possibility to use tokens. Tokens are a number of system variables (e.g. $(ESCAPE_SQUOTE(DATE) that contains the current date) that gets expanded/replaced with a value when the job is run. An obvious use of tokens is to create a new log file with today’s date and time in the file name each time an SQL Agent job is run.

For a complete list of tokens, see MSDN.

Removed tokens

Note that, as of SQL Server 2012, the tokens AGENT_JOB_NAME and AGENT_STEP_NAME seems to be removed for some reason.

Alert tokens

Tokens that are prefixed with an A- will not expand unless the job is activated by an alert. Further, these tokens are disabled by default for security reasons. If you call them when they are disabled, the job will fail with a similar error message:

[red_box]
Unable to start execution of step 1 (reason: Variable A-DBN not found). The step failed.
[/red_box]

To enable them, use SQL Server Management Studio. Right click SQL Server Agent, select Properties and select the page Alert System. Check the Replace tokens for all job responses to alerts checkbox:

Replace tokens for all job responses to alerts
Replace tokens for all job responses to alerts

Alternatively, run:

EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens = 1

Note that it may take a while for this change to have effect. If you have the possibility, restart the SQL Agent service to make the change immediately.

Tokens demo

Perhaps the best way to demonstrate tokens, is to create a SQL Agent job that expands all of them. So here it is:

USE msdb
GO

DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job
	@job_name = 'Print Tokens Job',
	@enabled = 1,
	@job_id = @jobId OUTPUT

EXEC msdb.dbo.sp_add_jobstep
	@job_id = @jobId,
	@step_name = 'Print tokens step',
	@step_id = 1,
	@subsystem = N'TSQL', 
	@command = N'
	PRINT ''*********Alert Tokens*********''
	PRINT ''Database name: $(ESCAPE_SQUOTE(A-DBN))''
	PRINT ''Server name: $(ESCAPE_SQUOTE(A-SVR))''
	PRINT ''Error number: $(ESCAPE_SQUOTE(A-ERR))''
	PRINT ''Error severity: $(ESCAPE_SQUOTE(A-SEV))''
	PRINT ''Error message: $(ESCAPE_SQUOTE(A-MSG))''

	PRINT ''*********General Tokens*********''
	--PRINT ''Agent job name: $(ESCAPE_SQUOTE(AGENT_JOB_NAME))'' --Removed in SQL 2012?
	--PRINT ''Step name: $(ESCAPE_SQUOTE(AGENT_STEP_NAME))'' --Removed in SQL 2012?
	PRINT ''Current Date: $(ESCAPE_SQUOTE(DATE))''
	PRINT ''Instance: $(ESCAPE_SQUOTE(INST))''
	PRINT ''Job Id: $(ESCAPE_SQUOTE(JOBID))''
	PRINT ''Computer name: $(ESCAPE_SQUOTE(MACH))''
	PRINT ''Master SQLServerAgent service name: $(ESCAPE_SQUOTE(MSSA))''
	PRINT ''Prefix for the program used to run CmdExec job steps: $(ESCAPE_SQUOTE(OSCMD))''
	PRINT ''SQL Server installation directory: $(ESCAPE_SQUOTE(SQLDIR))''
	PRINT ''SQL Server error log directory: $(ESCAPE_SQUOTE(SQLLOGDIR))''
	PRINT ''No times the step has executed (ex retries): $(ESCAPE_SQUOTE(STEPCT))''
	PRINT ''Step Id: $(ESCAPE_SQUOTE(STEPID))''
	PRINT ''Computer name: $(ESCAPE_SQUOTE(SRVR))''
	PRINT ''Current Time: $(ESCAPE_SQUOTE(TIME))''
	PRINT ''The time the job began executing: $(ESCAPE_SQUOTE(STRTTM))''
	PRINT ''The date the job began executing: $(ESCAPE_SQUOTE(STRTDT))''

	PRINT ''*********Goodbye!*********''
	', 
	@database_name = N'tempdb',
	@output_file_name = N'$(ESCAPE_SQUOTE(SQLLOGDIR))\Print_Tokens_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt'

EXEC msdb.dbo.sp_add_jobserver
	@job_id = @jobId,
	@server_name = N'(local)'

If you run this job, a log file will be created in the SQL Server error log folder path. The name of the file will be “Print_Tokens_YYYYMMDD_HHMMSS.txt”. The content of the file will look something like this:

SQL Server Agent log file
SQL Server Agent log file

The text “SQLSTATE 01000” can be ignored.

Tomas Lind

Tomas Lind - Consulting services as SQL Server DBA and Database Developer at High Coast Database Solutions AB.

This Post Has 5 Comments

  1. Alex

    Tomas-

    I randomly tried out an idea about tokens for job name and step name. As noted the AGENT_JOB_NAME and AGENT_STEP_NAME tokens do not work, but I reviewed the list of tokens on BOL. We have JOBID and STEPID, so I thought what if MS updated the others similarly? So I tried JOBNAME and STEPNAME in SQL Server 2016 SP1 and they work.

    $(ESCAPE_SQUOTE(JOBNAME))
    $(ESCAPE_SQUOTE(STEPNAME))

  2. Lee Linares

    Excellent script. However I ran into a problem when the job failed with this error:
    Unable to start execution of step 1 (reason: Variable SQLLOGDIR not found). The step failed.

    Apparently, SQLLOGDIR is not recognized in SQL 2012 SP4 or older. The same goes for JOBNAME and STEPNAME.

    It all works great in SQL 2014 SP1 though. Thanks for taking the time to make this available.

    Lee

  3. Lee Linares

    Sorry, a typo on my part. I meant to say it all works great in SQL 2016 SP1. In SQL 2014 JOBNAME and STEPNAME do not work but SQLLOGDIR does. Thanks again.

    Lee

  4. Stefan p

    If i’m missing the value for
    PRINT ”SQL Server installation directory: $(ESCAPE_SQUOTE(SQLDIR))”
    what could be the reason? Where does the agent get thoose values from? The registry?

  5. I noticed that if I have a SQL Agent job with an output file defined as in your example:

    @output_file_name = N’$(ESCAPE_SQUOTE(SQLLOGDIR))\Print_Tokens_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt’

    If I run the SQL Agent job after 10AM, I do indeed get an output file in the format: Print_Tokens_YYYYMMDD_HHMMSS.txt

    But if I run the SQL Agent job before 10AM, when the Hour portion of the TIME has leading zeroes, it strips off the leading zeroes in the file name.

    If I run the SQL Agent job between midnight and 12:59AM, I get an output file in the format: Print_Tokens_YYYYMMDD_MMSS.txt.

    If I run the SQL Agent job between 1AM and 9:59AM, I get an output file in the format: Print_Tokens_YYYYMMDD_HMMSS.txt.

    For jobs that run every 5 minutes, I do indeed get a separate output file for each execution, but they end up out of order in the folder. I was wondering if you know of a way to preserve the leading zeroes in the $(ESCAPE_SQUOTE(STRTTM))

Leave a Reply