Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

View lock information using Activity Monitor

Posted on 2010-08-13
10
Medium Priority
?
955 Views
Last Modified: 2012-05-10
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,
0
Comment
Question by:enrique_aeo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 13

Accepted Solution

by:
dwkor earned 2000 total points
ID: 33434913

/*
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
 

Author Comment

by:enrique_aeo
ID: 33435900
very interesting, and could generate a block to see how it works your query
0
 
LVL 5

Expert Comment

by:robertg34
ID: 33438207

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:enrique_aeo
ID: 33442487
Hello, I have understood my question isas I can generate a lock on the database?
0
 
LVL 13

Expert Comment

by:dwkor
ID: 33442672
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
 

Author Comment

by:enrique_aeo
ID: 33442683
Hello, I made the query, but not blocking it generates no

begin tran
  update HumanResources.Employee
  set fechaRegistro = null
  where fechaRegistro is null
0
 
LVL 13

Expert Comment

by:dwkor
ID: 33442824
open the second session and run
select * from humanresouces.employee
0
 

Author Comment

by:enrique_aeo
ID: 33442843
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
 
LVL 13

Assisted Solution

by:dwkor
dwkor earned 2000 total points
ID: 33442867
are you sure your update statement updated any rows?
0
 

Author Closing Comment

by:enrique_aeo
ID: 33442938
That's right, the update did not update records, and i fixed it, thank you very much for your patience. Regards
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Loops Section Overview

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question