Job hangs - no error Please suggest

Question

Job hangs - no error Please suggest

Viewing 8 posts - 16 through 22 (of 22 total)

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

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 1

thanks mano, i checked the articles are really interesting but none of them is related to the my job issue. I went through the checklist provided in the article, evrything looks to be fine, but still the job hangs. I am still investigating, ill keep posted. Thanks,

steve block SSCertifiable Points: 7698 More actions · Answer 2

You could try deleting the job and / or package and recreating it. Sometimes code get special characters in them that are not visible. The intermitent nature of the problem says that this will probably not work but it sounds like you are past trying likely solutions.

Steve

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 3

thanks Steve, i did delete the job and recreated. I will keep updated thanks,

Matt S.-364742 SSC Enthusiast Points: 183 More actions · Answer 4

This may or may not be related...but I ran into a similar problem with SQL Server 2000 after moving to a new server with W2K3 SP2.

There are security changes in SP2 that affect MSDTC. I had a "cross server" job that tried to do an insert in a temp table using data from a different server. The job sat there in "Executing" status.

Of course that's all pretty specific to SQL2000/W2K3 SP2...but I thought I'd throw it out hoping it might help.

BTW this doc fixed my problem: http://support.microsoft.com/kb/839279

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 5

thanks Matt, i do not see any error. ..let me see..thansk

DBA in MN Grasshopper Points: 23 More actions · Answer 6

Based upon the LastBatch time for SPID 54, it looks like it has been hung for 5 days (since the evening of 06/05) since your other spids show 06/09 and 06/10. Does this mean no log backups have run since then?

I have on occasion had issues where backup jobs appeared to hang because a network blip happened at some point in the process and SQL Server did not realize that the connection was severed. That left the job stuck as sort of an orphaned connection. My backup jobs were writing to another server on the network. This would not apply if you are backing up to the same server your database is on.

I obviously do not know if this applies in your situation, but power issues or even a port in a network switch going bad could cause the momentary disruption in the connection that leaves the backup job stuck waiting for an acknowledgement that will never come.

I have seen both network switches fail and dead rack-mounted UPS devices (i.e. no longer hold a charge on the batteries) over the years.

Max-146500 SSCarpal Tunnel Points: 4691 More actions · Answer 7

DBA in CR (6/26/2009)
...network blip...

Good thought, we normally get the ubiquitously descriptive "General Network Error" when that happens.

Ah, I've got it! Search your code for:

WAITFOR DELAY '100:00:00'

:hehe:

A slightly more professional idea; have you checked if msdb is functioning normally?

[Edit]Or base on what Matt said, restart the MSDTC services and clear the OS event logs. Hey, at this stage you should be willing to walk around a fire backwards chanting strange incantations and shaking a stick in the general direction of your server.

Max

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 8

after deleting and re-creating the job..so far it worked fine in test..i will have to observe few more days..thanks everyone for suggestions..