question

ajithkn123 avatar image
ajithkn123 asked

db tables getting locked frequently

Hello All, We are using Sql Server 2000 with peoplesoft Application.From a few days many of the tables are getting blocked(LOCKED) and no user is able to access the application during that time.Please provide some solution. Thanks and Regards, Ajith
sqlsql-server-2000
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image
Tim answered
Blocking is a normal operation in SQL Server as some process do require locking the table to perform their operation. Things like inserts, updates, deletes typically will hold a lock while the operation is being performed. These blocks/locks should be extremely short. Depending on how the instance is setup and how the query is written it can also hold a lock while the read is taking place, this is typically where we experience issues. If someone has taken an exclusive lock while searching a huge block of data, it will block all other users until that query has been completed. Basic tuning can help reduce this impact, making sure your queries are optimized, stats updated, indexes where you need them, etc. Unfortunately this is on a SQL 2000 database so you do not have the advantage of having the DMV's with SQL 2005/2008. A server side trace can help you identify the queries doing the blocking as well as sp_who2 to see the SPID causing the block then a dbcc inputbuffer (spid) to see what the query is.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

As my boss always says, we have users executing code, he wants some code to execute some users. :)
1 Like 1 ·
ThankYou Trad for you valuable reply, Firstly These locks are bringing down the application(peoplesoft app) and at that time no users can loggin and also when i checked using sp_who2 active it shows kworldreader as one of the users but i have no idea from where this user has poped up coz only sa user is given access to db along with a few developers id.Is there any performance monitoring tool which helps to increase the efficiency of sql server 2000. Secondly when i execute sp_who2 active i getting many processes with status sleeping do i need to KILL all those process to restore the system at critical situstions cause many of those process will be pointing to master db??pls dont mind to answer my question as im a newbie to sql server 2000 Thanks in advance! Ajith
0 Likes 0 ·
Drop the active, do sp_who2. Look for the SPID that is causing the blocking. Once you have that SPID you can execute DBCC INPUTBUFFER (SPID) to see what the SPID is executing. Do you have a SQL user called kworldreader? Check in your security for the userid. Once you determine what the SPID is doing to block everyone else you can either tune that or kill the spid and execute the user causing it.
0 Likes 0 ·
bopeavy avatar image
bopeavy answered
You need to give some more info do you have a trace? Whats runnning during this time? How long is it lasting? At what time(during the time you do maintenance?
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

no can u pls tell me how to set or get the trace for sql server 2000 as im a newbie to sql server!!pls dont mind. Thanks in advance Ajith
0 Likes 0 ·
Here is how to start a trace in 2000 http://support.microsoft.com/kb/283790
0 Likes 0 ·
What time is this accuring during maintenance times?
0 Likes 0 ·
sp_lock avatar image
sp_lock answered
@Trad has made to good point. If you are new to SQL, then I would suggest reviewing [this][1] article from the SQLTeam site. [1]: http://www.sqlteam.com/article/sql-server-2000-performance-tuning-tools
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.