?
Solved

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

Posted on 2006-03-29
8
Medium Priority
?
278 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
0
Comment
Question by:bethamsetty
8 Comments
 
LVL 9

Expert Comment

by:udayshankar
ID: 16326788
run a trace in sql profiler to see whats happening on the server
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 16326977
Look in master..sysprocesses (assuming SQL 2000)
0
 
LVL 33

Accepted Solution

by:
knightEknight earned 150 total points
ID: 16327048
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
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
LVL 33

Expert Comment

by:knightEknight
ID: 16327068
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
0
 

Author Comment

by:bethamsetty
ID: 16327171
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'
0
 
LVL 35

Expert Comment

by:David Todd
ID: 16327352
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
0
 

Author Comment

by:bethamsetty
ID: 16327482
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
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16327523
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.
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question