Agent runs DTS package, freezes

Question

Post reply

Agent runs DTS package, freezes

Oscar Janson

Old Hand

Points: 361
More actions
July 1, 2008 at 3:49 am

#209222

Hello All,
I have one DTS package scheduled to run every hour. It should dump several SQL tables to a Access database, and since outdated rows shouldn't remain, I tell it to drop the table before it inserts. This works fine for somewhere between a few runs to a couple of days. Then my customer calls that he's seeing empty tables in his application (of which the Access database provides the required data). When I log in via VPN and look at the tasks, I see that the task is still running, although the next time is scheduled to be somewhere like 6 AM or 9 PM or something.. usually times when everybody's gone home. Backups on that server run around 11 PM. In short, it looks like the package is frozen, and there doesn't seem to be a specific time it happens. The next time I try though, the task fails because it tries to drop a table that isn't there anymore. That makes sense, because if the table is dropped and the transfer fails, it isn't there next time you try. (I do think it shouldn't fail on drop though)
What I'd like to know however, is why the task freezes. A failing task at least gives me an error to work with.
I'd really appreciate it if you could give me suggestions on what to try.
Thanks in advance!
Best regards,
Oscar

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

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

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

You may try the following two scripts to see what are running on your server.

SELECT * FROM master..sysprocesses

EXEC sp_who2 active

Oscar Janson Old Hand Points: 361 More actions · Answer 2

Thanks for your reply!

I've tried your two commands and gained the following information:

The select query tells me that the cmd field of the process in question is AWAITING COMMAND

The stored procedure tells me that its status (I'm pretty sure it's one and the same, even though the SPID doesn't match, it's the only thing happening on that database) is RUNNABLE, and its command is SELECT INTO.. does that mean my DTS hangs on selecting the data rather than trying to store it?

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 3

Is there any other process running on this server beside yours? If yes, run sp_lock to see whether or not there is any object lock. If no, then, it most likely is the issue of your DTS package. You need to check whether or not there is any room for you to optimize the package.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

One thing to think about is that if something has changed and your DTS package needs input, like responding to an error in Access or something, then the package freezes. I'd stop/kill things, then run the package manually to see if anything pops up.

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 5

When running the scripts,

SELECT * FROM master..sysprocesses

EXEC sp_who2 active

pay attention to the IO and CPU time. If they do not change, your job is most likely hanging there.

Oscar Janson Old Hand Points: 361 More actions · Answer 6

Thanks for the input folks! I had to do it manually again yesterday, after which time I know things will run smooth for a while, but then I'll keep this thread as a guide for what I'll find out then.. if I again get a situation where I don't know what to do, I'll be sure and let you folks know!

Best regards,

Oscar

Oscar Janson Old Hand Points: 361 More actions · Answer 7

Hello all,

Again my problem occured. I have the following results per your suggestions:

EXECUTE sp_lock :

I am not sure what these columns are supposed to tell me, but the type says in most cases DB, 3 cases TAB, one says EXT.

Resource is empty for all records, except for the one that says EXT, that one says 1:17896. Modes for type DB are S, for TAB they are IX, for EXT it is X. The status column says GRANT for all records.

Killing the process, and trying it manually afterwards ALWAYS works. I do have to put an empty version back first to do that though, because the package always fails on not being able to delete a table.

master..sysprocesses gives me the following:

I see two processes executed a second after eachother. This is odd, because the agent only lists this job once..

The cpu and physical IO values do not appear to change...

sp_who2 active gives me the following:

Command = SELECT INTO

CPUTime = 125, 141, 188, etc

DiskIO = 22, 22, 22, etc

Although I could've sworn I saw DiskIO being incremented when I tried a short while ago... the job started around 2 am, it's 12:19 pm over here now..

Right now, the suggestion that Access might be displaying a error that doesn't get handled sounds very likely, although, I can't think of a reason of why it would give an error one time, and no error a lot of other times.. I know this is a SQL forum, but is there a way I can log something what happens at the side of Access?

Thanks again folks!

Best regards,

Oscar