May 24, 2018 at 4:15 am
Hi All,
Can someone help in creating an extended event to lock information of the currently running spids. Need an extended event session which captures below info for a specific <<database>>. I wanted to check what kind of locks are being acquire on an object.
spid,
dbid
dbname
procedurename
sqlstmt started
sqlstmt compelted
objectname
locks applied
any attentions
hostname
client app
batchstarttime
batchendtime
Suppose, if I want to execute below stored procedure. I want to see all the sql stmts along with lock info. How to do that???
create procedure usp_p1
as
begin
begin try
select * From t1 where c1 > 2 and c1 < 10;
insert into t1 values(4,'smith',9000)
update t1 set c3 = c3+3000 where c1 in (101,102,103);
delete from t1 where c1 in (1001,1002,1003);
select * From t1;
end try
begin catch
select ERROR_NUMBER();
if @@TRANCOUNT>0
rollback;
end catch
end
Thanks,
Sam
May 25, 2018 at 9:03 am
You can add teh Lock_acquired event to your trace, but that is going to be A TON of information because everything (unless using snapshot isolation) takes some kind of lock. Another option is to download sp_WhoIsActive and use the @get_locks = 1 parameter and run sp_WhoIsActive as your process is running. This will return an XML column with all locks held at the time that you run sp_WhoIsActive. This might be a little more lightweight than an XE session collecting all locks.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply