November 21, 2002 at 6:29 am
I'm very new to SQL Server.
We are testing an Access front end / SQL Server backend across our WAN.
I would like to be able to tell when users logged on, how much they actually did (roughly) and when they logged off. The object being not to know exactly what users were doing but to make sure they were actually participating in the user trial.
I am monitoring who is logged on at the moment by having Access use sp_who every 15 minutes and write the result to an Access table on my PC.
I searched through books on line to see whether there is anything better than sp_who to see what users have been doing. Any suggestions?
November 21, 2002 at 6:37 am
Use profiler to log logins and logouts. These can be logged to a table in your database.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 21, 2002 at 7:43 am
Simon, that's just what I need
Many thanks
Stefan
November 21, 2002 at 10:44 am
OK I managed to get the Profiler to write audit logon and audit logoff events to a table, but this seems to record multiple rows when I am using my Access front end.
Ideally I would like to record when the Access front end opens and again when it closes.
In Enterprise Manager process info, it seems to know when an ODBC connection is first opened and then closed because it shows the connection as sleeping until I shut down Access which presumably closes the ODBC connection.
Is there a way of doing this in the profiler or do I have to record all the events to the table and then query the result. I am worried that the table will become very large.
November 21, 2002 at 12:26 pm
An logon and logoff should only occur when the connection closes, I am suprised that you are finding this.
It is likely that after a while of no use the connection is closed.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 22, 2002 at 3:15 am
OK a little clearer but still a bit muddy.
When Access opens it is using ODBC. Looks like each SPID number has a login and loginout depending on activity and there can be more than one SPID open at the same time. But even when I am not doing anything there is always one SPID with a login and no corresponding logout until I shut down Access when the number of login and logout events are equal.
I guess what I could do is start this trace running every day just recording login and logout events. And then sum the reads and writes to measure activity.
Is this likely to slow down the Server excessively?
If I then save the Trace as a template, I can create a new trace and then set it up to save to the table. Do I have to use a template or is there another way of opening and starting again a trace I have already used so for example I do not have to reinput the table details?
Edited by - stefanj on 11/22/2002 03:35:16 AM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply