November 6, 2006 at 12:29 pm
Hello all,
I have inherited a SQL Server that has a lot of databases on it, some of which I suspect are no longer needed.
To help me determine which are no longer needed, I set up a Profiler trace to tell me what activity is happening against them. Among other columns, this trace shows ApplicationName and HostName for each entry logged.
When I review the trace results, I see some entries that list an ApplicationName of
SQL Agent - TSQL JobStep (Job 0xAF02F1FDC9727245946920DDF2F0D5E2 : Step 1)
I searched msdb..sysjobs on that machine, but could find no entries with that ID.
I suspected that this Job ID could belong to a job running on a linked server, but the HostName property I see is the name of the local SQL Server.
And how can I tell if an entry in the trace is originating from a linked server?
How can I find the english name of the job that has that GUID? Am I looking in the wrong place? Or just the wrong server?
Thanks for your help!
Jason
November 6, 2006 at 1:34 pm
Look at the msdb..sysjobs
November 6, 2006 at 3:07 pm
"I searched msdb..sysjobs on that machine, but could find no entries with that ID."
November 6, 2006 at 3:36 pm
Ha! That is my fault. To find the name of job, we can run the following snippet.
SELECT * FROM msdb..sysjobs WHERE job_id='...'
Do you use master server to control jobs? If so, it is quite normal because jobs are created and launched from the master server.
November 6, 2006 at 4:20 pm
No worries, I appreciate the help.
I don't know the answer to that question, tho. Is there a way to determine from the server if it is a master or a target?
November 29, 2006 at 9:21 am
"JAR", did you manage to resolve this? Like you, I'm trying to link some SQL Profiler trace data with a specific job. We do not have any master/slave job configurations, and the guid value presented in the "Application Name" trace column
[example:
SQLAgent - TSQL JobStep (Job 0x9F1D96A456D1FF45BB72A6712D370244 : Step 5)
end example]
is not found in the job_id column of msdb..sysJobs.
(My best guess is that the SQL Agent application reads the job, generates a new guid, and uses that in an application name built and used for all connections for that job run instance. However, I can't figure out why they'd do that, as it makes no sense to me.)
Philip
December 4, 2006 at 3:57 pm
Are you asking how to determine a server is master or slave?
December 4, 2006 at 4:26 pm
Jar
The hostname is going to just give you the name of the server you are running the trace from. If you have a chance to run the trace and monitor it then run sp_who2 and that will tell you exactly what server is running that job.
Then you could look at sysjobs
I hope that helps
December 4, 2006 at 4:55 pm
select * from orginating_server where job_id = ''
If Orginating_server name is the master server name from where job originated...
MohammedU
Microsoft SQL Server MVP
September 26, 2011 at 12:15 pm
--BUILD LIST OF Captured SQL Agent Jobs from Profiler GUIDs
--ASSUMES THAT YOU HAVE A PROFILER TRACE TABLE "xxProfilerTraceTablexx"
--ALSO ASSUMES "xxProfilerTraceTablexx" IS ON SAME TABLE AS SQL Agent Jobs
IF EXISTS
(
SELECT * FROM tempdb.dbo.sysobjects
WHERE id = object_id(N'[tempdb].dbo.[#ProfilerSQLAgentJobs]')
)
DROP TABLE #ProfilerSQLAgentJobs ;
GO
CREATE TABLE #ProfilerSQLAgentJobs
(
JobID UNIQUEIDENTIFIER
,JobGUID VARCHAR(128)
,JobName VARCHAR(250)
) ;
GO
DECLARE @JobID VARCHAR(128),@sqlText NVARCHAR(400)
DECLARE curSQLAgentJobIDs CURSOR READ_ONLY FOR
SELECT
JobID = SUBSTRING(ApplicationName,CHARINDEX('Job 0x',ApplicationName)+4,34)
FROM dbo.xxProfilerTraceTablexx --SEE ASSUMPTIONS ABOVE
WHERE ApplicationName LIKE 'SQLAgent - TSQL %'
GROUP BY SUBSTRING(ApplicationName,CHARINDEX('Job 0x',ApplicationName)+4,34)
ORDER BY JobID
OPEN curSQLAgentJobIDs
FETCH NEXT FROM curSQLAgentJobIDs INTO @JobID
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sqlText =
N'
INSERT INTO #ProfilerSQLAgentJobs
(JobID,JobGUID,JobName)
SELECT job_id, ''' + @JobID + ''',[name] FROM msdb.dbo.sysjobs
WHERE job_id = CAST(' + @JobID + ' AS UNIQUEIDENTIFIER) ;
'
--PRINT @sqlText ;
EXEC sp_executesql @sqlText;
FETCH NEXT FROM curSQLAgentJobIDs INTO @JobID
END
CLOSE curSQLAgentJobIDs
DEALLOCATE curSQLAgentJobIDs
SELECT * FROM #ProfilerSQLAgentJobs
SELECT
NumRows = COUNT(*)
,a.DatabaseName
,a.LoginName
,EventDesc = b.[name]
,ApplicationName =
CASE
WHEN a.ApplicationName LIKE 'SQLAgent - TSQL %'THEN 'SQLAgent - ' + sj.JobName
ELSE a.ApplicationName
END
,a.ObjectName
FROM
dbo.xxProfilerTraceTablexx a --SEE ASSUMPTIONS ABOVE
INNER JOIN
sys.trace_events b
ON a.EventClass = b.trace_event_id
LEFT JOIN
#ProfilerSQLAgentJobs sj
ON SUBSTRING(a.ApplicationName,CHARINDEX('Job 0x',a.ApplicationName)+4,34) = sj.JobGUID
GROUP BY
a.DatabaseName
,a.LoginName
,b.[name]
,a.ApplicationName
,sj.JobName
,a.ObjectName
ORDER BY
NumRows DESC
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply