4

I'm looking at a SQL Server environment in which jobs start in several different ways. One way is from a job running on a different SQL Server.

So, for example, "SQL Server A" runs a job named "DoStuff". One step in "DoStuff" is to run the job "MoreStuff" on "SQL Server B".

If I were looking at a problem on "SQL Server B", how would I answer the question, "What started 'MoreStuff'?"

The Job Properties "General" page has nothing about any external source.

The Application log records what "MoreStuff" is doing, but it doesn't seem to have any information about what started "MoreStuff". Other classic logs and newer Applications and Services Logs don't seem to have anything relevant in them. (I could have missed something.)

I'll take information on Server 2008 R2 and up, and on SQL Server 2005 and up.

4
  • 1
    You could create a trace of SQL:BatchStarting and RPC:Starting starting events, with a filter on sp_start_job.
    – Dan Guzman
    Commented Jul 3, 2015 at 22:13
  • Have you checked sysjobhistory where step_name = '(Job outcome)'? The message may tell you who / what started the job. Commented Jul 4, 2015 at 0:09
  • sysjobhistory only shows the login name that made the request, which isn't going to help if it's sa or a domain account (it won't tell you the workstation it came from, unless it's a local Windows account, and it won't tell you whether it was an application, a stored procedure, T-SQL in a query window, a job, etc.). Commented Jul 4, 2015 at 18:15
  • Yes, it will only tell you a login, but if that login is the user account that runs jobs on SQL Server B, then at lest you know it was started by a job on B...and if it is a regular user account, then you know who... Commented Jul 5, 2015 at 23:11

1 Answer 1

1

Well you can try to run a trace (if it's possible for you). But you can also try to just write it to your SQL Server Agent Job History instead.

I tried to write a little bit of code which can be added as a first step in front of all other steps. It will simply write a line "Executed from [hostname]".

I couldn't test it, due to the fact that I have just one instance on this machine, and I normally use it for trainings and won't misconfigure it just to have another instance. And it's late too. :-)

You can try this code:

DECLARE @host_name nvarchar(255)

SELECT @host_name = host_name
FROM sys.dm_exec_sessions AS ses
LEFT JOIN sys.servers as ser
        ON ses.host_name = ser.name
WHERE session_id = @@SPID -- only current session
    AND ser.name IS NULL -- only local connections

-- remote call
IF @host_name IS NULL BEGIN
    DECLARE @sql nvarchar(max), @server_name nvarchar(max),
            @ParmDefinition nvarchar(max) = N'@hn nvarchar(255) OUTPUT'

    SELECT @server_name = QUOTENAME(ser.name)
    FROM sys.dm_exec_sessions AS ses
    LEFT JOIN sys.servers as ser
            ON ses.host_name = ser.name
    WHERE session_id = @@SPID -- only current session
        AND ser.name IS NOT NULL -- only remote connections

    SET @sql = N'
        SELECT @hn= host_name
        FROM sys.dm_exec_requests AS ses
        WHERE wait_resource LIKE = N''%'+@@SERVERNAME+'%'+CONVERT(nvarchar(max),@@SPID) +N'%'''

    -- Encapsulate EXEC, to run it dynamically on another server
    EXEC (N'DECLARE @host_name nvarchar(255);
            EXEC '+@server_name+'.master.dbo.sp_executesql @sql = N'''+@sql+''', 
                @ParmDefinition = N'''+@ParmDefinition+''', 
                @hn = @host_name OUTPUT;
            PRINT(N''Executed at ''+@host_name)')

END
-- local call
ELSE BEGIN
    PRINT(N'Executed from '+@host_name)
END

Well, what does it do? It tries to check if the session is a local one or a session from a linked server. If it's a local one, it will just print out the host_name from the caller. If the caller is another server, it will take a look at the other server and find the SPID which executes the current session and will print the host_name from the remote server.

I don't know if it work that way, it need some test. Alongside with that it can be extended to look dynamically to the next server if it's a remote call too. (e.g. ServerA calls ServerB calls ServerC calls ServerD (which is the entry point)).

Hopefully it will help you and give you a working base.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.