-
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
-
You can query master..sysprocesses and look at login_time and last_batch columns.
-
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
-
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.
-
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
-
Forum Rules
|
|