SQL Server Performance

How to get job id

Discussion in 'General DBA Questions' started by sramesh, Nov 9, 2006.

  1. sramesh New Member

    Hi all
    this is my script

    FROM master.dbo.sysprocesses
    WHERE program_name like 'SQLAgent - TSQL JobStep%'

    i got the program name like this

    SQLAgent - TSQL JobStep (Job 0xCDD5865A3C51D911936200508BAD07B9 : Step 1)

    How to get this real value?
    CDD5865A3C51D911936200508BAD07B9 --- i need this redable name

    S. Ramesh
  2. dineshasanka Moderator

    check sysjobs table of msdb db

    Contributing Editor, Writer & Forums Moderator

    Visit my Blog at
  3. sramesh New Member

    My concept is collecting long running jobs.
    thats why i asking the above question

    S. Ramesh
  4. sonnysingh Member

  5. sonnysingh Member

  6. sramesh New Member

    Hi<br />thanks all..<br /><br />i findout my self. check my script<br />This is i want.<br /><br /><br /><br /><br /><br /><br />alter PROCEDURE LongRunningJobs<br /><br />as<br />/***********************************************************************************************<br />Author: Ramesh S<br />Date: 10th November 2006<br />Purpose: Findout long running jobs<br />***********************************************************************************************/<br /><br />--- Declare local variable<br />DECLARE @JobNamevarchar(8000)<br />DECLARE @Durationint<br />DECLARE @Job varchar(1000)<br />DECLARE @Tmp varchar(1000)<br />DECLARE @Job1varchar(1000)<br />DECLARE @Iint<br />DECLARE @Jint<br />DECLARE @Kint<br /><br />--- Create a temp table for storing jobs<br />CREATE TABLE #Jobs (JobID varchar(8000), Duration int)<br /><br />--- Declare cursor <br />DECLARE CurJobs CURSOR FOR<br />SELECT LTRIM(SUBSTRING(P.program_name,32,35)) as JobName,<br />datediff(MS, P.login_time, getdate())/1000 as Duration <br />FROM master..sysprocesses P<br />WHERE --P.status = 'sleeping' and<br />P.program_name LIKE '%SQLAgent - TSQL JobStep%'<br /><br />OPEN CurJobs<br /><br />FETCH NEXT FROM CurJobs INTO @JobName, @Duration<br />---- Now we got program name like this<br />---- SQLAgent - TSQL JobStep (Job 0x12570D0B81EF0C408F82D1198954726B : Step 2) <br />---- Over goal is get the job id<br />---- 0B0D5712-EF81-400C-8F82-D1198954726B = 0x12570D0B81EF0C408F82D1198954726B<br />---- Now we convert 0x12570D0B81EF0C408F82D1198954726B to 0B0D5712-EF81-400C-8F82-D1198954726B<br />---- 0x12570D0B81EF0C408F82D1198954726B = 0x 12570D0B-81EF-0C40-8F82-D1198954726B<br />----0B0D5712-EF81-400C-8F82-D1198954726B<br /><br />WHILE @@FETCH_STATUS = 0 <br />BEGIN<br />------ TO GET JOB NAME<br />SET @Job = ''<br />SET @J = 3<br />WHILE @J &lt; 4 AND @J &gt; 0<br />BEGIN<br />IF @J = 1<br />BEGIN<br />SET @Tmp = SUBSTRING(@JobName, 1,<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />SET @I = 8<br />SET @K = 9<br />END<br />ELSE<br />BEGIN<br />SET @Tmp = SUBSTRING(@JobName, 4 * (@J)+1 , 4 )<br />SET @I = 4<br />SET @K = 5<br />END<br />SET @Job1 = ''<br />WHILE @I &lt; @K AND @I&gt;0<br />BEGIN<br />SET @Job1 = @Job1 + SUBSTRING(@Tmp,@I-1, 2) <br />SET @I = @I - 2<br />END<br />SET @J = @J - 1<br />SET @Job = @Job1 + '-'+ @Job --+ CASE @J WHEN <br />END<br />SELECT @Job = @Job + SUBSTRING(@JobName,17 , 4 )+ '-'+ SUBSTRING(@JobName,21 , 12 )<br />print @Job<br />INSERT INTO #Jobs (JobID, Duration )<br />VALUES (@Job, @Duration) <br /><br />FETCH NEXT FROM CurJobs INTO @JobName, @Duration<br />END<br /><br />CLOSE CurJobs<br />DEALLOCATE CurJobs<br />---- Get long running jobs<br />SELECT SJ.Name, P.Duration, AVG(SJH.run_duration) as AvgDuration, P.JobID<br />FROM #Jobs P<br />JOIN MSDB..sysjobhistory SJH ON SJH.job_id = P.JobID <br />JOIN MSDB..sysjobs SJ ON SJ.job_id = SJH.job_id<br />WHERE SJH.step_id = 0<br />AND SJH.run_status = 1<br />GROUP BY SJH.job_id, SJ.name, P.Duration, P.JobID<br />HAVING P.Duration &gt; AVG(SJH.run_duration)<br /><br />DROP TABLE #Jobs<br /><br /><br />Thanks<br />S. Ramesh
  7. Andrey.Vegger New Member

    This is the script that might help you to recover your job information:DECLARE
    @JobName varchar(max)SELECT @JobName = [name]
    FROM msdb.dbo.sysjobsWHERE job_id = cast(0xCDD5865A3C51D911936200508BAD07B9 AS uniqueidentifier)
    msdb..sp_help_job @job_name = @JobNameEXECUTE
    msdb..sp_help_jobstep @job_name = @JobName

    hi Andrey,

    Its fantastic. Working for me..... I really wonder your post helps me even after 4 years....

Share This Page