Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Sharan
Starting Member
2 Posts |
 Posted - 2010-01-14 : 04:27:30
|
| Hi,We have an overnight process that runs a variety of stored procedures that 9/10 will all complete in about 2 hours - lovely.Occassionally, one of those stored procedures will stuck - doing nothing for hours. We then get called and have to intervene. To fix we quit the exe that is running the stored procedures, and then run the stored procedures in sql server - and they go through no problem.This has happened on and off for years - and I would love to know why! the stored proc that hangs normally completes in a few minutes and when it restarts goes through in a few seconds. The CPU and disk activity is low when it is hanging - and I know the proc has started as I have started writing diagnostics to the event log.Anyone else experienced this? SQL Server 2005 BTW. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-14 : 04:32:40
|
Maybe it is a problem in your "exe"?Or maybe the "exe" is starting all SP's together at the same time and they are blocking in concurrent processing?
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-14 : 04:39:28
|
| Have you checked if the SProc is Blocking on anything when it gets stuck?What does the Sproc do - just raw SQL? or does it interact with other systems, the filesystem, call COM objects, and of that sort of "external" stuff? |
|
|
|
Sharan
Starting Member
2 Posts |
Posted - 2010-01-14 : 11:35:24
|
| The exe calls an SP that in turn calls various SP's in turn one after the other. When it sticks, it might be on the 5th sp for example - so I don't think it's the exe as all that does is call the parent sp.The sp that gets stuck is raw sql, reading from one database into another.When it's re-run it flies through. We then generally stop/start sql server and it's ok again for a few weeks.Next time it happens I'll look to see if it's being blocked.Cheers |
|
|
|
|
|
|
SP hangs for hours - but normally takes minutes...