Determine when currently executed job started

Hello!
I am trying to determine whether currently executed job is taking longer
than usual. I was wondering whether there is a way to determine start time
of currently executed job. I can use sp_help_job or xp_sqlagent_enum_jobs
but none of them reports this information.
Any advice is greatly appreciated,
Igor
Hi,
Query the MSDB..sysjobschedules table for below fields:-
active_start_date
active_end_date
active_start_time
active_end_time
Thanks
Hari
SQLServer MVP
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:e8XavO6lFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Hello!
> I am trying to determine whether currently executed job is taking longer
> than usual. I was wondering whether there is a way to determine start time
> of currently executed job. I can use sp_help_job or xp_sqlagent_enum_jobs
> but none of them reports this information.
> Any advice is greatly appreciated,
> Igor
>
>
|||Hari,
Sorry, I didn't make myself clear. I would like to determine when job
that is running at this moment started. Unfortunately, fields you mentioned
do not provide this information. For example, I just scheduled a job to run
every 2 minutes continuously and I see that active_start_date=20050802 and
active_start_time=0.
I can collect data about currently running jobs using
xp_sqlagent_enum_jobs or sp_help_job but data I need is not reported by
those procedures.
Thanks,
Igor
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:e6dVhS6lFHA.3380@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Query the MSDB..sysjobschedules table for below fields:-
> active_start_date
> active_end_date
> active_start_time
> active_end_time
> Thanks
> Hari
> SQLServer MVP
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:e8XavO6lFHA.1148@.TK2MSFTNGP12.phx.gbl...
>

Determine when currently executed job started

Hello!
I am trying to determine whether currently executed job is taking longer
than usual. I was wondering whether there is a way to determine start time
of currently executed job. I can use sp_help_job or xp_sqlagent_enum_jobs
but none of them reports this information.
Any advice is greatly appreciated,
IgorHi,
Query the MSDB..sysjobschedules table for below fields:-
active_start_date
active_end_date
active_start_time
active_end_time
Thanks
Hari
SQLServer MVP
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:e8XavO6lFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Hello!
> I am trying to determine whether currently executed job is taking longer
> than usual. I was wondering whether there is a way to determine start time
> of currently executed job. I can use sp_help_job or xp_sqlagent_enum_jobs
> but none of them reports this information.
> Any advice is greatly appreciated,
> Igor
>
>|||Hari,
Sorry, I didn't make myself clear. I would like to determine when job
that is running at this moment started. Unfortunately, fields you mentioned
do not provide this information. For example, I just scheduled a job to run
every 2 minutes continuously and I see that active_start_date=20050802 and
active_start_time=0.
I can collect data about currently running jobs using
xp_sqlagent_enum_jobs or sp_help_job but data I need is not reported by
those procedures.
Thanks,
Igor
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:e6dVhS6lFHA.3380@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Query the MSDB..sysjobschedules table for below fields:-
> active_start_date
> active_end_date
> active_start_time
> active_end_time
> Thanks
> Hari
> SQLServer MVP
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:e8XavO6lFHA.1148@.TK2MSFTNGP12.phx.gbl...
>

Determine when currently executed job started

Hello!
I am trying to determine whether currently executed job is taking longer
than usual. I was wondering whether there is a way to determine start time
of currently executed job. I can use sp_help_job or xp_sqlagent_enum_jobs
but none of them reports this information.
Any advice is greatly appreciated,
IgorHi,
Query the MSDB..sysjobschedules table for below fields:-
active_start_date
active_end_date
active_start_time
active_end_time
Thanks
Hari
SQLServer MVP
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:e8XavO6lFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Hello!
> I am trying to determine whether currently executed job is taking longer
> than usual. I was wondering whether there is a way to determine start time
> of currently executed job. I can use sp_help_job or xp_sqlagent_enum_jobs
> but none of them reports this information.
> Any advice is greatly appreciated,
> Igor
>
>|||Hari,
Sorry, I didn't make myself clear. I would like to determine when job
that is running at this moment started. Unfortunately, fields you mentioned
do not provide this information. For example, I just scheduled a job to run
every 2 minutes continuously and I see that active_start_date=20050802 and
active_start_time=0.
I can collect data about currently running jobs using
xp_sqlagent_enum_jobs or sp_help_job but data I need is not reported by
those procedures.
Thanks,
Igor
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:e6dVhS6lFHA.3380@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Query the MSDB..sysjobschedules table for below fields:-
> active_start_date
> active_end_date
> active_start_time
> active_end_time
> Thanks
> Hari
> SQLServer MVP
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:e8XavO6lFHA.1148@.TK2MSFTNGP12.phx.gbl...
>> Hello!
>> I am trying to determine whether currently executed job is taking longer
>> than usual. I was wondering whether there is a way to determine start
>> time of currently executed job. I can use sp_help_job or
>> xp_sqlagent_enum_jobs but none of them reports this information.
>> Any advice is greatly appreciated,
>> Igor
>>
>sql

Detect if a Job is running using TSQL

Is it possible to detect if a SQL Server Agent job is running using TSQL? I would like to programmatically determine if a job is running and take certain action if it is or isn't.

Thanks - Amos.

One simple way is to query the msdb.dbo.sysjobactivity table for the job and check to see if stop_execution_date is null. If it is, the job is running. At least that's my understanding.

sp_help_job returns a current_execution_status column. The problem with using this stored procedure is that it cannot be used in an insert...exec statement, because it has an insert...exec statement in it and these cannot be nested. You could go into the procedure and find the procedure it runs to get the job status, but that procedure is not documented.

Ron Rice

|||

Using Books Online, look up sp_help_job.

For example,

EXECUTE sp_help_job
@.job_id = @.MyJobID [OR] @.job_name = @.MyJobName,

@.execution_status > 0

Destination runs out of space => job never finishes

Hi,

I have developed a SSIS package that performs data cleansing before data is loaded into a DW. I'm using a Multicast transformation to load the cleansed data to both a production and a test environment.

A couple of time now, I have exprienced that the test environment runs out of disk space and can not grow the database file (I know - this should never happen, but tell that to the DBA :o).

For some reason this causes the package to hang - no error is returned and the job executing the package remains in "Executing job" status, meaning that the cube processing is never started. Shouldn't I get an error back when the disk runs out of space?

Regards,
SuneYes, you should. However, we rely on the provider returning the error from the SQL Server. If it doesn't then we have no way of knowing an error occurred.

Thanks,
Matt

designing database for jobs portal

Hi...
I am making a online job portal...for dat i need to make a gud database.
wud anybody be kind enuf to provide or tell me how to make a good database design...
thnx..Introduction to Data Modeling (http://www.utexas.edu/its/windows/database/datamodeling/)

Design Advice

Hello - this is my first post (just found the site today). I'm looking for advice regarding the following design issue(s):

I have a VB batch job that runs a couple of times throughout the day - each time it runs, it inserts approximately 100k rows to a table (call it Table A). I need to be able to view the inserted data by batch run and in aggregate. Obviously querying the table has now become too expensive to produce aggregate reports.

I'd like to create an aggregate table (call it Table B) so that as rows are inserted into Table A, Table B is updated to reflect running totals.

Currently, the batch job inserts records into Table A by using the ADO batchupdate method. If I were to stick with this, then I could create a trigger to update Table B as records are inserted into Table A.

Question 1: Is it posssible to create a trigger that will update if the record exists or insert if it doesn't? What kind of overhead does that represent?

Question 2: If instead of using the updatebatch method, I call a stored procedure to insert each of the records into Table A and also insert/update Table B, will I suffer a performance hit?

Sorry for the long post, just wanted to make sure I didn't leave anything out. TIA for any help you can offer!In answer to your questions

1) You can create a trigger on Table A that updates/inserts into TableB.
UPDATE
SET value = value+i.Increment
FROM inserted i,
TableB b
WHERE b.Key = i.Key

INSERT TableB (col1,col2,col3,...)
SELECT col1,col2,col3,...
FROM inserted i
WHERE NOT EXISTS
(
SELECT *
FROM TableB b
WHERE b.Key = i.Key
)
This is just an idea on how you can do it. You could simplify the INSERT part if only one record is in the INSERTED table. You may need to think about what should happen to the values in TableB if TableA is updated?

2) Stored procedure are allows the way to go and increase performance, however when it comes to bulk operations or single operation with stored procedure, I like bulk. If you could combine the 2 that would be great.