Scheduled task doesn't appear to finish

Scheduled task doesn't appear to finish

Post by Maurfeen Lam » Thu, 15 Mar 2001 03:40:34



When I run a stored procedure from query analyzer it takes 2.5 minutes. When
I run the same procedure as a scheduled task it has to be cancelled after
over an hour. The scheduled task does not seem to complete. Any ideas?
 
 
 

Scheduled task doesn't appear to finish

Post by Andrew J. Kell » Thu, 15 Mar 2001 05:07:30


Try putting SET NOCOUNT ON at the beginning of the sp. If that doesn't work
then try executing it in the Job as a CmdExec type job step and use oSql to
run the stored procedure.

--
Andrew J. Kelly
Targitmail.com


Quote:> When I run a stored procedure from query analyzer it takes 2.5 minutes.
When
> I run the same procedure as a scheduled task it has to be cancelled after
> over an hour. The scheduled task does not seem to complete. Any ideas?


 
 
 

Scheduled task doesn't appear to finish

Post by Rashmi Da » Thu, 15 Mar 2001 05:32:59


when a task is run as scheduled task, it uses the startup account of SQL
Server Agent, to run the task.  Please check and make sure that SQL Agent
startup account has enough permission to execute the task.  In other words
you can use the same login to login to the box, then connect to SQL Server
and execute the stored procedure.  

If after that nothing is clear , run SQL Server profiler to find what is
occurring.

Also run sp_who in a while loop to see if the task is being blocked by some
other tasks running at night.  

what type of task is it? What does it accomplish?

 
 
 

Scheduled task doesn't appear to finish

Post by Maureen Lam » Sat, 24 Mar 2001 05:46:51


The suggestion that I add "set nocount on" did the trick.

Thanks.
Maureen

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

 
 
 

Scheduled task doesn't appear to finish

Post by Maureen Lam » Sat, 24 Mar 2001 05:55:32


As you will see from a previous post, the "SET NOCOUNT ON" was the solution.

I already knew that SQL Server Agent was running properly as short queries run as scheduled tasks were working. I had already submitted SQL Profiler outputs to Microsoft for both the query running in Query Analyzer and running as a scheduled task. They could not see any reason for the slow performance.

This is a test server and it was the only user task running on the server. What it does is update two fields of a table depending on whether another field is null and some additional criteria are met? By reducing the result set to 900, it would run in 5 seconds under Query Analyzer and finish in about 3 minutes as a scheduled task. After including SET NOCOUNT ON, the times were 2 and 3 seconds, resp. And, running against the full result set of 35,000 records took only 1 minute. Much better!

Thanks for the suggestions,
Maureen Lamb
Informatics Services Division
Finance/TBS-SCT Canada

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

 
 
 

Scheduled task doesn't appear to finish

Post by Rashmi Da » Fri, 04 May 2001 08:10:11


please see the following Q article :

Q249730

A scheduled Microsoft SQL Server 7.0 Transact-SQL (T-SQL) task may run
slower in
SQL Server 7.0 or SQL Server 2000 than in Query Analyzer or Microsoft SQL
Server
6.5.

CAUSE
=====

Because the SQL Server Agent has to share server resources with the SQL
Server
engine, the SQL Server Agent introduces a delay when you process T-SQL task
output. The purpose of the delay is to avoid consuming all available CPU
resources when you run the task.

This behavior is designed to:

 - Allow multiple jobs to run in parallel.

   -and-

 - Decrease the impact on CPU resources, especially on Microsoft Windows 95
or
   Microsoft Windows 98.

This allows multiple jobs to run in parallel, however, since each job gets
CPU
time, in the case where there is only one scheduled job, a decrease in job
throughput occurs.

WORKAROUND
==========

To obtain maximum throughput when there is only one scheduled job, create
the
task as a scheduled Cmdexec task that runs the OSQL utility.

If you have procedures that return several empty result sets (informational
DONE_IN_PROC messages), you can reduce the number of delays that occur when
you
run the task as a scheduled T-SQL task, if you add a SET NOCOUNT ON
statement to
the beginning of the stored procedure or T-SQL batch.

--------------------


| X-Newsreader: AspNNTP 1.50 (ActionJackson.com)
| Subject: RE: Scheduled task doesn't appear to finish
| Mime-Version: 1.0
| Content-Type: text/plain; charset="us-ascii"
| Content-Transfer-Encoding: quoted-printable

| Newsgroups: microsoft.public.sqlserver.programming
| Date: Thu, 22 Mar 2001 12:55:32 -0800
| NNTP-Posting-Host: 63.84.175.221
| Lines: 1        
| Path: cppssbbsa01.microsoft.com!tkmsftngp01!tkmsftngp04
| Xref: cppssbbsa01.microsoft.com
microsoft.public.sqlserver.programming:152644
| X-Tomcat-NG: microsoft.public.sqlserver.programming
|
| As you will see from a previous post, the "SET NOCOUNT ON" was the
solution.
| I already knew that SQL Server Agent was running properly as short
queries run as scheduled tasks were working. I had already submitted SQL
Profiler outputs to Microsoft for both the query running in Query Analyzer
and running as a scheduled task. They could not see any reason for the slow
performance.
| This is a test server and it was the only user task running on the
server. What it does is update two fields of a table depending on whether
another field is null and some additional criteria are met? By reducing the
result set to 900, it would run in 5 seconds under Query Analyzer and
finish in about 3 minutes as a scheduled task. After including SET NOCOUNT
ON, the times were 2 and 3 seconds, resp. And, running against the full
result set of 35,000 records took only 1 minute. Much better!
| Thanks for the suggestions,
| Maureen Lamb
| Informatics Services Division
| Finance/TBS-SCT Canada
| *** Sent via Developersdex http://www.developersdex.com ***
| Don't just participate in USENET...get rewarded for it!
|

 
 
 

1. Scheduled DTS package doesn't start task

    Hello.

    I have a DTS package with a Custom FTP Task. If I execute it on the
server from the DTS Designer it runs without problems as if I use
xp_cmdshell 'dtsRun ...'. The problem is that if I schedule the package
the FTP task doesn't execute but the next step in the package is
executed normally. Can this be a problem with the location of the DLL
implementing the CustomTask? I installed it on C:\program
Files\Microsoft SQL Server\80\tools\binn.

    Anyone has any clue about it.

    Thank you very much.

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

2. Using EXEC statement inside a CURSOR statement

3. task scheduling doesn't work!!

4. Insert Update

5. Scheduled Tasks doesn't always run

6. Remote Access to SYSOPER and SYSDBA

7. Scheduling DTS doesn't fail but doesn't work

8. Summary Band in Report Wr

9. Scheduled tasks won't run from the Task Manager

10. Stored procedure doesn't finish but no error message

11. Database Doesn't Appear in Enterprise Manager

12. Raiserror message from Trigger doesn't appear in Access 2000 form