How to get job id

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

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


Thanks
S. Ramesh

read the following pages....

http://www.sql-server-performance.com/tt_sysprocesses_table_1.asp
http://support.microsoft.com/default.aspx?scid=kb;en-us;243589&sd=tech#appliesto
http://www.databasejournal.com/features/mssql/article.php/3500276

This is useful software for this kind of activities
http://www.microsoft.com/downloads/...c7-4d98-4c2b-bf72-ec2b4ae69191&DisplayLang=en

I hope these information enough to get you want you looking for..

read the following pages....

http://www.sql-server-performance.com/tt_sysprocesses_table_1.asp
http://support.microsoft.com/default.aspx?scid=kb;en-us;243589&sd=tech#appliesto
http://www.databasejournal.com/features/mssql/article.php/3500276

This is useful software for this kind of activities
http://www.microsoft.com/downloads/...c7-4d98-4c2b-bf72-ec2b4ae69191&DisplayLang=en

I hope these information enough to get you want you looking for..

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

This is the script that might help you to recover your job informationECLARE
@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

hi Andrey,

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