August 22, 2001 at 8:41 am
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?
August 22, 2001 at 2:33 pm
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
August 22, 2001 at 2:58 pm
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
August 22, 2001 at 4:41 pm
That will work, but uses system tables (unsupported). I'd try to avoid that if possible.
Steve Jones
August 23, 2001 at 1:09 pm
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
August 23, 2001 at 2:19 pm
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply