May 15, 2002 at 6:53 am
Hi Guys,
We are using SQL Server 2000 as our database and Visual Foxpro as our application. In some cases the SQL Server didnot process any query and it seems like hanging the SQL Server.We used to stop and start the SQL Server whenever we experienced the above problem.
We believe that this may be because of blocking/locking . What can we do to overcome this without affecting the performance?
Is their any option to find the lock/block automatically whenever it occurs and release the block/lock without stoping and restarting the SQL Server ?
Also how can I set the lock timeout period for all session ? [ie, from the online help it seems that if we run set LOCK_TIMEOUT 10000 this will apply only to the particular session. If the session finished, the lock out time again shows as -1 . I want to make the lockout time as permanent one until anyone change the lock out time.]
Thanks
Jerish
May 15, 2002 at 7:40 am
Changing the lock timeout isnt going to help a lot, better to fix the problem. Start by running sp_who and checking the blocked column - it will give you the spid that is blocking. Often they will chain so you have to follow until you reach the one connection that started the blocking. Then runn dbcc inputbuffer(spid#) to see what command it sent over. Once you know what/where the block is being generated you can look at various strategies to correct (add/change index, serialize access order, tune the query, etc).
Andy
May 15, 2002 at 7:41 am
Hi,
First off, to view the current locks in Query Analyser, you can use the sp_lock tool. This gives you plenty of info. Or you can use EM. Drill down to Management->Currant Activity and you can then view locks in a variety of ways.
Also, you can run a trace using profiler to determine the queries that are causing these issues. Check the spids returned in the locks against the spids in the trace.
I'd start off there...
Clive
Clive Strong
May 20, 2002 at 12:58 pm
Found this little beauty on Swynk, maybe it'll help!
http://www.swynk.com/downloads/findbp.sql
Terry Crosby
Terry
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply