I have a job that has run without problem for months. It runs on both our test/dev server and our production server. The databases and jobs are identical in both environments.
A few days ago, the job stopped working in the production environment. It still works fine in the test/dev environment.
The job runs for Stored Procedures. I was first alerted to the problem when someone notified me their query wouldn't work. I found the job, which normally takes about 12 minutes had been running for 3.5 hours. It had hung on the third SP. It runs every 3 hours and had run fine the previous scheduled time.
I killed the job and since, cannot even do the first SP. The first SP has four steps: 1. Retrieve data from a linked server into a temp table (works fine). 2. Delete data from a regular table where the date range matches that retrieved from the linked server. 3. Insert the data retrieved into the regular table. 4. Rebuild indexes.
The data retrieval from the linked server works find and I can see the data in the temp table, query it, etc. However, when it goes to delete step, nothing seems to happen. It uses CPU, but a process that normally takes 3-5 seconds (based on history), will go forever it seems. I've let it go as long as 45 minutes before cancelling the process.
dbcc checkdb shows no errors. I have restarted SQL Server, then rebooted the physical server, checked hard disk space, rebuilt all indexes, examined memory and so forth. I have no clue where to go next.
After rebooting the physical server, I immediately retried the first SP before anyone or any other processes could cause a contention issue and still had the same results.
Any suggestions?
Thank you.
Check out my blog post Unexplained SQL Server Timeouts and Intermittent Blocking
Here is the section that might give you the clue you need.
If the stored proc getUserPrivileges didn’t have enough problems for you, then let me add: it probably gets recompiled on each call. So SQL Server acquires a COMPILE lock on each call. The reason it gets recompiled is because the temp table gets created and then a lot of rows are deleted from it.... This will cause the stored proc to be recompiled on the SELECT that follows (yes, in the middle of running the stored proc.) In other procs I’ve noticed a DECLARE CURSOR statement whose SELECT statement references a temporary table – this will force a recompile too.
For more info on recompilation see: http://support.microsoft.com/kb/243586/en-us
So it makes sense to me that it might work in some environments and not others depending on how much deleting is going on even if it is in a permanent table.
Can you break your stored proc into 2. The first would get the delete date range and do the deletes. The second would then process the new inserts. I think this will make it so it doesn't kick off a recompile in the middle of running.