Stored procedure/query executing forever (it does not complete or timeout)

I am using SQL server 2005, SQL server studio client. I am having a long stored procedure (it does a bunch of table joins, some delete, some insert and some updates) periodically running (approximately every other 2 minutes).

After I have this sp, I noticed that my database is sometimes not responding (it happens a few times when the SP is not running, and many times during the SP is running). When the DB is not responding, i can't open new connection from the SQL server studio client, if i run a query/sp the status will become running and stays that forever, until i manually reset the SQL service fr开发者_如何学运维om control panel admin-tools.

Have you seen similar problems?

Is it perhaps because the SP I newly created is doing too much things and cause the DB to crash?

---

A few more quick tips:

• You can use the sp_who2 command to view current sessions on the DB and determine if the SP is blocked, or blocking other processes.
• Check the estimated execution plan for the SP, and look for sources of slowness, like table scans.
• Your SP might be making too many changes in a single transaction and filling up the transaction log or causing it to grow. Inspect your DB's recovery mode, and whether backups are occurring regularly so that transaction log space can be reused. Consider batching large modifications into smaller chunks.

---

It sounds like your SP might be causing locks. You could use SQL Profiler to try and dig deeper into what might be happening. Here's a link providing further info -

http://www.simple-talk.com/sql/learn-sql-server/how-to-track-down-deadlocks-using-sql-server-2005-profiler/