How to tell if a job has hung?

using SQL7 and we have very occasionally had ascheduled nightly job
hang - it does not fail so we don't get an email it is just
"executing" - forever.

How could I test for this situation to generate an email if the job
runs longer than say 15 minutes?

Thanks

Billbill.. <b@.c.com> wrote in message news:<1tejqvkdp38mbv4rgncm373mu70af5306b@.4ax.com>...
> using SQL7 and we have very occasionally had ascheduled nightly job
> hang - it does not fail so we don't get an email it is just
> "executing" - forever.
> How could I test for this situation to generate an email if the job
> runs longer than say 15 minutes?
>
> Thanks
> Bill

Oen possibility is to query msdb..sysjobhistory and check the
run_status to see is it's still executing. You could also consider
finding the average run time for the job from the same table, and then
send an email only if the current execution time exceeds the average
by a certain threshold.

Simon|||Create another first step in the job to write the job name and start
datetime to a table. Add a final setp to write the completion.
Now have a schedule job to check for the start and completion - if the
completion does not turn up for 15 mins (can also check for the start)
then send an email.|||both good ideas

Thanks

Bill

how to stop the query

Hi All,
I am executing one query in sql analyzer. It was taking too much of time.I am not able to stop the query using stop button,it was not responding.
Many users are working with that database at the same time,not possible to stop services(agent or server) also.

Any help from you.

Regards
Bharat

Quote:

Originally Posted by bharadwaj

Hi All,
I am executing one query in sql analyzer. It was taking too much of time.I am not able to stop the query using stop button,it was not responding.
Many users are working with that database at the same time,not possible to stop services(agent or server) also.

Any help from you.

Regards
Bharat

You could try to disconnect... FILE >> Disconnect

how to stop executing a script

a setup process executes a script via osql. I want to parameterize
it in such a way that whenever wanted the script will do nothing.
For that I plan to created a table in master database, say
NODROP_DATABASE
the first few lines of the script will be
IF EXISTS (SELECT * FROM master.dbo.sysobjects where name =
'NODROP_DATABASE'
and type = 'U')
BEGIN
return
END
-- rest of the script here with many GO in between.
what is happening is that the return statement takes the control to the
first line after GO. The execution continues from the first GO after
that.
Is there any other approach to stop executing.
Assuming the script is run by a SYSADMIN you can RAISERROR with a severity
of 20 or greater to terminate the current connection.
Alternatively, run the script from OSQL with the -b option and it will
terminate on the first error.
David Portas
SQL Server MVP
|||... or, if using OSQL, issue a RAISERROR with state 127. OSQL will stop on errors with state 127.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:peKdnWO5478MiE3fRVn-vw@.giganews.com...
> Assuming the script is run by a SYSADMIN you can RAISERROR with a severity of 20 or greater to
> terminate the current connection.
> Alternatively, run the script from OSQL with the -b option and it will terminate on the first
> error.
> --
> David Portas
> SQL Server MVP
> --
>
|||Tibor Karaszi wrote:

> ... or, if using OSQL, issue a RAISERROR with state 127. OSQL will stop on errors with state 127.
David and Tibor,
Raising error with RAISEERROR means abnormal termination. The problem
with
that approach is that setup has to be programmed to ignore 127. I want
a graceful exit from running the script as if the script ran
successfully.
|||Why not just add the conditional test in your calling code and decide
at that point whether or not to execute the script. I can't think of a
better way.
David Portas
SQL Server MVP
|||Hi,
You could try to use labels.
IF (1 = 1)
GOTO QUIT
PRINT 'Not executing code'
QUIT:
PRINT 'Quit'
Ramon @. Havana Club,
SQL Server MCDBBA
"Data Cruncher" <dcruncher4@.netscape.net> ??/?? ? ??
??: news:1121093312.487489.156320@.z14g2000cwz.googlegr oups.com...[vbcol=seagreen]
> Tibor Karaszi wrote:
on errors with state 127.
> David and Tibor,
> Raising error with RAISEERROR means abnormal termination. The problem
> with
> that approach is that setup has to be programmed to ignore 127. I want
> a graceful exit from running the script as if the script ran
> successfully.
>

how to stop executing a script

a setup process executes a script via osql. I want to parameterize
it in such a way that whenever wanted the script will do nothing.
For that I plan to created a table in master database, say
NODROP_DATABASE
the first few lines of the script will be
IF EXISTS (SELECT * FROM master.dbo.sysobjects where name =
'NODROP_DATABASE'
and type = 'U')
BEGIN
return
END
-- rest of the script here with many GO in between.
what is happening is that the return statement takes the control to the
first line after GO. The execution continues from the first GO after
that.
Is there any other approach to stop executing.Assuming the script is run by a SYSADMIN you can RAISERROR with a severity
of 20 or greater to terminate the current connection.
Alternatively, run the script from OSQL with the -b option and it will
terminate on the first error.
David Portas
SQL Server MVP
--|||... or, if using OSQL, issue a RAISERROR with state 127. OSQL will stop on
errors with state 127.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:peKdnWO5478MiE3fRVn-vw@.giganews.com...
> Assuming the script is run by a SYSADMIN you can RAISERROR with a severity
of 20 or greater to
> terminate the current connection.
> Alternatively, run the script from OSQL with the -b option and it will ter
minate on the first
> error.
> --
> David Portas
> SQL Server MVP
> --
>|||Tibor Karaszi wrote:

> ... or, if using OSQL, issue a RAISERROR with state 127. OSQL will stop on errors
with state 127.
David and Tibor,
Raising error with RAISEERROR means abnormal termination. The problem
with
that approach is that setup has to be programmed to ignore 127. I want
a graceful exit from running the script as if the script ran
successfully.|||Why not just add the conditional test in your calling code and decide
at that point whether or not to execute the script. I can't think of a
better way.
David Portas
SQL Server MVP
--|||Hi,
You could try to use labels.
IF (1 = 1)
GOTO QUIT
PRINT 'Not executing code'
QUIT:
PRINT 'Quit'
Ramon @. Havana Club,
SQL Server MCDBBA
"Data Cruncher" <dcruncher4@.netscape.net> '?/'' ? ''
'?: news:1121093312.487489.156320@.z14g2000cwz.googlegroups.com...
> Tibor Karaszi wrote:
>
on errors with state 127.[vbcol=seagreen]
> David and Tibor,
> Raising error with RAISEERROR means abnormal termination. The problem
> with
> that approach is that setup has to be programmed to ignore 127. I want
> a graceful exit from running the script as if the script ran
> successfully.
>

how to stop executing a script

a setup process executes a script via osql. I want to parameterize
it in such a way that whenever wanted the script will do nothing.
For that I plan to created a table in master database, say
NODROP_DATABASE
the first few lines of the script will be
IF EXISTS (SELECT * FROM master.dbo.sysobjects where name = 'NODROP_DATABASE'
and type = 'U')
BEGIN
return
END
-- rest of the script here with many GO in between.
what is happening is that the return statement takes the control to the
first line after GO. The execution continues from the first GO after
that.
Is there any other approach to stop executing.Assuming the script is run by a SYSADMIN you can RAISERROR with a severity
of 20 or greater to terminate the current connection.
Alternatively, run the script from OSQL with the -b option and it will
terminate on the first error.
--
David Portas
SQL Server MVP
--|||... or, if using OSQL, issue a RAISERROR with state 127. OSQL will stop on errors with state 127.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:peKdnWO5478MiE3fRVn-vw@.giganews.com...
> Assuming the script is run by a SYSADMIN you can RAISERROR with a severity of 20 or greater to
> terminate the current connection.
> Alternatively, run the script from OSQL with the -b option and it will terminate on the first
> error.
> --
> David Portas
> SQL Server MVP
> --
>|||Tibor Karaszi wrote:
> ... or, if using OSQL, issue a RAISERROR with state 127. OSQL will stop on errors with state 127.
David and Tibor,
Raising error with RAISEERROR means abnormal termination. The problem
with
that approach is that setup has to be programmed to ignore 127. I want
a graceful exit from running the script as if the script ran
successfully.|||Why not just add the conditional test in your calling code and decide
at that point whether or not to execute the script. I can't think of a
better way.
--
David Portas
SQL Server MVP
--|||Hi,
You could try to use labels.
IF (1 = 1)
GOTO QUIT
PRINT 'Not executing code'
QUIT:
PRINT 'Quit'
Ramon @. Havana Club,
SQL Server MCDBBA
"Data Cruncher" <dcruncher4@.netscape.net> '?/'' ? ''
'?: news:1121093312.487489.156320@.z14g2000cwz.googlegroups.com...
> Tibor Karaszi wrote:
> > ... or, if using OSQL, issue a RAISERROR with state 127. OSQL will stop
on errors with state 127.
> David and Tibor,
> Raising error with RAISEERROR means abnormal termination. The problem
> with
> that approach is that setup has to be programmed to ignore 127. I want
> a graceful exit from running the script as if the script ran
> successfully.
>