SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Locks from inactive spid????


Locks from inactive spid????

Author
Message
Gianluca Sartori
Gianluca Sartori
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10038 Visits: 13351
This morning my users called me to report that all applications were hung.
I looked at the processes and I found lots of spids blocked by spid 150.
When I run
sp_who2 150

it was AWAITING COMMAND and no transaction open.
sp_lock 150

reported lots and lots of objects with locks X and IX.

What could it be? Can a process hold locks when no transaction is open and no command is being performed?

Any help would be greatly appreciated
Regards
Gianluca

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Michael Earl-395764
Michael Earl-395764
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5093 Visits: 23078
How did you determine there was no open transaction? You can be awaiting command with transactions open.
Gianluca Sartori
Gianluca Sartori
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10038 Visits: 13351
In Activity Monitor there's a column named "Open Transactions", that contained a 0 for my mysterious spid... I don't know where that information is extracted from, but I think I can rely on it.

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Michael Earl-395764
Michael Earl-395764
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5093 Visits: 23078
Ok, that is a good check.

Did the spid have a cursor open?
Gianluca Sartori
Gianluca Sartori
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10038 Visits: 13351
Well, I don't know if it had cursors open in that moment, all I know is that it opened 2 (small) cursors, but I checked and the deallocate instruction was in the right place in the procedure. The first thing that comes to my mind is to get rid of the cursors, but I still can't get any sense out of this.

I've been working for many years with different versions of SQLServer and I never ran into this kind of problem.

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Michael Earl-395764
Michael Earl-395764
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5093 Visits: 23078
Were the cursors related to the locked records? That would be the first place to look. Is it an updatable cursor? If it is and does not need to be, perhaps just using the READONLY keyword will solve your issue (you will not get exclusive locks).
Gianluca Sartori
Gianluca Sartori
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10038 Visits: 13351
In fact the cursor was declared without any option, so I switched it to FAST_FORWARD, just to get a quick fix. I'll try to code it without cursors, also because there's no need for cursors in that context.

Thank you very much for your help, I'll see in the next days what's happening with these modifications.

Regards
Gianluca

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search