Locking/hanging in the db

[chrissparkle]

I have a Coldfusion website running SQL 2000 and I've got some pages that hang (forever) until I reboot the server. The more of these pages that hang it ends up stopping all requests to my website until I restart everything. It's becoming quite annoying!

There are 3 pages on my site that are hanging and the only thing they share in common is that they all make use of my "mainProfile" table in my db. I have a Members table which is the control table - member joins and they get inserted into Members and the ID is then used to insert a row into the MainProfile table. The MainProfile table memberID column is a primary key, but not an "identity" column. I'm wondering if not having it as an identity is hurting my application or maybe causing problems with unresponsive requests etc?

 

[hmckillop]

I would suggest running a trace on the SQL DB via profiler and capture the RPC:Starting and RPC:Completing.
From this you should see the proc being called and then not completing if it is a database issue.
At this point you can check in Query Analyser if it is a db problem by running sp_who and sp_Lock to see what is being blocked and by whom.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[SQLSister]

HAving an identity is easier to manage than finding the next value and inserting it, that is true. It however is not necessarily what is hanging your app. First, what indexes do you have on the table? Take your queries or stored procs to Query analyzer and look at the execution plan. Likely you are doing table scans instead of propely using indexes. Also you may have blocking problems. You need to get a good book on performance tuning to explain alot of what can cause these problems. They are not very simple to diagnose or fix.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[mrdenny]

The next time is happens use the sp_who and/or sp_who2 procedures to see what blocking is happening on the SQL Server. You can also see what objects are being locked via the sp_lock procedure.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com