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

Determine the last time SP was executed

Hello -

I am trying to determine the last time a SP was executed. Does anyone
know how to do this? I'm trying to cleanup some databases.

Thanks!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You need to be running PROFILER...which would be a big drain to run it 100% in
parallel with a production server...

I suggest running it for a day or 2, during days when you know every part of an
application will get 'touched'. Then investigate the profiler output for your
target objects.|||I have a SP usage table that I use to see what SP's are being ran and
when. The table consists of an ID (autogen), sp_name, LastTimeUsed,
TimesUsed. I can keep track of what SP's are being used, when they
were last used and how many times they were used. I did this one day
as a CYA measure, and found it very useful. And the overhead for my
system is very small.

dcmfan@.aol.comSPNOAM (DCM Fan) wrote in message news:<20031210191602.04241.00000665@.mb-m29.aol.com>...
> You need to be running PROFILER...which would be a big drain to run it 100% in
> parallel with a production server...
> I suggest running it for a day or 2, during days when you know every part of an
> application will get 'touched'. Then investigate the profiler output for your
> target objects.|||Thanks!

Thats what I thought but I was hoping there was somthing else.

Thanks Again!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!sql

Determine Previous Task Executed

I have the failed path of many tasks pointing to a single Script Task that logs a failed entry into my database. In this script task, I need to be able to determine the task that faliled as I need to write a unique error message depending on the task that failed.

In my VB script, how would I determine which task was the one that failed?

Thank You!!!

a SSIS newbie

There is no way I can see to do that.

Have you investigated the logging available in SSIS before you write your own?

If you do want a task for additional error handling work, then try the on error event handler. Event handlers can host an entire workflow of tasks if you want, and are a better way of capturing this type of event compared to workflow constraints. You have some system variables scoped to the event handler that should give you all the information you want.

|||What I do is add a derived column to each error flow. Then in that derived column, I add a new column, ErrorReason (or whatever you like), which is where I store a small string of what the error was. So then in your script component, you could look at that column and act accordingly.

However, it sounds like you can do all of this outside of a script component. (You are inside the data flow, correct? I assume so because you talk of "failed paths.") If you're talking about a failed precedence constraint, then I'll defer to Darren above.|||

Hi AspUser123,

I agree with Darren.

Recently, I implemented a custom error logging mechanism for a client using the onError Event Handler. They wanted the package to continue executing, but capture information about each fault.

Since the Error events bubble, I implemented the entire custom logging solution in the Package onError Event Handler. You can use the SourceName system variable to identify the name of the task that originally generated the error - even if it's nested inside several layers of containers. You can also collect information such as the ErrorDescription and ErrorCode (error number).

In this case I ended up writing the information out to a SQL Server database for storage, reporting, and analysis - but you could easily implement a flat file output. You can use an Execute SQL Task (calling a stored procedure) to write to SQL Server, or add a Data Flow to the onError Event Handler to store the system variable values to a flat file destination.

Hope this helps,
Andy

|||I agree with Darren.

Design Choices

I have 20-30 action queries which will be executed nightly. Is it better to place them in a DTS package and call the package, or place them in a stored procedure and call the stored procedure?If you are using a Access dtabase on a web site and using an inc file for your data connections I can down load the database and crack security. As for the performance of Acess it sucks over 1,000,000,000 records.|||This is for a SQL Server database. I'm not looking for your opinion on Access|||depends on what your action queries do. If you need to move data then DTS is the way to go. If you need to work with data inside SQL server then I would faver stored procedures.