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.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s