June 15, 2012 at 12:43 am
Hi,
I was looking for something to get the List of tables which have acquired Locks.
I came accross "sp_lock" but it only give the DatabaseId so I can get to know the Database but not the Tables.
Then I came accross a procedure - sp_lock2[/url].
This seemed to get the list of tables as well.
Is there another approach in which I can do this other than using the above mentioned Procedure??
June 15, 2012 at 12:49 am
It is possible to get this from sys.dm_tran_locks
June 15, 2012 at 1:06 am
June 15, 2012 at 1:40 am
Try this simplied example:
create table Tab1(id int, data char(10))
begin tran
insert into Tab1 values(1, 'One')
select OBJECT_NAME(resource_associated_entity_id) 'ObjectName'
from sys.dm_tran_locks
where resource_type = 'OBJECT'
commit
June 15, 2012 at 1:44 am
Actually you need this
select OBJECT_NAME(resource_associated_entity_id, resource_database_id) 'ObjectName'
from sys.dm_tran_locks
where resource_type = 'OBJECT'
Otherwise the OBJECT_NAME call will look in the current database for the connection, not the database where the object is as sys.dm_tran_locks is a server wide DMV not a database wide DMV.
June 15, 2012 at 2:43 am
These two queries don't show the complete results as sp_lock.
When i execute sp_lock I can see a result set consisting of a few rows of results.
But, when I execute your queries, an empty Result Set is returned.
So, it makes me think that your query is not showing the data for all the Locks in the Server at that time.
What causes the difference?
June 15, 2012 at 2:46 am
Take a look at the output from sys.dm_tran_locks and then you can modify the query to bring back what you want. Not all locks will be placed on an object, it might be at the DB level so change the where clause to match.
June 15, 2012 at 3:00 am
Here is the link to the Print Screen image that shows the result sets returned by both the queries.
One more thing I wanted to ask is that sp_lock shows Object_Id as '0' for some locks in the result Set. What does an Object_Id 0 mean?
June 15, 2012 at 3:05 am
Thats because the lock isnt tied to an object as I said in the above post, as the lock is at the DB level the DB is not an object, therefore the objid is 0
June 15, 2012 at 3:15 am
anthony.green (6/15/2012)
Thats because the lock isnt tied to an object as I said in the above post, as the lock is at the DB level the DB is not an object, therefore the objid is 0
Hmm....I need to do a lot of homework on this...I guess.
Anthony, can you refer me some good reads on "Working With Locks". Would be really helpful.
Need to get a good understanding of them to proceed any further.
June 15, 2012 at 3:25 am
Obivoulsy the usuals BOL, Google etc
This will give a overview as well http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/ chapter 6
June 15, 2012 at 3:47 am
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply