Locks from inactive spid????

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Locks from inactive spid????

Locks from inactive spid????

Rate Topic

Display Mode

Topic Options

Author

Message

spaghettidba

Posted Wednesday, August 06, 2008 3:15 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 4,804, Visits: 8,068

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

Get your two-cent-answer quickly
The Spaghetti DBA

Post #547343

Michael Earl-395764

Posted Wednesday, August 06, 2008 6:47 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078

How did you determine there was no open transaction? You can be awaiting command with transactions open.

Post #547453

spaghettidba

Posted Wednesday, August 06, 2008 6:52 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 4,804, Visits: 8,068

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.

Get your two-cent-answer quickly
The Spaghetti DBA

Post #547456

Michael Earl-395764

Posted Wednesday, August 06, 2008 7:18 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078

Ok, that is a good check.

Did the spid have a cursor open?

Post #547487

spaghettidba

Posted Wednesday, August 06, 2008 7:25 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 4,804, Visits: 8,068

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.

Get your two-cent-answer quickly
The Spaghetti DBA

Post #547496

Michael Earl-395764

Posted Wednesday, August 06, 2008 7:36 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078

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).

Post #547504

spaghettidba

Posted Wednesday, August 06, 2008 7:54 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 4,804, Visits: 8,068

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

Get your two-cent-answer quickly
The Spaghetti DBA

Post #547522

« Prev Topic | Next Topic »

Permissions