A table with a mind of it's own.

Wed Apr 14, 2004 by Derrick Leggett in b-rants-b

I ran across something today I have never encountered in SQL Server 2000; and thought I would share it with the poor souls unfortunate enough to read my blog. We had a process at work that was taking several hours to run (mainly because the stored procedures are written like crap), and about 5 days ago just stopped working. It would just run for 3-5 hours and then stop, producing an error saying it was locked by another process.

There were absolutely no other processes running on this server to “lock” the process, including the process it said was locking it. I set up a profiler, turned on Performance Monitor, ran integrity checks, recompiled the stored procedure, reindexed the table, and hoped it would go away. IT DIDN'T. How incredibly vain and stupid of me to think it would. :)

I tried everything I could think of to solve the problem and accomplished minus 5 things. The SQL Server developer rewrote a copy of the proc to use a copy of the table he created and it ran in under 15 minutes. Sooooo, I figured I would drop the table, recreate it, and test the procedure. When I went to drop the table, it wouldn't drop. The drop ran for 30 minutes and was the ONLY process running consistently during that time. The only way I could get the table to drop was to restart the SQL Server and drop it. Earlier in the week, I got the process to start working by restarting SQL Server also.

On my last badly frayed nerve, I recreated the table and tested the procedure again. The process now runs consistently around 15-20 minutes. Before it laid over and died it was taking around 1.5-2.0 hours with nothing unusual showing up in profiler except the millions of reads the inefficient code produces.

Any great ideas? Beats me!!!!!!!!!!!!!

Now onto the task of rewriting the stored procedure. It was written by an analytics group. Why is it these people never analyze their code and realize it's a horrible, smelly, disgusting pile of

e eeh eeeeh eeeeeh

effffffffff

ish ish iiiish iiiiiiiiish iiiiiiiiiiiii

iii

Legacy Comments


Lavos 2004-04-14	re: A table with a mind of it's own. No idea what was causing it, but I had something somewhat similar happen before, but can't remember what caused it :) Did you run the DBCC database and table checking functions to see if the database/table wasn't corrupted somewhere? Have you checked out sp_locks to see if there are wierd locks hanging? Also, one thing that I ran into before, was that since SQL Server's error handling left much to be desired some errors would abort a batch/command and leave a transaction stranded, leaving my tables locked. I can't remember for the life of me how to check what transactions are currently open on the server, and see how long they've been open, but it's another thing to check.

Derrick Leggett 2004-04-15	re: A table with a mind of it's own. Yeah, did all that multiple times. SQL Servers error handling is actually okay if you understand how it works and interacts with the transactions. You can see what transactions are open on the server by typing in: select * from sysprocesses where open_tran = 1

Enigma 2004-04-26	re: A table with a mind of it's own. select * from sysprocesses where open_tran >= 1 would be better .. i think