0

Using following query in my procedure I can count to connection to my database

SELECT 
    COUNT(dbid) as 'Total Connections'
FROM 
    master.dbo.sysprocesses WITH (nolock)
WHERE 
    dbid = 7

This is working fine, is there any way to get length of the every connection/session in minutes?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1544004
  • 207
  • 4
  • 9

1 Answers1

2

If you do this:

SELECT DATEDIFF(n, login_time, GETDATE()) AS SessionTime
FROM master.dbo.sysprocesses WITH (nolock)
WHERE dbid =7

You will get the time difference between login_time and now in minutes, which I reckon is session time.

BEWARE OF THIS:

If the return value is out of range for int (-2,147,483,648 to +2,147,483,647), an error is returned. For millisecond, the maximum difference between startdate and enddate is 24 days, 20 hours, 31 minutes and 23.647 seconds. For second, the maximum difference is 68 years.

From here: http://msdn.microsoft.com/en-us/library/ms189794.aspx

NOTE: You should be okay as you are asking for minutes connected which is not likely to overflow any time soon!

Tom Chantler
  • 14,753
  • 4
  • 48
  • 53
  • 2
    Well, he's not going to have to worry about datediff limitations on minutes for SQL Server connections. – Jim Aug 20 '12 at 15:11
  • 1
    That's true enough ;-) But it may just be worth noting for somebody else who is after milliseconds or similar. – Tom Chantler Aug 20 '12 at 15:12