Results 1 to 5 of 5

Thread: How to capture first & last active connection per user?

  1. #1
    Join Date
    Jun 2003
    Location
    Illinois
    Posts
    4

    Question How to capture first & last active connection per user?

    Any suggestions on how to create a report to show when someone first connected to SQL Server and when they last processed anything on SQL Server?

    I wrote a query to check sysprocesses every 10 minutes, but it only reports that the person has a connection. It does not tell me if the connection is active. I thought I may need to look at processor and disk to see if those numbers change, but I'm not sure if that is the best approach. Any thoughts?

    Thanks, Dave

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    You can query master..sysprocesses and look at login_time and last_batch columns.

  3. #3
    Join Date
    Jun 2003
    Location
    Illinois
    Posts
    4
    Thanks. Last_Batch will help, but I believe it only records the time a stored procedure was last executed. Since most of the code utilizes stored procs this should help, but I was kinda hoping to find a catch-all solution.

    Thanks again, Dave

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Last_Batch time is the last time any sql statement is sent from the client to server.

    I think BOL is not correct on saying only stored procedure and EXECUTE statement time is captured, you can verify this by running this

    select login_time, last_batch from sysprocesses where spid=@@spid

    to display last time you executed anything.

  5. #5
    Join Date
    Jun 2003
    Location
    Illinois
    Posts
    4
    Thanks for the info about BOL. I've never tried to verify their statements, just assumed they were correct. I just ran a quick test running a dynamic query of sysobjects and sure enough the timestamp changed.

    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •