SQL Server Agent – Output File Name – Tokenization

Background

I have always been perplexed by the fact that Sql Server Management Studio does not allow us to indicate that we desire timestamp file names for SQL Server Agent Jobs Logging.

SSMS

SQL Server Agent

Job Step Properties

Job Step Properties – Advanced

Initial
Image

Explanation
  1. Output file

 

Usage

Yesterday, I found out that timestamps are actually supported.

General – Image

Advanced – Image

Advanced – Textual

E:\Microsoft\SQLServer\SQLServerAgent\Log\DatabaseBackupFiles_$(ESCAPE_SQUOTE(JOBID))$(ESCAPE_SQUOTE(STEPID))$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt

 

Payload

cleanupFilesBak.cmd

Script


@echo off

REM How to use multiple commands in batch using forfiles command
REM https://stackoverflow.com/questions/23318731/how-to-use-multiple-commands-in-batch-using-forfiles-command

setlocal

set "_folderBase=G:\Microsoft\SQLServer\Backup"
set "_SQLInstance=%computername%"
set "_folderTarget=%_folderBase%\%_SQLInstance%\"

set "_fileExt=*.bak"

set "_daysPast=1"

set _appPgm=echo 

REM Added period (.) after folder name for /p argument
if not exist "%_folderTarget%." mkdir "%_folderTarget%"

forfiles /P "%_folderTarget%." /s  /m %_fileExt% /d -%_daysPast% /c ^"cmd /c ^
%_appPgm% ^
  file name is @PATH ^
  file size is @FSIZE ^
  file date is @FDATE ^
^"

endlocal

Output

Explanation

  1. DatabaseBackupFiles_[JobID]_[stepID]_[DateYYYYMMDD]_[timeHHMMSS].txt

 

Tokens

Token Description Version Active
A-DBN Active Database name
(A-SVR) SQL Server Name
(JOBNAME) The name of the Job Not active as of v2014/SP2
(STEPNAME) The name of the step Not active as of v2014/SP2
(DATE) Current date (in YYYYMMDD format).
(INST) Instance name. For a default instance, this token will have the default instance name: MSSQLSERVER.
(JOBID) Job ID
(MACH) Computer name
(SQLDIR) The directory in which SQL Server is installed. By default, this value is C:\Program Files\Microsoft SQL Server\MSSQL.
(SQLLOGDIR) Replacement token for the SQL Server error log folder path – for example, $(ESCAPE_SQUOTE(SQLLOGDIR)).
(STEPID) Step ID.
(SRVR) Name of the computer running SQL Server. If the SQL Server instance is a named instance, this includes the instance name.
(TIME) Current time (in HHMMSS format).
(STRTTM) Current time (in HHMMSS format).
(STRTDT) The date (in YYYYMMDD format) that the job began executing.

 

Dedicated

Dedicated to Ola Hallengren as I found out about this functionality through his script.

And, also to Dave Benham.

Dave has a nice explanation on how to escape commands in batch files and thus span multiple lines.

His explanation is here and it is in response to a Stackoverflow question.  The Question is titled “How to use multiple commands in batch using forfiles command“.

 

Listening

Listening to “Drifting Spirits” @ Teavolve.

Link

References

  1. Docs / SQL / SSMS / Agent
    • Use Tokens in Job Steps
      Link
  2. Thomas Lind
    • SQL Agent job logging with tokens
      Link

 

SQL Server Agent Jobs and extraneous contents in logged messages

Background

This morning I needed to dig into a SQL Server Job that was failing, but unfortunately the error was not being exposed\surfaced.

 

Message

Message – Image

Message – Text

Executed as user: SQLADMIN. Jul 11 2017 12:19PM – Transaction Begin …. [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Transaction Began [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Preparing adding records into [UserAcct].[UserActivity] … [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Adding records into [UserAcct].[UserActivity] 0 [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Update records into [UserAcct].[UserActivity] 0 [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Committing Transaction [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Committed Transaction [SQLSTATE 01000] (Message 0). The step succeeded.

 

Explanation

  1. If we take a deep look into the messages pasted above, we will notice multiple occurrence of “[SQLSTATE 01000] (Message 0)

 

Q/A

Googled and found a very helpful comment from Tibor Karaszi.

Tibor Karaszi

How do I suppress [SQLSTATE 01000] messages in sp output?

Link

 

Troubleshooting

SQL Profiler

Ran SQL Server Profiler and here is a screenshot:

Image

 

Outline

  1. SQLAgent – Generic Refresher
  2. SQLAgent – Job Manager
  3. SQLAgent – TSQL JobStep ( Job 0x####…. : Step 1)
    • — network protocol: LPC
      set quoted_identifier on
    • set quoted_identifier off
    • select @@microsoftversion
    • select convert(sysname, serverproperty(N’servername’))
    • SELECT ISNULL(SUSER_SNAME(), SUSER_NAME())
    • SET TEXTSIZE 1024
    • print ‘hello’

Detail

Hello

Hello

  1. Application Name :- SQLAgent – TSQL jobstep ( Job 0x )
  2. NTUserName :- SQLServerAgent
  3. LoginName :- NT Service\SQLServerAgent
  4. Client Process ID :- 2636

 

Task Manager

Reviewed “Task Manager” and confirmed that the process name for process ID is SQLAgent.exe

Remediate

SQL Server Agent Step

Current Job Step Configuration

Revised Job Step Configuration

Changes

  1. Type
    • Original :- Transact-SQL Script (T-SQL)
    • Revised :- Operating System (CmdExec)
  2. Command
    • Original :- exec [csdemo].Products.UpdateCertificatesUserDataAutoConfirm
    • Revised :- sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d demo -Q “exec [csdemo].[Products].[UpdateCertificatesUserDataAutoConfirm]” -b

Explanation

We changed from the internal Transact SQL Script tooling built into SQL Server Agent unto sqlcmd.exe

Sqlcmd exposes a lot more functionality.

 

Recap

Logged Messages

Logged Messages – Original

Logged Messages – Revised

 

Dedicated

Can’t go anywhere but to Tibor Karaszi, SQL Server MVP.

Like Eli Manning an MVP.