Check Job-Status

Hi NG,
I defined a database maintenance task as a job. The job will normally be
executed by the SQL-Server according it's schedule. Furthermore I'd like to
start the job on demand.
Before I execute my job with EXEC msdb..sp_start_job @.job_name='my_job_name'
I have to check if it is already running. This could be checked with
sp_help_job.
The stored procedure sp_help_job returns multiple rowsets, how can I found
out if the job is still running or if it is idle?
DECLARE @.retval int
EXEC @.retval = msdb..sp_help_job @.job_name='my_job_name',
@.execution_status=4 returns 4 rowsets. If a job is already running I get the
following error:
Server: Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job Job for 'my_job_name' (from User
sa) refused because the job already has a pending request from User sa.
I tried to check on @.@.ERROR or @.@.ROWCOUNT but both variables are related to
the last rowset of SP sp_help_job .
Thanks for suggestions, reneHi
The column msdb.dbo.sysjobhistory.run_status will give you the status of the
job.
Integer
Status of the job execution:
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
Regards
Mike
"Re Fo" wrote:
> Hi NG,
> I defined a database maintenance task as a job. The job will normally be
> executed by the SQL-Server according it's schedule. Furthermore I'd like to
> start the job on demand.
> Before I execute my job with EXEC msdb..sp_start_job @.job_name='my_job_name'
> I have to check if it is already running. This could be checked with
> sp_help_job.
> The stored procedure sp_help_job returns multiple rowsets, how can I found
> out if the job is still running or if it is idle?
> DECLARE @.retval int
> EXEC @.retval = msdb..sp_help_job @.job_name='my_job_name',
> @.execution_status=4 returns 4 rowsets. If a job is already running I get the
> following error:
> Server: Msg 22022, Level 16, State 1, Line 0
> SQLServerAgent Error: Request to run job Job for 'my_job_name' (from User
> sa) refused because the job already has a pending request from User sa.
> I tried to check on @.@.ERROR or @.@.ROWCOUNT but both variables are related to
> the last rowset of SP sp_help_job .
> Thanks for suggestions, rene
>
>
>
>

Check Job-Status

Hi NG,
I defined a database maintenance task as a job. The job will normally be
executed by the SQL-Server according it's schedule. Furthermore I'd like to
start the job on demand.
Before I execute my job with EXEC msdb..sp_start_job @.job_name='my_job_name'
I have to check if it is already running. This could be checked with
sp_help_job.
The stored procedure sp_help_job returns multiple rowsets, how can I found
out if the job is still running or if it is idle?
DECLARE @.retval int
EXEC @.retval = msdb..sp_help_job @.job_name='my_job_name',
@.execution_status=4 returns 4 rowsets. If a job is already running I get the
following error:
Server: Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job Job for 'my_job_name' (from User
sa) refused because the job already has a pending request from User sa.
I tried to check on @.@.ERROR or @.@.ROWCOUNT but both variables are related to
the last rowset of SP sp_help_job .
Thanks for suggestions, reneHi
The column msdb.dbo.sysjobhistory.run_status will give you the status of the
job.
Integer
Status of the job execution:
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
Regards
Mike
"Re Fo" wrote:

> Hi NG,
> I defined a database maintenance task as a job. The job will normally be
> executed by the SQL-Server according it's schedule. Furthermore I'd like t
o
> start the job on demand.
> Before I execute my job with EXEC msdb..sp_start_job @.job_name='my_job_nam
e'
> I have to check if it is already running. This could be checked with
> sp_help_job.
> The stored procedure sp_help_job returns multiple rowsets, how can I found
> out if the job is still running or if it is idle?
> DECLARE @.retval int
> EXEC @.retval = msdb..sp_help_job @.job_name='my_job_name',
> @.execution_status=4 returns 4 rowsets. If a job is already running I get t
he
> following error:
> Server: Msg 22022, Level 16, State 1, Line 0
> SQLServerAgent Error: Request to run job Job for 'my_job_name' (from User
> sa) refused because the job already has a pending request from User sa.
> I tried to check on @.@.ERROR or @.@.ROWCOUNT but both variables are related t
o
> the last rowset of SP sp_help_job .
> Thanks for suggestions, rene
>
>
>
>

Check Job-Status

Hi NG,
I defined a database maintenance task as a job. The job will normally be
executed by the SQL-Server according it's schedule. Furthermore I'd like to
start the job on demand.
Before I execute my job with EXEC msdb..sp_start_job @.job_name='my_job_name'
I have to check if it is already running. This could be checked with
sp_help_job.
The stored procedure sp_help_job returns multiple rowsets, how can I found
out if the job is still running or if it is idle?
DECLARE @.retval int
EXEC @.retval = msdb..sp_help_job @.job_name='my_job_name',
@.execution_status=4 returns 4 rowsets. If a job is already running I get the
following error:
Server: Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job Job for 'my_job_name' (from User
sa) refused because the job already has a pending request from User sa.
I tried to check on @.@.ERROR or @.@.ROWCOUNT but both variables are related to
the last rowset of SP sp_help_job .
Thanks for suggestions, rene
Hi
The column msdb.dbo.sysjobhistory.run_status will give you the status of the
job.
Integer
Status of the job execution:
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
Regards
Mike
"Re Fo" wrote:

> Hi NG,
> I defined a database maintenance task as a job. The job will normally be
> executed by the SQL-Server according it's schedule. Furthermore I'd like to
> start the job on demand.
> Before I execute my job with EXEC msdb..sp_start_job @.job_name='my_job_name'
> I have to check if it is already running. This could be checked with
> sp_help_job.
> The stored procedure sp_help_job returns multiple rowsets, how can I found
> out if the job is still running or if it is idle?
> DECLARE @.retval int
> EXEC @.retval = msdb..sp_help_job @.job_name='my_job_name',
> @.execution_status=4 returns 4 rowsets. If a job is already running I get the
> following error:
> Server: Msg 22022, Level 16, State 1, Line 0
> SQLServerAgent Error: Request to run job Job for 'my_job_name' (from User
> sa) refused because the job already has a pending request from User sa.
> I tried to check on @.@.ERROR or @.@.ROWCOUNT but both variables are related to
> the last rowset of SP sp_help_job .
> Thanks for suggestions, rene
>
>
>
>
sql

Check Execution status when manually running Snapshot agents

I am working on a script to run in Query Analyzer to run all of my SnapShot
agents manually rather than wait for the schedule to do it. I am using
sp_start_job to run each agent such as:
exec msdb.dbo.sp_start_job @.Job_Name='SQLSERVER-SomeSnapshotAgent-167'
waitfor delay '00:30:00' --some fixed time interval to try and have jobs not
run into each other
exec msdb.dbo.sp_start_job @.Job_Name='SQLSERVER-AnotherSnapshotAgent-154'
If I put all of my Snapshot agent jobs in a script to run back to back, how
can I check for execution status so the next job does not run into the
previous agent already running?
Thanks,
Steve
Steve,
you can use the following rputine (modified slightly) to determine the job's
current status.
http://www.replicationanswers.com/Downloads/KillRunningJobs.txt
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||How do I obtain the Guid Job_id for the job name(s) that I am wanting to run?
Thank You
Steve
"Paul Ibison" wrote:

> Steve,
> you can use the following rputine (modified slightly) to determine the job's
> current status.
> http://www.replicationanswers.com/Downloads/KillRunningJobs.txt
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
|||Steve,
it'll be something like this - just replace the name with the name of the
snapshot agent:
select job_id FROM sysjobs
where name = 'UK-3XSW02J\PAULS2000INST-Pub1-2'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Check database integrity and backup question

In my backup schedule, I check to "Check database integrity" at 4:00 am and
schedule the backup at 4:30 pm. What happen if the database integrity is not
finish by the time the backup schedule starts ? Thanks.The backup will go ahead as normal. Backups in SQL Server are online, and
are not blocked by other processes whether that is an integrity check or a
normal update.
Jacco Schalkwijk
SQL Server MVP
"fniles" <fniles@.pfmail.com> wrote in message
news:uvjunGnJFHA.3420@.tk2msftngp13.phx.gbl...
> In my backup schedule, I check to "Check database integrity" at 4:00 am
> and schedule the backup at 4:30 pm. What happen if the database integrity
> is not finish by the time the backup schedule starts ? Thanks.
>

Check database integrity and backup question

In my backup schedule, I check to "Check database integrity" at 4:00 am and
schedule the backup at 4:30 pm. What happen if the database integrity is not
finish by the time the backup schedule starts ? Thanks.
The backup will go ahead as normal. Backups in SQL Server are online, and
are not blocked by other processes whether that is an integrity check or a
normal update.
Jacco Schalkwijk
SQL Server MVP
"fniles" <fniles@.pfmail.com> wrote in message
news:uvjunGnJFHA.3420@.tk2msftngp13.phx.gbl...
> In my backup schedule, I check to "Check database integrity" at 4:00 am
> and schedule the backup at 4:30 pm. What happen if the database integrity
> is not finish by the time the backup schedule starts ? Thanks.
>

Check database integrity and backup question

In my backup schedule, I check to "Check database integrity" at 4:00 am and
schedule the backup at 4:30 pm. What happen if the database integrity is not
finish by the time the backup schedule starts ? Thanks.The backup will go ahead as normal. Backups in SQL Server are online, and
are not blocked by other processes whether that is an integrity check or a
normal update.
--
Jacco Schalkwijk
SQL Server MVP
"fniles" <fniles@.pfmail.com> wrote in message
news:uvjunGnJFHA.3420@.tk2msftngp13.phx.gbl...
> In my backup schedule, I check to "Check database integrity" at 4:00 am
> and schedule the backup at 4:30 pm. What happen if the database integrity
> is not finish by the time the backup schedule starts ? Thanks.
>