Finding Login idle time

  • Is there a stored proc that will return the idle time for a particular login? Does anyone know any methods of finding how long a login has been logged into SQL server sitting idle?

  • There is no stored proc, but the output of exec sp_who2 contains a lastbatch column. This is the last date/time when a batch (or command) was executed by the column. Doing datediff with getdate() on this column will get the results.Try this:

    create table #sp_who2

    ( spid int

    , Status varchar( 50)

    , login varchar( 80)

    , hostname varchar( 80)

    , blkby varchar( 10)

    , dbanme varchar( 80)

    , command varchar( 500)

    , cputime int

    , diskio int

    , lastbatch varchar( 22)

    , programname varchar( 200)

    , spid2 int

    )

    insert #sp_who2

    exec sp_who2

    select

    spid

    , datediff( ss, cast(

    substring( lastbatch, 1, 5) +

    '/' +

    cast( datepart( year, getdate()) as char( 4)) +

    ' ' +

    substring( lastbatch, 7, 20) as datetime)

    , getdate() ) 'seconds'

    from #sp_who2

    drop table #sp_who2

    Steve Jones

    steve@dkranch.net

  • I think this would work too. It returns the amount of time since the last batch in minutes:

    select spid, DATEDIFF(mi, last_batch, getdate()) from master..sysprocesses

    Chad

  • That will work, but uses system tables (unsupported). I'd try to avoid that if possible.

    Steve Jones

    steve@dkranch.net

  • Steve:

    Out of curiosity, what exactly do you mean when you say system tables aren't supported? I have not heard this before.

    The reason I ask is because if you look at the sp_helptext on sp_who2, you can see it also uses sysprocesses to create a temp table (#tb1_sysprocesses).

    Your insight is appreciated.

    Chad

  • MS reserves the right to alter "system tables" during upgrades or even service packs. I have built processes in the past that used system tables and when upgrading, my solutions failed. It was not a big deal to rewrite them, but it is wasteful and time consuming.

    If you can stick to using "Supported" methods of interaction, then you are better off and these are somewhat guarenteed not to change. If you use sp_who2, MS bascially has a contract with the users that this procedure will continue to return the same results, even though the method of getting the results may change.

    Also, if you call support because you have found a problem and are using "unsupported" methods, you will not get support.

    Steve Jones

    steve@dkranch.net

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

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