Scheduled Stored Procedure Causes SQL Server to stop responding.

Hi,
We have a scheduled stored procedure running on SQL Server (enterprise
server I believe). This stored proc is one of a set of procs run by one
job, it takes about 11 minutes to do everything.
Sometimes, during the execution of the Job, the server seems to not
respond to connection requests from our website, giving the following
error:
Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding.
I didn't think that running a stored procedure (even if it is a bit of a
beast) would bring down SQL server. Does anyone have any idea what we
are doing wrong?
Thanks for your help & time,
Austin Rathe
*** Sent via Developersdex http://www.codecomments.com ***Can you use performance monitor to see what actually is happening to the
system when that stored proc was running? Check to see if the CPU is maxing
out or the disks are being thrashed? May be there's scope for optimising
that stored proc. Could you post the code?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Austin Rathe" <austin#REMOVETHIS#@.gametart.com> wrote in message
news:O3Fkl8WfFHA.3316@.TK2MSFTNGP14.phx.gbl...
Hi,
We have a scheduled stored procedure running on SQL Server (enterprise
server I believe). This stored proc is one of a set of procs run by one
job, it takes about 11 minutes to do everything.
Sometimes, during the execution of the Job, the server seems to not
respond to connection requests from our website, giving the following
error:
Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding.
I didn't think that running a stored procedure (even if it is a bit of a
beast) would bring down SQL server. Does anyone have any idea what we
are doing wrong?
Thanks for your help & time,
Austin Rathe
*** Sent via Developersdex http://www.codecomments.com ***