I have an interesting question to all friends over here.
How can we automatically capture the sqltext information by writing a stored procedue or by doing anything which captures the sqltext information of any spid which crosses a certain threshold of the number of locks? (In this case, let us assume the total locks configured is 500000 and threshold for reference is 200000)

As the error log doesn't contain the information about the sqltext of the queries which holds maximum number of locks, we wouldn't come to know which query crossed the threshold and things like that. Requesting memebers to help.


*****Failed Methods********
1. I tried to quey the syslocks table by using,
declare @lockvar
select @lockvar = spid, count(*) from master..syslocks group by spid having count(*) > 200000
dbcc traceon(3604)
dbcc sqltext(@lockvar)

Error : This says that the variable used in a select statement should not be used for retrieving value.

2. Storing the details in a temp table and accessing it.
This shows some weird syntax error.


Responses will greatly be appreciated. Thanks in advance.

"select @lockvar = spid, count(*)"
1) Your error is: you are assigning spid to variable @lockvar and then also select count(*)
Use
select @lockvar = spid, @cnt=count(*)

2) Can't see your weird syntax error from here