Insert single row taking an hour

I'm currently looking into an issue on one of our SQL 2008 databases where it keeps appearing to lock up. It will be running fine for hours, then suddenly all our applications using that database will stop working. It's been happening for about 2 weeks now, before that there were no issues. Nothing much has changed in that time, certainly nothing that should cause this issue.

I've had SQL Profiler running to try and figure out what is go开发者_开发技巧ing on. It shows hundreds of queries running every second throughout the day no problem at all, then suddenly there is a 10 minute gap where only a few queries have run. When looking around that time, I found an insert query that had been running for just over an hour. That same query (but different data) has been seen to have a lock on the database on several occasions when everything has frozen up.

Usually what we have to do to get it running is restart the sql server service, obviously that gets rid of any locks and kills any queries, and everything goes again. However it then stops again, sometimes within the hour, other times several hours later.

It's a simple insert which basically just says "insert into table (x, y, z) select x, y, x from sourcetable". When I run the select part of the statement that took an hour, it takes no time to run (literally it says 00:00:00), so I can't quite understand what is going on.

We did get a few errors coming up for a while saying that one of the tables may be corrupt(not the destination or source of the insert statement though). We ran the DBCC command on that table but it didn't report any errors.

I'm going to have a look at our .net application that is running that statement to see if there is anything there that is causing it, but its unlikely and wondering if anyone has seen anything like this before and knows what might be the cause?

Thanks in advance.

The server will tell you why it's waiting if you ask it the right questions. Take a look at sys.dm_exec_requests and sys.dm_os_waiting_tasks for the session_id that's hanging and see what it's waiting on. That might give you some insight into what's going on.