sysprocesses: Why are some login name values empty

  • While watching the sysprocesses information to try to determine a connection issue I notice that most of the time when there are suspended connections they have a blank login name. The sleeping connections always show the connection to my IIS server. I can see system processes as expected.

    Why are some connections listed without a login name?

    Thanks

    ST

  • Can you show some examples? Attach a screenshot?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • --Query

    SELECT

    DB_NAME(dbid) as DBName, status,

    COUNT(dbid) as NumberOfConnections,

    loginame as LoginName

    FROM

    sys.sysprocesses

    WHERE

    DB_NAME(dbid) IN ('XXXXXXXXXXXXXX')

    GROUP BY

    dbid, status, loginame

    -- Output

    DBName Status NumberOfConnections LoginName

    ------------------------- --------------- -------------------- -------------------------

    FOO background 6 svcaccount

    FOO2 sleeping 1 web_connection

    FOO3 sleeping 1 web_connection

    FOO runnable 1 other_service

    FOO sleeping 50 web_connection

    FOO suspended 9

    FOO suspended 1 web_connection

  • Odd... Maybe a side effect of it being a deprecated view.

    Why are you using sysprocesses anyway? It's included only for backward compatibility with SQL 2000.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Can you suggest a different view? I'm monitoring the activity on the server to see how many connections there are and the state. I'm tracking down a connection timeout issue.

  • One of the execution-related DMVs, either ...exec_sessions or ...exec_connections, depending what you're trying to do.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply