How to check which locks are held on a table

by

This is not exactly showing you which rows are locked, but this may helpful to you.

You can check which statements are blocked by running this:

select cmd,* from sys.sysprocesses
where blocked > 0

It will also tell you what each block is waiting on. So you can trace that all the way up to see which statement caused the first block that caused the other blocks.

Edit to add comment from @MikeBlandford:

The blocked column indicates the spid of the blocking process. You can run kill {spid} to fix it.

More Related Contents:

  1. Querying data by joining two tables in two database on different servers
  2. Generate a resultset of incrementing dates in TSQL
  3. SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints
  4. Favourite performance tuning tricks [closed]
  5. Can an INNER JOIN offer better performance than EXISTS
  6. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?
  7. Is it possible to write single query on two table which are not connected to each other?
  8. How does database indexing work? [closed]
  9. How to update two tables in one statement in SQL Server 2005?
  10. INSERT statement conflicted with the FOREIGN KEY constraint – SQL Server
  11. Create a date from day month and year with T-SQL
  12. Are there any disadvantages to always using nvarchar(MAX)?
  13. Fastest way to remove non-numeric characters from a VARCHAR in SQL Server
  14. SQL – Subtracting a depleting value from rows
  15. Turn off constraints temporarily (MS SQL)
  16. Get the week start date and week end date from week number
  17. Pass table as parameter into sql server UDF
  18. conditional unique constraint
  19. SELECT FOR UPDATE with SQL Server
  20. Fastest way to determine if record exists
  21. The backend version is not supported to design database diagrams or tables
  22. Performance of SQL Server 2005 Query
  23. OPENXML with xmlns:dt
  24. How to find sum of multiple columns in a table in SQL Server 2005?
  25. When using GETDATE() in many places, is it better to use a variable?
  26. Query times out from web app but runs fine from management studio
  27. The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions
  28. SQL Server 2005 – Export table programmatically (run a .sql file to rebuild it)
  29. SQL Sort Order with Null values last
  30. Insert all values of a table into another table in SQL

Leave a Comment