Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Locks from inactive spid???? Expand / Collapse
Author
Message
Posted Wednesday, August 6, 2008 3:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 5,018, Visits: 10,523
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #547343
Posted Wednesday, August 6, 2008 6:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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
Posted Wednesday, August 6, 2008 6:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 5,018, Visits: 10,523
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #547456
Posted Wednesday, August 6, 2008 7:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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
Posted Wednesday, August 6, 2008 7:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 5,018, Visits: 10,523
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #547496
Posted Wednesday, August 6, 2008 7:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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
Posted Wednesday, August 6, 2008 7:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 5,018, Visits: 10,523
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #547522
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse