Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast






Learn more about SQL Server tools








Learn more about SQL Server tools


   Got a SQL tip?
            We want to know!

Providing SQL Agent Job Log Data for Developers

MSSQLTips author Kun Lee By:   |   Read Comments (3)   |   Related Tips: More > SQL Server Agent
Problem

As a production Database Administrator, I do not want to give Developers direct access to the SQL Server Agent job log especially for servers in the DMZ. Another problem we have is that when there is a lot of log data for a job, the default job log doesn't contain the full log detail and that makes it harder to troubleshoot. Most of all, we are trying not to use a different code set for deployment based on the environment. Basically we want to use the same methods to deploy our jobs to Development, Test and Production.

Solution

I was able to solve the full detail logging issue with help from this article How to store longer SQL Agent Job Step output messages, which helps with troubleshooting, but still the problem was to provide the log to SQL developers. So we used an output file instead to capture more detail, you can take a look at this article Verbose SQL Server Agent Logging for more information.

Now using verbose SQL Agent logging, we can save the output file locally or to a shared folder and provide Read Only permissions for Developers. Another thing we did was to use SQL Tokens, so with the same code we could pick up the server name and other variables to dynamically generate the output file name.

What is SQL Token?

Here is the definition from books online. "SQL Server Agent allows you to use tokens in Transact-SQL job step scripts. 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".  You can find more information about Tokens here Use Tokens in Job Steps.

IMPORTANT NOTE: The format of Tokens was changed in SQL 2005 SP1 and later, so be aware of the formatting differences as shown below.

Pre-requisite - Turn on Token Replacement

Before you use Tokens, you must turn on the "Token Replacement" property.

You can easily do this from a T-SQL Script"

USE [msdb]]GO
EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens=1
GOGO

Or via SSMS, by right clicking on SQL Server Agent and selecting Properties and then change as shown below:

you can use SSMS and change SQL Agent Property

Implementation - SQL Server Management Studio

When you create a job step, click the "Advanced" page and you will see Transact-SQL script (T-SQL) Output file as shown below.

Here is an example job setup, notice the difference for SQL 2005 and higher and SQL 2000.

  • Server name: SQLTIP1\SQL2012A
  • Job name: DBA - SQL Token Testing
  • Step name: SQL Token Test
  • Output file format SQL 2005 SP1 or higher
    \\MYFILESERVER\SQLLogs\$(ESCAPE_NONE(MACH))_$(ESCAPE_NONE(INST))\SQLTokenTest_Step1_$(ESCAPE_NONE(DATE)).log
  • Output file format - SQL 2000
    \\Myfileserver\SQLLogs\[MACH]_[INST]\SQLTokenTest_Step1_[DATE].log
click the "Advance" and you will see T-SQL Script output file

The output file will be generated in \\Myfileserver\sqllogs\SQLTIP1_SQL2012A\ folder and as you can see I didn't need to specify the server name, but it was dynamically generated by the "\$(ESCAPE_NONE(MACH))_$(ESCAPE_NONE(INST))\" token.

And the file name also includes the [year], [month] and [day] as shown below.

Implementation - SQL Serer Management Studio

Implementation - T-SQL Script (SQL 2005 sp1 or higher)

Here is T-SQL code to create a job.  This script includes the Tokens, so that I can run this same script on multiple servers without having to make any changes and the tokens will be replaced with the actual values from the server when the job is run.

USE [msdb]]GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'DBA - SQL Token Testing', 
@enabled=1, 
@notify_level_eventlog=0, 
@notify_level_email=0, 
@notify_level_netsend=0, 
@notify_level_page=0, 
@delete_level=0, 
@description=N'SQL Token Testing', 
@category_name=N'[Uncategorized (Local)]', 
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'SQL Token Test', 
@step_id=1, 
@cmdexec_success_code=0, 
@on_success_action=1, 
@on_success_step_id=0, 
@on_fail_action=2, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'TSQL', 
@command=N'select @@version', 
@database_name=N'master', 
@output_file_name=N'\\MYFILESERVER\SQLLogs\$(ESCAPE_NONE(MACH))_$(ESCAPE_NONE(INST))\SQLTokenTest_Step1_$(ESCAPE_NONE(DATE)).log', 
@flags=0

Limitation when using SQLCMD and SQL Tokens

If you use SQLCMD mode from a SSMS query window you may see an error similiar to the below error. This is because you cannot mix SQLCMD mode with SQL Tokens.

KNOWN BUG for and WORKAROUND

This also applies if you use SQLCMD from the command line and try to use SQL Tokens as shown below.

If you are using the same version or lower for SQLCMD.exe, you also get below error

 

That's it. I hope this helps to manage your SQL servers. Also, I want to give the credit to my colleagues Robert and Ed as they started this process, so kudos to them.

Next Steps


Last Update: 1/23/2013


About the author
MSSQLTips author Kun Lee
Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Wednesday, January 23, 2013 - 6:28:42 PM - Ludwig Guevara Read The Tip

Hi,

It’s a nice Article, I check suggested articles: Verbose SQL Server Agent Logging and How to store longer SQL Agent Job Step output messages. The main Idea is to help a developer to know, what happen if a job fails during execution.

An easy Solution: Add an extra step to the job "If the job Fails" to monitor it. Create a store procedure with the below code, looking for last know job issue and send it by email to your developer.

SELECT TOP 1 CONVERT( varchar(MAX),
            REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
            REPLACE( REPLACE( jh.Message, '--','' ), '[SQLSTATE 01000]', '[SQL]' ), 'Progress', 'Prg' )
                    , 'Started', 'Sta' ), 'End Prg', 'EPrg' ), 'Executing query', 'Eqry' )
                    , 'Source', 'Src' ), '(Message 0)', '' ), '[SQLSTATE 40001] (Error 1205)', 'FStp' ) )
        FROM msdb.dbo.sysjobs j
        INNER JOIN msdb.dbo.sysjobhistory jh ON j.job_id=jh.job_id
        WHERE run_status = 0
            AND run_date = CONVERT(varchar, getdate(),112)
            AND run_time > REPLACE( CONVERT(varchar, DATEADD( n, - @Minutes, getdate() ),108), ':','')
            AND j.job_id=@JobID )


Tuesday, January 29, 2013 - 10:26:54 PM - Roshan Read The Tip

Great article.

Previously I have used sysjobhistory to pull the results to a centralized database for developers and other teams.

Tokens are new lerning to me.

Thanks  for sharing

 


Wednesday, August 14, 2013 - 9:17:24 AM - Hassan Parthasarathy Read The Tip

Nice article,

thanks!

Partha



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 

Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.