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?

Blackberry Server MSDE and active directory authentication

To all, thanks in advance,
I have blackberry server running sql msde 2000 (sp4), which is
authenticated via windows integrated with an active directory user
account. When I changed the password on said user account, restart the
mssqlserver service, it fails to start with a 560 error. I had to
changed said user account back to the old password in order for the
mssqlserver service to start. I wanted know where in ms sql msde 2000,
I needed to make the change in order for this service to start. I also
tried starting the service using local system account and it would not
start either. All help with this is greatly appreciated.
JB
Hi
Control Panel > Services. The Service can be stopped and started from there,
and the account password can be changed there for the service.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<jon.jefferson@.gmail.com> wrote in message
news:1131849352.800496.311170@.g47g2000cwa.googlegr oups.com...
> To all, thanks in advance,
> I have blackberry server running sql msde 2000 (sp4), which is
> authenticated via windows integrated with an active directory user
> account. When I changed the password on said user account, restart the
> mssqlserver service, it fails to start with a 560 error. I had to
> changed said user account back to the old password in order for the
> mssqlserver service to start. I wanted know where in ms sql msde 2000,
> I needed to make the change in order for this service to start. I also
> tried starting the service using local system account and it would not
> start either. All help with this is greatly appreciated.
> JB
>
|||Mike
Thanks for your response, but I did that and the service would not
start under with the new password. The only idea I did not try was to
restart the Server.
Thanks,
JB
|||Mike,
Also, there are other services that start with this same service
account (i.e. all the blackberry services,) and those start fine,
except for errors in the Apps event log because the msssqlserver
service would not start with the same user account change for some
strange reason. I am changing passwords to in AD because I am taking
over duties after someone has left and need to change passwords for
security purposes. This is my only hold up.
JB