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
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.