We're still using old Classic ASP and are wanting to log whenever a user does something in our application. We'll write a generic subroutine to take in the details we want to log.
Should we log this to say a txt file using FileSystemObject or log it to a MSSQL database?
If the database, should we add a new table to the one existing database or should we use a separate database?
Either works. It's up to your preference.
We have one central database where ALL of our apps log their error messages. Every app we write is set up in a table with a unique ID, and the error log table contains a foreign key reference to the AppId.
This has been a HUGE bonus for us in giving us one place to monitor errors. We had done it as a file system or by sending emails to a monitored inbox in the past, but we were able to create a fairly nice web app for interacting with the error logs. We have different error levels, and we have an "acknowledged" flag field, so we have a page where we can view unacknowledged events by severity, etc.,
If you have the database in place, use this instead of filesystem.
Rationale :
The decision to use existing db or new one depends - if you have multiple applications (with their own databases) and want to log / audit all actions in all apps centrally, then a centralized db might make sense.
Since you say you want to audit user activity, it may would make sense to audit in the same db as your users table / definition (if applicable).
I agree with the above with the perhaps obvious exception of logging database failures which would make logging to the database problematic. This has come up for me in the past as I was dealing with infrequent but regular network failovers.