Sql-server – What started this SQL Server job

sql serversql-server-2005sql-server-2008-r2sql-server-agent

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.

Best Answer

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.

Related Question