Post reply

sysprocesses: Why are some login name values empty

  • November 24, 2015 at 8:00 am

    #305490

    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

  • November 24, 2015 at 8:03 am

    #1842063

    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

  • November 24, 2015 at 8:16 am

    #1842073

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

  • November 24, 2015 at 8:24 am

    #1842080

    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

  • November 24, 2015 at 8:35 am

    #1842085

    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.

  • November 24, 2015 at 8:43 am

    #1842089

    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

  • November 24, 2015 at 8:51 am

    #1842095

    Thanks

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

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