Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

IF update command is run on a table and commit is pending then another user cannot be able to update the table.

Is there any query to get the user or PC which caused lock on the table.

share|improve this question

2 Answers 2

It is possible to list the locks using the DMV sys.dm_tran_locks (sp_lock is deprecated).

The DMV query can be hard to code against and the results can be difficult to interpret. Consider using the free sp_WhoIsActive tool from Adam Machanic instead. He explains how to use it to analyse chains of blocking locks in this blog post (which also contains a download link).

share|improve this answer

Sys.dm_tran_locks  will list all current locks which are granted or pending. You can join it on session_id with  sys.dm_exec_sessions  will list all current sessions which includes client host name and login name.

You can even use the GUI - activity monitor to watch for activities going on on the server instance. (You can even profile using PROFILER and then use the T-SQL to monitor your server instance).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.