Here is a situation one of my client's recently encountered on SQL Server 2005.  It was incredibly painful and a challenge to determine what was happening, plus it's an issue that is supposed to be fixed in SP2.  So wanted to share the details of the symptoms in case you experience it too.  My client is on a SP2+ build.  They ...