Running status

Question

Running status

SQL ORACLE

One Orange Chip

Points: 27807
More actions
November 11, 2010 at 9:07 am

#231233

When running multiple jobs on a server, we may see the status of a job in sleep while executing sp_who2 stored procedure.
Occasionally, the status kept in sleeping status forever. Is there any way in "waking up" this sleeping job?
Any input will be greatly appreciated.

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

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

nigelrivett SSCertifiable Points: 5362 More actions · Answer 1

It's probably sleeping because it can't do anything - waiting for something tpo complete?

Could be that it's doing things in short bursts and when you get the snapshot it's sleeping - check the cpu and disk io to see if they are changing.

Also could have multiple threads and the one you are viewing is waiting for another thread to complete.

There's no way that I kow of to change the priority of a thread while it's running.

Cursors never.
DTS - only when needed and never to control.

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

Initially, there were multiple jobs connecting to one database. After killing all other jobs connecting to this database except one. The remaining job is still in sleeping status forever and the CPU and IO kept the same forever.

I checked CPU & memory usage. They were low. It meaned there was no other heavy load on this server. I checked sp_lock. There was no lock at all on the whole SQL instance.

I really did not want to kill this spid and restart this job because it had run for a long time. But this job seemed dormant and I could not see any hope for it to wake up.

Steve Jones - SSC Editor SSC Guru Points: 734444 More actions · Answer 3

Can you check what commands it was last running? DBCC INPUTBUFFER? It's possible that it's stuck and needs to be killed.

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

Steve Jones - SSC Editor (11/11/2010)
Can you check what commands it was last running? DBCC INPUTBUFFER? It's possible that it's stuck and needs to be killed.

I did use DBCC INPUTBUFFER(spid) to check. The job is there but in the sleeping status.

I could kill but I was not willing to.

I could not see any lock (stuck). But the job was sleeping.

Steve Jones - SSC Editor SSC Guru Points: 734444 More actions · Answer 5

When you say the job is there, what do you mean? Is this a package execution or stored procedure? Have you looked at the code to see if there could be an issue with it?

I assume this has run correctly in the past. Could it have popped a dialog box?

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

"When you say the job is there, what do you mean? Is this a package execution or stored procedure? Have you looked at the code to see if there could be an issue with it?

I assume this has run correctly in the past. Could it have popped a dialog box? "

When I said the job is there, I meaned that it was displayed while executing sp_who2 stored procedure.

This job was the execution of a stored procedure.

Executing this job could go through normally, i.e., the job could run successfully in the past. It was hanging there occasionally.

No pop-up dialog box.