We've got a SQL Server 2000 box that keeps crashing every night, when the DBA group runs a job. Now, this same SQL Server is being used from Crystal Reports, running from our public web site. Add one more bug to your list for Crystal for .NET: it seems to leave (hundreds of) locked processes in “sleeping“ status. We think this is happening when the Crustal engine “Cannot obtain a free license.” My wild guess is that Crustal is not properly closing the connection when this happens. Anyhow, I've never been too worried about this, since in my experience locked processes in SQL are an everyday thing, and although certainly bad, not something that should crash a server.
Well, the DBA came in today pointing the finger at these locked processes. Her reasoning was that when the server crashes, she gets an "out of memory" error, and all these locks must certainly be taking up memory so the problem must be the locks.
Now, I'm not so hasty pointing fingers, but her explanation seems unlikely. If these hundred or so locks could be taking up the lionsshare of the 2G of memory this server, SQL Server 2000 would have some serious problems.
Anyhow, my question is this: How bad are locked processes anyhow? Are they something to worry about? Could they possibly crash SQL?
-B