DTS Package hanging Problem

Question

DTS Package hanging Problem

Paul Sp

SSCrazy

Points: 2314
More actions
December 21, 2004 at 8:02 am

#163457

Hi
I am running a DTS local package using SQL Server Agent every 20 minutes. Most of the time it runs fine - then, about once a week it 'hangs'. I have to go into Enterprise Manager, right click the Agent Job, and choose 'Stop Job'. It will then carry on running happily for several days.
The Local Package is made up of serveral SQL tasks linked by 'On Success' workflow arrows. The failure appears to me to involve just one table - tblStaff_Current - if I run select statements in QA all other tables respond - this table hangs. The table appears to me to derive the error from the SQL below. Can anybody tell me if there is something terminally wrong with the statement. The statement checks tblstaff - if it is empty, I expect the SQL to carry on to the next statement - or maybe somebody can spot something else...
Many thanks
Paul
if (select count(*) from tblstaff) > 0
begin
truncate table tblstaff_Current
insert into tblstaff_Current
select distinct s.empnumber as Person_id, empknownas as FirstName, empsurname as Surname,
jobdesc as Job_Title, empknownas +'.'+empsurname as [StaffName],
'domain\'+empknownas+'.'+empsurname as Login
from tblstaff as s
left outer join tblstaffjob as j
on s.empnumber=j.empnumber
end
Further SQL statements proceed from this point.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Michael Lee-169622 SSCommitted Points: 1721 More actions · Answer 1

I can't help you on why it hangs. I have the same problem when I am importing from a Teradata DB. My 'solution' is to schedule another job to check on the status of the first job to see if it is still running or not, and then stop it if it is. I look at the first job's average run time and schedule the stop job to run when the first job should be done.

The job step against the msdb is:

USE msdb

EXEC sp_stop_job @job_name = 'YourJobNameHere'

I also use the 'Quit With Success' flag to page me so I know the original job failed to run. You use the 'Quit With Success' because that means the step acually stopped the job.

I hope this helps!

Michael Lee

Shalom!,

Michael Lee

Paul Sp SSCrazy Points: 2314 More actions · Answer 2

Hi Michael

Thanks very much for your reply. My table tblStaff is drawn from the Personnel system on another SQL Server. I have already implemented your suggestion - and am very glad to have some sort of solution. Maybe one day we'll both find out why the hanging occurrs.

I am very grateful for your help.

Paul

Michael Lee-169622 SSCommitted Points: 1721 More actions · Answer 3

I think it has to do with the remote system dropping the ball and not returning a response. I have played with the different time outs in the ODBC connection, but they don't seem to help.

SQL Serve is waiting for it's call...

Shalom!

Michael Lee

Shalom!,

Michael Lee

Paul Sp SSCrazy Points: 2314 More actions · Answer 4

Paul Sp

SSCrazy

Points: 2314

December 22, 2004 at 8:23 am

#534770

Thanks, Michael

Paul

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 5

Paul,

Could you clarify the following statement: "if I run select statements in QA all other tables respond - this table hangs. " It hangs in Query Analyzer all the times or it hangs in QA only during the time the job hangs?

It may be a locking problem which is really easy to detect in Current Activity window, processes view, last 2 fields if you scroll to the right, 'blocked by" and "blocking"

Another suggetion is "Distinct". Do you have enough memory for Distinct? Check the Task Manager performance tab when it happened.

Regards,Yelena Varsha

Paul Sp SSCrazy Points: 2314 More actions · Answer 6

Hi Yelena

Thanks for replying. The table only hangs in QA when the package hangs - leading me to believe that the table (which is being processed within the package) is the guilty object in making the package hang.

The package runs every few minutes every day and only hangs very occasionally - once per week tops. The staff tables aren't very large, so I guess the distinct shouldn't be too considerable an overhead.

I haven't used the Current Activity window to any degree, and, as you have now pointed it out, I'll make more use of it in future. Unfortunately I can't reproduce the hanging, so can't test it until it happens.

Many thanks for taking your time to help.

Paul

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 7

Paul,

Per your description it does look like locking, if it is only once in a while. maybe some other process accesses the table at the same time. Do use Current Activity (Processes), do use Refresh menu item because it does not refresh automatically.

I used Current Activity window to detect one of the most unusual locking problems: Read-Only user running a DTS package to get data created deadlock for the other process that was doing Update. Good thing that you can see the current status when everything hangs and users started to call you. Expand all fields, use scroll bar to get all fields and make screenshots, you can analyze them later. You will be able to see even the client computer name, user and everything for the process.

You may try to use Profiler when running a package specifying only locking events.

Have a wonderful Christmas,

I am off both today and tomorrow, but may login once

Yelena

Regards,Yelena Varsha