I have "scenario 2" blocking issue. the process is sleeping and open transac
tions is 1 or more.
However the command that was run was a prodedure that has "No Begin Transact
ion, or rollback or commit."
Any ideas?
I don't understand how the open transactions can be greater then zero.You are probably have SET IMPLICIT_TRANSACTIONS ON .
Andrew J. Kelly SQL MVP
"mannie" <anonymous@.discussions.microsoft.com> wrote in message
news:5F627C7E-59AD-4232-B80D-588E58B932C0@.microsoft.com...
> I have "scenario 2" blocking issue. the process is sleeping and open
transactions is 1 or more.
> However the command that was run was a prodedure that has "No Begin
Transaction, or rollback or commit."
> Any ideas?
> I don't understand how the open transactions can be greater then zero.|||I have not. Perhapes the default mode has been set in some configuration at
the server level. Is this possible?|||Yes, the default for a lot of drivers is to set implicit transactions on.
You can run profiler and make sure to include the Existing connections event
to see what gets set.
Andrew J. Kelly SQL MVP
"mannie" <anonymous@.discussions.microsoft.com> wrote in message
news:A558E87B-FA4C-4FE4-B4D1-15D745B50F17@.microsoft.com...
> I have not. Perhapes the default mode has been set in some configuration
at the server level. Is this possible?
Showing posts with label sleeping. Show all posts
Showing posts with label sleeping. Show all posts
Sunday, March 11, 2012
Wednesday, March 7, 2012
Blocked sleeping active connections
Hi
We just installed SQL service pack 4. I am now finding that when doing a sp_who2 active, there are a lot of connections that are blocked by itself. The common factor is they all have a status of 'sleeping'. The strange thing is that even though it shows the connection is blocked, it is in fact not and will still return results. Below is a snapshot of a portion of what the sp_who2 active returns:
SPID Status Login HostName BlkBy DBName
53 sleeping sa TRACKER 53 dbABC
58 sleeping sa TRACKER 58 dbCDE
64 sleeping sa TRACKER 64 dbSTA
66 RUNNABLE User12 PC24 . master
70 sleeping User5 ANALYSIS 70 dbBML
74 sleeping sa TRACKER 74 dbCDE
76 sleeping sa TRACKER 76 dbPTS
83 sleeping User5 ANALYSIS 83 dbANA
86 DORMANT User11 CPTDB . NULL
Has anyone seen this? Is it related to the installation of service pack 4? (We have installed the services pack on many other SQL servers, but have not come accross this before.
Tx,
TessZAThe "self-blocking spid" is a new feature of SP4. In short, it is not actual blocking. If a spid is waiting on a latch, it shows as blocking itself. I have not seen it on this scale, though. Do you happen to know if you are suffering a lot of disk activity around the times that a lot of self-blocking spids are showing up?
We just installed SQL service pack 4. I am now finding that when doing a sp_who2 active, there are a lot of connections that are blocked by itself. The common factor is they all have a status of 'sleeping'. The strange thing is that even though it shows the connection is blocked, it is in fact not and will still return results. Below is a snapshot of a portion of what the sp_who2 active returns:
SPID Status Login HostName BlkBy DBName
53 sleeping sa TRACKER 53 dbABC
58 sleeping sa TRACKER 58 dbCDE
64 sleeping sa TRACKER 64 dbSTA
66 RUNNABLE User12 PC24 . master
70 sleeping User5 ANALYSIS 70 dbBML
74 sleeping sa TRACKER 74 dbCDE
76 sleeping sa TRACKER 76 dbPTS
83 sleeping User5 ANALYSIS 83 dbANA
86 DORMANT User11 CPTDB . NULL
Has anyone seen this? Is it related to the installation of service pack 4? (We have installed the services pack on many other SQL servers, but have not come accross this before.
Tx,
TessZAThe "self-blocking spid" is a new feature of SP4. In short, it is not actual blocking. If a spid is waiting on a latch, it shows as blocking itself. I have not seen it on this scale, though. Do you happen to know if you are suffering a lot of disk activity around the times that a lot of self-blocking spids are showing up?
Subscribe to:
Posts (Atom)