We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

How can i know wheather my job is running or not ?

bethamsetty
bethamsetty asked
on
Medium Priority
293 Views
Last Modified: 2008-02-26
Hi ,
   I have migrated one Server 'A' database to server 'B' and after completion of this, i have scheduled a job in 'B' server.  Usually in 'A' server, it takes 30 min, but in 'B' server it is keep on running and not coming out.  Is there a way to monitor whether my job is really doing something inside or not ?

Thanks in advance

Sridhar.B
Comment
Watch Question

run a trace in sql profiler to see whats happening on the server
Kevin HillSr. SQL Server DBA

Commented:
Look in master..sysprocesses (assuming SQL 2000)
CERTIFIED EXPERT
Commented:
CREATE PROCEDURE spIsJobRunning
   @jobName nvarchar(256)
 AS
  begin

    declare @returnStep tinyint

    CREATE TABLE #tmp_Job_Data
    (
      job_id                UNIQUEIDENTIFIER NOT NULL,
      last_run_date         INT              NOT NULL,
      last_run_time         INT              NOT NULL,
      next_run_date         INT              NOT NULL,
      next_run_time         INT              NOT NULL,
      next_run_schedule_id  INT              NOT NULL,
      requested_to_run      INT              NOT NULL,
      request_source        INT              NOT NULL,
      request_source_id     sysname          COLLATE database_default NULL,
      running               INT              NOT NULL,
      current_step          INT              NOT NULL,
      current_retry_attempt INT              NOT NULL,
      job_state             INT              NOT NULL
    )


    SET NOCOUNT ON

    INSERT INTO #tmp_Job_Data
    EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, ''

    SET NOCOUNT OFF

    select @returnStep = current_step
    from   #tmp_Job_Data X (nolock) inner join
           msdb..sysjobs J (nolock) on J.job_id = X.job_id
    where J.name = @jobName
      and X.running = 1

    drop table #tmp_Job_Data

--    select IsNull(@returnStep,0) as current_step_id

    return IsNull(@returnStep,0)

  end
GO

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT

Commented:
the above will return 0 if the job is not running, otherwise it will return the current step number of the running job.

declare @step tinyint

exec @step= spIsJobRunning 'myJobName'

select @step

Author

Commented:
Thanks....i created SP and ran the small statement .  It is showing '1' but how can we relate this number with the step executing in our DTS package.  Because i see many "Execute SQL Tasks".  If it could shows us the description of the task then it would be nice to identify it very easily..

any help 'KnightEknight'
David ToddSenior Database Administrator
CERTIFIED EXPERT

Commented:
Hi,

One of the problems is that SQL Agent can't see inside a DTS package, and will only show the one step - starting DTS package.

My guess is that your next step is to open the DTS package on Server 'B' and run it interactively, and see where it stalls/errors.

Regards
  David

Author

Commented:
Yes! I can do that ..but before that i want to know how much process done.. otherwise it's big trouble for me to look in to each table and to identify how much process done.

Thanks
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Add separate steps between each major step in the pkg that just write info to a table; that will give you a type of trace of the pkg.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.