Retriveing the process ID for a SQL Server Agent job being run

We have created a SQL Server Job called 'Asynchronous Batch Agent'
which is run Asynchronously on a set of databases each containing a
batch.
What I would like to do is to retrieve the Process ID of a particular
instance of this agent when its being called and write that to a table
with the ID for the individual database.
Im having little luck at finding this information. I look at sp_who2
and sysprocesses...but get a link to the job.
Thanks for your help on this
ChrisHi Chris,
Try @.@.spid, db_id() and db_name().
Hope this helps,
Ben Nevarez
"chris.asaipillai@.gmail.com" wrote:
> We have created a SQL Server Job called 'Asynchronous Batch Agent'
> which is run Asynchronously on a set of databases each containing a
> batch.
> What I would like to do is to retrieve the Process ID of a particular
> instance of this agent when its being called and write that to a table
> with the ID for the individual database.
> Im having little luck at finding this information. I look at sp_who2
> and sysprocesses...but get a link to the job.
> Thanks for your help on this
> Chris
>

Retriveing the process ID for a SQL Server Agent job being run

We have created a SQL Server Job called 'Asynchronous Batch Agent'
which is run Asynchronously on a set of databases each containing a
batch.
What I would like to do is to retrieve the Process ID of a particular
instance of this agent when its being called and write that to a table
with the ID for the individual database.
Im having little luck at finding this information. I look at sp_who2
and sysprocesses...but get a link to the job.
Thanks for your help on this
Chris
Hi Chris,
Try @.@.spid, db_id() and db_name().
Hope this helps,
Ben Nevarez
"chris.asaipillai@.gmail.com" wrote:

> We have created a SQL Server Job called 'Asynchronous Batch Agent'
> which is run Asynchronously on a set of databases each containing a
> batch.
> What I would like to do is to retrieve the Process ID of a particular
> instance of this agent when its being called and write that to a table
> with the ID for the individual database.
> Im having little luck at finding this information. I look at sp_who2
> and sysprocesses...but get a link to the job.
> Thanks for your help on this
> Chris
>

Retrive old JobHistory

I have a job which only keeps 100 entries in the job history. When one new
entry comes, the oldest entry in the list is deleted.
I need retrieve the old job histories that have been removed from the
current job history list. How can I do it? Restore DB is not an option.
Thanks a lot,
LixinYou will need to restore msdb(as different db) and look at the table
sysjobhistory, I recommend you iether increase the number of history rows
for that job or write a job which copies the rows to an archive table.
Yovan
"Lixin Fan" <nospam@.hotmail.com> wrote in message
news:uYx1XbouDHA.2712@.tk2msftngp13.phx.gbl...
> I have a job which only keeps 100 entries in the job history. When one new
> entry comes, the oldest entry in the list is deleted.
> I need retrieve the old job histories that have been removed from the
> current job history list. How can I do it? Restore DB is not an option.
> Thanks a lot,
> Lixin
>|||Hi Lixin
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
I think a trigger will be a workaround to you question.
Suppose you want to record the job history of Job_test, and the job_id is
={07C00C79-AAB0-49B0-BA0A-9E7C884440DB} ( you can get this information from
two tables in database 'msdb': 'sysjob' and 'sysjobhistory').
Suppose you have a table 'job_history' in database 'Database_test', and the
table 'job_history' has the same schema with the table 'sysjobhistory' in
msdb and you want to use this table to save the job history. You can add a
trigger to the sysjobhistory.
The thinking is: when the system add a new entry into the 'sysjobhistory',
check if this new entry is for job_test. If no, then take no action. If
yes, then the trigger will copy this entry in the job_history table in
database_test. By this way, you can record all the job history when the job
occurred.
The code for the trigger would be like this:
CREATE TRIGGER [jobtest] ON [dbo].[sysjobhistory]
FOR insert
AS
Declare @.job_date int
Declare @.job_time int
Declare @.job_idx varchar
Set @.job_idx=' ({07C00C79-AAB0-49B0-BA0A-9E7C884440DB}'
If @.job_idx<> (select top 1 job_id from [msdb].[dbo].[rundate] where
job_id=@.job_idx order by rundate,runtime asc)
--if the latest entry in the sysjobhistory is not caused by the job you
want to record
--no action
Begin
Return
End
--If the job you want to record add a new entry
Else
Begin
Insert [database_test].[dbo].[job_history].[rundate] value (select top 1 *
from [msdb].[dbo].[runtime] where job_id=@.job_idx order by rundate,runtime
asc
End
Note:
1) This code is just a thinking, not a tested one
2) This method will add the workload on your system, especially on a system
which has many concurrent jobs.
I hope this will help you to solve your problem. If you still have
questions, please feel free to post message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Great help. Thank you very much.
Lixin
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:NMNq6HxuDHA.2900@.cpmsftngxa07.phx.gbl...
> Hi Lixin
> Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
> your issue.
> I think a trigger will be a workaround to you question.
> Suppose you want to record the job history of Job_test, and the job_id is
> ={07C00C79-AAB0-49B0-BA0A-9E7C884440DB} ( you can get this information
from
> two tables in database 'msdb': 'sysjob' and 'sysjobhistory').
> Suppose you have a table 'job_history' in database 'Database_test', and
the
> table 'job_history' has the same schema with the table 'sysjobhistory' in
> msdb and you want to use this table to save the job history. You can add a
> trigger to the sysjobhistory.
> The thinking is: when the system add a new entry into the 'sysjobhistory',
> check if this new entry is for job_test. If no, then take no action. If
> yes, then the trigger will copy this entry in the job_history table in
> database_test. By this way, you can record all the job history when the
job
> occurred.
> The code for the trigger would be like this:
> CREATE TRIGGER [jobtest] ON [dbo].[sysjobhistory]
> FOR insert
> AS
> Declare @.job_date int
> Declare @.job_time int
> Declare @.job_idx varchar
> Set @.job_idx=' ({07C00C79-AAB0-49B0-BA0A-9E7C884440DB}'
> If @.job_idx<> (select top 1 job_id from [msdb].[dbo].[rundate] where
> job_id=@.job_idx order by rundate,runtime asc)
> --if the latest entry in the sysjobhistory is not caused by the job you
> want to record
> --no action
> Begin
> Return
> End
>
> --If the job you want to record add a new entry
> Else
> Begin
> Insert [database_test].[dbo].[job_history].[rundate] value (select top 1 *
> from [msdb].[dbo].[runtime] where job_id=@.job_idx order by rundate,runtime
> asc
> End
> Note:
> 1) This code is just a thinking, not a tested one
> 2) This method will add the workload on your system, especially on a
system
> which has many concurrent jobs.
> I hope this will help you to solve your problem. If you still have
> questions, please feel free to post message here and I am ready to help!
>
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>

Retrieving the name of a job while it is executing

Does anyone know of a way to get the name of the job that is running. Is
there a variable that this is stored in similar to @.@.servername or a stored
procedure that can return this information. I am wanting to capture the
jobname while the job is running
Thanks,
Randy Bunton
One method is to specify a '[JOBID]' token in your job step T-SQL script.
You can then use this value to retrieve the job name from sysjobs. For
example.
SELECT name
FROM msdb..sysjobs
WHERE job_id = CAST([JOBID] AS uniqueidentifier)
Note that SQL Server Agent job tokens can only be specified in the directly
executed script. If you need the value in stored procedures, etc, you can
pass the value as a parameter.
Hope this helps.
Dan Guzman
SQL Server MVP
"Randy Bunton" <buntonrl@.charlestoncpw.com> wrote in message
news:uVYnPsiSEHA.3988@.tk2msftngp13.phx.gbl...
> Does anyone know of a way to get the name of the job that is running. Is
> there a variable that this is stored in similar to @.@.servername or a
stored
> procedure that can return this information. I am wanting to capture the
> jobname while the job is running
> Thanks,
> Randy Bunton
>

Retrieving the name of a job while it is executing

Does anyone know of a way to get the name of the job that is running. Is
there a variable that this is stored in similar to @.@.servername or a stored
procedure that can return this information. I am wanting to capture the
jobname while the job is running
Thanks,
Randy BuntonOne method is to specify a '[JOBID]' token in your job step T-SQL script
.
You can then use this value to retrieve the job name from sysjobs. For
example.
SELECT name
FROM msdb..sysjobs
WHERE job_id = CAST([JOBID] AS uniqueidentifier)
Note that SQL Server Agent job tokens can only be specified in the directly
executed script. If you need the value in stored procedures, etc, you can
pass the value as a parameter.
Hope this helps.
Dan Guzman
SQL Server MVP
"Randy Bunton" <buntonrl@.charlestoncpw.com> wrote in message
news:uVYnPsiSEHA.3988@.tk2msftngp13.phx.gbl...
> Does anyone know of a way to get the name of the job that is running. Is
> there a variable that this is stored in similar to @.@.servername or a
stored
> procedure that can return this information. I am wanting to capture the
> jobname while the job is running
> Thanks,
> Randy Bunton
>

Retrieving the name of a job while it is executing

Does anyone know of a way to get the name of the job that is running. Is
there a variable that this is stored in similar to @.@.servername or a stored
procedure that can return this information. I am wanting to capture the
jobname while the job is running
Thanks,
Randy BuntonOne method is to specify a '[JOBID]' token in your job step T-SQL script.
You can then use this value to retrieve the job name from sysjobs. For
example.
SELECT name
FROM msdb..sysjobs
WHERE job_id = CAST([JOBID] AS uniqueidentifier)
Note that SQL Server Agent job tokens can only be specified in the directly
executed script. If you need the value in stored procedures, etc, you can
pass the value as a parameter.
Hope this helps.
Dan Guzman
SQL Server MVP
"Randy Bunton" <buntonrl@.charlestoncpw.com> wrote in message
news:uVYnPsiSEHA.3988@.tk2msftngp13.phx.gbl...
> Does anyone know of a way to get the name of the job that is running. Is
> there a variable that this is stored in similar to @.@.servername or a
stored
> procedure that can return this information. I am wanting to capture the
> jobname while the job is running
> Thanks,
> Randy Bunton
>