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.
1. I tried to quey the syslocks table by using,
select @lockvar = spid, count(*) from master..syslocks group by spid having count(*) > 200000
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.