Results 1 to 2 of 2

Thread: Locks issue.

  1. #1
    Join Date
    Aug 2011

    Exclamation Unanswered: Locks issue.

    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.

  2. #2
    Join Date
    May 2005
    South Africa
    Provided Answers: 1
    "select @lockvar = spid, count(*)"
    1) Your error is: you are assigning spid to variable @lockvar and then also select count(*)
    select @lockvar = spid, @cnt=count(*)

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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts