The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog

 

Home

No Ads ever, except search!
Monday, March 18, 2024 Login
Public

SQL Agent Token - Cycle Job Log 1/18/2018 8:16:54 AM

I guess SQL Server Tokens have been around since SQL 2005, but I just learned of them.

I needed this because I had a Transaction Log backup job, running every 4 minutes that was randomly failing.  The sql agent text was longer than was captured so I couldn't see the error in the log history, I went to the job to view the "output" file, and of course it was already overwritten by the next successful run.

I didn't want to change the job to "append" as I didn't want to have it grow "forever" and end up with a large job output text file on the server.

After some research I found this: 

http://mumblestiltskin.blogspot.com/2011/03/sql-server-job-step-output-file-using_08.html

https://docs.microsoft.com/en-us/sql/ssms/agent/use-tokens-in-job-steps 

So I just simply modified the output step to "append" and added the token:

O:\MSSQL11.BCTAX03\MSSQL\JOBS\SQLAGENT_JOB_Backup_TRN(ALWAYSON)_StepID-1_$(ESCAPE_SQUOTE(STRTDT)).txt 

This uses a token in the job output file to create a file for each day, so the job is still set to "append", but you get one job file per day.

To manage the "daily" log files, I have a job that creates a sub-directory by day (yymmdd) and "sweeps" all the log files into it.  So I have one subdirectory per day with all the log files in.   Once a year I delete some directories, here's the code that does that, the whole job is in my script vault as well, under SQL Agent.

declare @xp_command varchar(8000)

declare @directory varchar(8)

declare @path varchar(1000)

declare @month varchar(2)

declare @day varchar(2)

declare @year varchar(4)

set @month = Right('00' + convert(varchar(2),DatePart(m,getdate())),2)

set @day = right('00' + convert(varchar(2),DatePart(d,getdate())),2)

set @year = convert(varchar(4),DatePart(yyyy,getdate()))

set @directory = @month + @day + @year

print @directory

set @xp_command = 'mkdir G:\CY_SQL_JOB_LOGS\' + @directory

print @xp_command

exec master..xp_cmdshell @xp_command

set @xp_command = 'move G:\CY_SQL_JOB_LOGS\*.* G:\CY_SQL_JOB_LOGS\' + @directory

print @xp_command

exec master..xp_cmdshell @xp_command

That's all there is too it !


Blog Home