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

    SELECT*
    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



    Thanks
    S. Ramesh
  2. dineshasanka Moderator

    check sysjobs table of msdb db

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
  3. sramesh New Member

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


    Thanks
    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)
    EXECUTE
    msdb..sp_help_job @job_name = @JobNameEXECUTE
    msdb..sp_help_jobstep @job_name = @JobName
  8. VISHNU DHARIPALLY New Member

    hi Andrey,

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

Share This Page