View lock information using Activity Monitor

I expertr. i need to know about
View lock information using Activity Monitor
Use the dynamic management view to view lock info
an url please or article,
enrique_aeoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dwkorCommented:

/*
Shows current locks. Even if it works across all database, ObjectName 
populates for current database only. Could be modified with dynamic SQL if needed

Be careful with Query text for LOCKS with GRANT status. This represents currently active
request for this specific session id which could be different than query which produced locks
It also could be NULL if there are no active requests for this session
*/

select
	TL1.resource_type
	,DB_NAME(TL1.resource_database_id) as [DB Name]
	,CASE TL1.resource_type
		WHEN 'OBJECT' THEN OBJECT_NAME(TL1.resource_associated_entity_id, TL1.resource_database_id)
		WHEN 'DATABASE' THEN 'DB'
		ELSE
			CASE 
				WHEN TL1.resource_database_id = DB_ID() 
				THEN
					(
						select OBJECT_NAME(object_id, TL1.resource_database_id)
						from sys.partitions
						where hobt_id = TL1.resource_associated_entity_id
					)
				ELSE
					'(Run under DB context)'
			END
	END as ObjectName
	,TL1.resource_description
	,TL1.request_session_id
	,TL1.request_mode
	,TL1.request_status
	,WT.wait_duration_ms as [Wait Duration (ms)]
	,(
		select
			SUBSTRING(
				S.Text, 
				(ER.statement_start_offset / 2) + 1,
				((
					CASE 
						ER.statement_end_offset
					WHEN -1 
						THEN DATALENGTH(S.text)
						ELSE ER.statement_end_offset
					END - ER.statement_start_offset) / 2) + 1)		
		from 
			sys.dm_exec_requests ER 
				cross apply sys.dm_exec_sql_text(ER.sql_handle) S
		where
			TL1.request_session_id = ER.session_id
	 ) as [Query]
from
	sys.dm_tran_locks as TL1 left outer join sys.dm_os_waiting_tasks WT on
		TL1.lock_owner_address = WT.resource_address and TL1.request_status = 'WAIT'
where
	TL1.request_session_id <> @@SPID
order by
	TL1.request_session_id

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
enrique_aeoAuthor Commented:
very interesting, and could generate a block to see how it works your query
0
robertg34Commented:

sp_who is a simple command to look for locking transactions.  Beyond that, here is a query that gives more information
from http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!906.entry
 
 

select  login_name,
        case des.transaction_isolation_level
            when 0 then 'Unspecified' when 1 then 'ReadUncomitted'
            when 2 then 'ReadCommitted' when 3 then 'Repeatable'
            when 4 then 'Serializable' when 5 then 'Snapshot'
        end as transaction_isolation_level,
        request_session_id, resource_type, resource_subtype, request_mode,         request_type, request_status, request_owner_type, 
        case when resource_type = 'object' then object_name(resource_associated_entity_id)
             when resource_type = 'database' then db_name(resource_associated_entity_id)
             when resource_type in ('key','page') then                                  (select object_name(object_id) from sys.partitions                                   where hobt_id = resource_associated_entity_id)
             else cast(resource_associated_entity_id as varchar(20))
        end
from sys.dm_tran_locks dtl
        left outer join sys.dm_exec_sessions des
            on dtl.request_session_id = des.session_id
where request_session_id <> @@spid

Open in new window

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

enrique_aeoAuthor Commented:
Hello, I have understood my question isas I can generate a lock on the database?
0
dwkorCommented:
do you want to generate the lock? well. the siblest way is:
begin tran
  update table1 set.. where..

it will place exclusive locks on the updated records till you commit or rollback transaction
0
enrique_aeoAuthor Commented:
Hello, I made the query, but not blocking it generates no

begin tran
  update HumanResources.Employee
  set fechaRegistro = null
  where fechaRegistro is null
0
dwkorCommented:
open the second session and run
select * from humanresouces.employee
0
enrique_aeoAuthor Commented:
first session
begin tran
  update HumanResources.Employee
  set fechaRegistro = null
  where fechaRegistro is null

second session
select * from HumanResources.Employee

 but not blocking it generates, i attached the results
resultSESSION.JPG
0
dwkorCommented:
are you sure your update statement updated any rows?
0
enrique_aeoAuthor Commented:
That's right, the update did not update records, and i fixed it, thank you very much for your patience. Regards
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.