May 13, 2014 at 9:03 am
I am looking at trace file output and in the column "ApplicationName" some of the records are like:
SQLAgent - TSQL JobStep (Job 0x7EC56A11F6993B4A94B17626C88470F4 : Step 31)
But if I look in msdb.sysjobsteps to try and find out what job it is, the ID format there is
9C32B8B4-3CCD-42FD-A972-0221DEAF83F9
How can I identify the SQL job that is shown in the trace file ?
SQL 2005 sp4
May 14, 2014 at 4:34 am
Solution:
I slightly modified Ulysses Brown's solution from here
http://www.sqlservercentral.com/Forums/Topic320752-5-1.aspx
giving me this:
--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.Trace_201404_27 --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
-- Display trace file with job name
select job.JobName, textdata, applicationname, *
from xxProfilerTraceTablexx TR
join #ProfilerSQLAgentJobs Job on Job.JobGUID = SUBSTRING(tr.ApplicationName,CHARINDEX('Job 0x',tr.ApplicationName)+4,34)
where databasename like 'My_Database%'
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply