This post I want to tell you about an error that just happened to me recently. It’s very silly, but still can make you waste some time trying to find out why.
Background
SQL Server comes with its own tasks scheduler for all paid versions (sorry, Express users) which is the SQL Agent. Obviously this comes very handy to do the different maintenance we normally undertake on our databases like backups, index and stats maintenance, consistency checks and other that can apply to your environment.
Inside a SQL Agent job step, we can use something called Tokens, and as BOL states
Using tokens when you write your job steps gives you the same flexibility that variables provide when you write software programs. After you insert a token in a job step script, SQL Server Agent replaces the token at run time, before the job step is executed by the Transact-SQL subsystem.
So in a simplified way, they are a sort of environment variables to be used within a job step.
I don’t have many use cases for this, but at least I have one 🙂
Use case
It’s usually a good idea to write the output of your SQL Agent jobs to a file, so you can investigate should any issue occur.
But when you define the output file, you need to choose between Appending the output to the same file over and over, or to overwrite it, but that defeats the purpose IMHO.
On the other hand, if you forget to roll over the files, they can grow quite large and then finding any error can become a nightmare.
So some time ago, I wrote a stored procedure that rolls the files for me and place them sorted so it’s easy to find any particular date.
The hack
The following stored procedure will do the trick for you, it will look at the output files for each of the steps and rename and place them within a subfolder so you can find them easily.
Obviously the structure of folders is how I like it ./Jobs/yyyy/mm/jobname/, but feel free to tweak it to suit your needs, maybe you prefer ./Jobs/jobname/yyyy/mm so that’s up to you.
If you leave it like it is, and you have a job like this
Then you will get something like this.
If you liked what you’ve seen, then here is the Stored Procedure, so you can copy paste and start having fun.
USE [master]
GO
IF OBJECT_ID('dbo.sqlg_renameJobLogFiles') IS NULL EXECUTE sp_executesql N'CREATE PROCEDURE dbo.sqlg_renameJobLogFiles AS RETURN';
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Raul Gonzalez @SQLDoubleG
-- Create date: 17/09/2013
-- Description: Rename Log Files for the specified job and place them into a folder
-- with the following pattern
-- [Drive:\Current Path]\Jobs\yyyy\mm\Step-N-StepName_yyyymmdd[_hhmiss].log
--
-- Usage:
-- Include the following call as the last step of a job
--
-- USE [master]
-- GO
-- -- tokens can only be used within job steps
-- DECLARE @job_id uniqueidentifier = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID)))
-- DECLARE @includeTime bit = 1
-- DECLARE @debugging bit = 0
-- EXECUTE [dbo].[sqlg_renameJobLogFiles]
-- @job_id = @job_id
-- , @includeTime = @includeTime
-- , @debugging = @debugging
-- GO
--
-- Asumptions: Each step outputs into a different file
--
-- Change Log: 30/03/2016 RAG - Added parameter @includeTime to add _hhmmss to the output filename.
--
-- Copyright: (C) 2017 Raul Gonzalez (@SQLDoubleG https://www.sqldoubleg.com)
--
-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
-- TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
-- PARTICULAR PURPOSE.
--
-- THE AUTHOR SHALL NOT BE LIABLE TO YOU OR ANY THIRD PARTY FOR ANY INDIRECT,
-- SPECIAL, INCIDENTAL, PUNITIVE, COVER, OR CONSEQUENTIAL DAMAGES OF ANY KIND
--
-- YOU MAY ALTER THIS CODE FOR YOUR OWN *NON-COMMERCIAL* PURPOSES. YOU MAY
-- REPUBLISH ALTERED CODE AS LONG AS YOU INCLUDE THIS COPYRIGHT AND GIVE DUE CREDIT.
--
-- =============================================
ALTER PROCEDURE [dbo].[sqlg_renameJobLogFiles]
@job_id UNIQUEIDENTIFIER
, @includeTime BIT = 0
, @debugging BIT = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @outputFiles TABLE (ID INT IDENTITY
, job_name SYSNAME
, step_no INT
, step_name SYSNAME
, output_file_name NVARCHAR(512) NOT NULL
, only_file_name NVARCHAR(512) NOT NULL)
DECLARE @numFiles INT
, @countFiles INT = 1
, @job_name SYSNAME
, @output_file_name NVARCHAR(512)
, @new_path NVARCHAR(512)
, @new_file_name NVARCHAR(512)
, @year VARCHAR(4) = DATEPART(YEAR,GETDATE())
, @month VARCHAR(2) = RIGHT('00' + CONVERT(VARCHAR,DATEPART(MONTH,GETDATE())), 2)
, @day VARCHAR(2) = RIGHT('00' + CONVERT(VARCHAR,DATEPART(DAY,GETDATE())), 2)
, @time VARCHAR(6) = REPLACE(CONVERT(VARCHAR,GETDATE(), 108), ':', '')
, @dirCmd NVARCHAR(2000)
, @mvCmd NVARCHAR(2000)
INSERT INTO @outputFiles
SELECT TOP 100 PERCENT
j.name
, js.step_id
, js.step_name
, output_file_name
, RIGHT( output_file_name, ( CASE WHEN CHARINDEX( CHAR(92), REVERSE(output_file_name) ) > 0
THEN CHARINDEX( CHAR(92), REVERSE(output_file_name) ) - 1
ELSE LEN(output_file_name)
END ) ) AS only_file_name
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobsteps AS js
ON js.job_id = j.job_id
WHERE j.job_id = @job_id
AND output_file_name IS NOT NULL
ORDER BY js.step_id
SET @numFiles = @@ROWCOUNT
WHILE @countFiles <= @numFiles BEGIN
SELECT @output_file_name = output_file_name
, @new_file_name = 'Step-' + CONVERT(VARCHAR,step_no) + '-' + step_name
, @new_path = REPLACE(output_file_name, only_file_name, '')
+ 'Jobs' + CHAR(92) + @year + CHAR(92) + @month + CHAR(92) + job_name + CHAR(92)
, @job_name = job_name
FROM @outputFiles
WHERE ID = @countFiles
-- Create folder if does not exist
SET @dirCmd = 'EXEC master..xp_cmdshell ''if not exist "' + @new_path + '". md "' + @new_path + '".'''
SET @mvCmd = 'EXEC master..xp_cmdshell ''move "' + @output_file_name + '" "'
+ @new_path + @new_file_name + '_' + @year + @month + @day
+ CASE WHEN @includeTime = 1 THEN '_' + @time ELSE '' END + '.log"'''
PRINT @dirCmd
PRINT @mvCmd
IF ISNULL(@debugging, 0) = 0 BEGIN
EXEC sp_executesql @dirCmd
EXEC sp_executesql @mvCmd
END
SET @countFiles += 1
END
END
GO
The final thing is to add a call to this stored procedure within your different jobs as the final step, the call is in the header section but it’d be like this
USE [master] GO -- tokens can only be used within job steps DECLARE @job_id uniqueidentifier = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID))) DECLARE @includeTime bit = 1 DECLARE @debugging bit = 0 EXECUTE [dbo].[sqlg_renameJobLogFiles] @job_id = @job_id , @includeTime = @includeTime , @debugging = @debugging GO
The gotcha!
Almost forgot! All this came for a reason, the use of tokens. Yes, there is no much juice using tokens, you can check books online to see what is available, I just use JOBID in my final job step.
The thing is that I have scripts to create jobs for new servers and I try to use SQLCMD to make it easier so I don’t have to bother connecting to the right server, the script will do it for me.
But when creating a job I come to this error.
/* A fatal scripting error occurred. Variable JOBID is not defined. */
And that was very annoying, because I couldn’t understand why, but seems like SQLCMD does not like tokens very much.
I can’t find any reference in BOL, but just in case you come to the same error, just run that bit locally, or maybe powershell would work? I let you try that.
Thanks for reading!
just beware of Microsoft changing things up.
we got caught when we went through an upgrade project from SQL 2000 to SQL 2005 came out.
(they changed things up from SQL 2000).
who knows when it will happen again !
Thanks for your comment!, very true, although Microsoft always try not to break anything and keep backwards compatibility, sometimes happen.
Cheers!