Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Thursday, 18 January 2018

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 !

Thursday, 18 January 2018 08:16:54 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server |  SQL Agent#
Comments are closed.
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
SQL Server Security, not where it n...
Pied Piper of Grandview II
Buick drops the ball, er...muffler
Recent Posts
Archive
June, 2018 (1)
May, 2018 (3)
April, 2018 (2)
February, 2018 (3)
January, 2018 (3)
November, 2017 (1)
October, 2017 (1)
August, 2017 (1)
June, 2017 (2)
May, 2017 (2)
April, 2017 (2)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Links
Categories
Admin Login
Sign In
Blogroll