SQL Server Performance Forum – Threads Archive

locked table

Yes, again lock problem. Assume that a user walkes away without closing his/her application and it locks a table (even a shared lock). We would like a way for the DB to trap and handle SQL Server errors, and allow us to better identify these types of issues. How can I automate it? Thanks, CanadaDBA

USE master
SELECT *
FROM sysprocesses
WHERE blocked > 0 GO –EXEC sp_lock
–EXEC sp_who
–EXEC sp_who2
–KILL I use this often to resolve locking issues.

The proper thing to do is fix the application. The application should be locking other people just to view data. What type of application are you using here? If you can’t do that, then use the solution presented here. You’ll need to automate a process that gets all blocked processes and looks at the blocking SPID (You can use DBCC INPUTBUFFER(‘spid’) for this). You will then need to analyze and create trends to see where your failure points are. After, you can correct the application or create an automated process to KILL appropriate processes. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

http://www.sql-server-performance.com/reducing_locks.asp
http://www.sql-server-performance.com/at_sql_locking.asp If you have a single query that is performing poorly the best way to identity why is to run it in query analyzer while displaying the graphical execution plan. You will then be able to see where the time/resource is going. If you are trying to identify what is running slowly then you should monitor your query activity with SQL Profiler, capturing queries that are consuming excessive resource. I would recommend that you remove all locking hints and see how the queries perform. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>