Debugging a database deadlock

Question

I am trying to solve a problem with a site written in classic ASP with a SQL Server 2000 database.

Every few days the site seems to go down. There is no response from the website when you try to visit it. The loading indicator in your browser will spin round and the page just stays blank.

When I run sp_who2 after the site has gone down there's always a process that has taken up a large amount of CPU time. This process will be blocking all the other processes in the database.

I can get the site working again by killing this process.

I can't work out what's going on. When I look to see the stored procedure that this process ran before it locked up there's nothing wrong with it. The page that runs this stored procedure closes all the connection objects.

Any ideas of what could be causing this deadlock, or how I can stop it from happening?

Answer 1

Not sure if this is the issue, but it could be that not all recordsets and connection are always closed... When we had similar issues in the past we ended up with the following routine.. (Note that this is just a snippet showing one recordset closure, the real procedure actually goes over 15 different recordsets to see if they need to be closed..).

The modCloseObjects() prodedure is then always called at the end of the page, before a redirect, inside error handling and so one...

' subroutine will close and set the objects to Nothing. '
' Close Recordsets and then the Connection '
sub modCloseObjects()

    'Close the record sets one by one '
    If ucase(TypeName(oRS)) = "RECORDSET" then
        if oRS.state <> adStateClosed then
            oRS.close
            Set oRS = Nothing
         end if
    end if

    ' if you have other recordSet objects, add them to the rourtine here: '


    ' Close the connection '

    If ucase(TypeName(objConn)) = "CONNECTION" then
        if objConn.state <> adStateClosed then
             objConn.close
             Set objConn = Nothing
        end if
    end if

end sub

If you don't have adovbs.inc , you'll need the following constant too:

Const adStateClosed = &H00000000