For background information, see https://msdn.microsoft.com/en-us/library/ms175575(v=sql.110).aspx
Step 1. make sure SQL Server Agent is set to Replace Tokens (Properties|Alert System), need to stop and re-start to take effect for the change.
Step 2. the tokenized codes cannot be parsed by SSMS, the values are replaced at runtime.
Step 3. I created a job with 2 steps: (1) back up the log when the log is 70% full, and (2) audit the log backup information in an auditing table.
---create the audit table----
CREATE TABLE MyJobLog (
ServerName SYSNAME
, Instance SYSNAME
, JobId UNIQUEIDENTIFIER
, ErrorNumber INT
, ErrorSeverity INT
, ErrorDate DATETIME DEFAULT GETDATE()
, ErrorMessage NVARCHAR(MAX)
)
GO
---Insert the following code with tokens for job step 2------
INSERT MyJobLog (
ServerName
, Instance
, JobId
, ErrorNumber
, ErrorSeverity
, ErrorMessage
,ErrorDate
)
VALUES (
'$(ESCAPE_SQUOTE(A-SVR))'
, '$(ESCAPE_SQUOTE(INST))'
, $(ESCAPE_SQUOTE(JOBID))
, $(ESCAPE_SQUOTE(A-ERR))
, $(ESCAPE_SQUOTE(A-SEV))
, '$(ESCAPE_SQUOTE(A-MSG))'
, '$(ESCAPE_SQUOTE(DATE))'
)
---Use the following code to increase the log and make sure the log backup occurs when the performance condition is met-------
DBCC SQLPERF('LOGSPACE')
WHILE 1=1
INSERT DBO.BIGTABLE VALUES('1')
---check the job history, it should have two steps succeed--------
