1

I'm new working with SQL Server 2008 R2 Express, and I´d like to gather some statistics every week / month for users who are connecting to this server. For instance, how many times they were connected, which table was most popular or when was the most traffic, etc.

I cannot find anything about this weekly / monthly user statistic for SQL Server 2008 R2.

Do you know how to do that?

Improve this question

1 Answer 1

Reset to default
3

SQL Server doesn't track things like table access and concurrency.

You could do this kind of thing yourself if you do some or all of these things:

  • audit logins
  • force all data access via stored procedures (and perform custom logging)
  • add triggers to capture insert/update/delete activity if you can't force data access through stored procedures
  • capture concurrency for activity by taking snapshots of DMVs (e.g. sys.dm_exec_connections, sys.dm_exec_sessions, sys.dm_exec_requests)
  • server-side trace / extended events for anything not captured above
Improve this answer
2
  • can you please tell us how can we track the Schama changes using Auditing? I tried to use SCHEMA_OBJECT_CHANGE_GROUP. My efforts are mentioned in this screenshoot.
    – RGI
    Commented Aug 28, 2012 at 7:04
  • @RGI For DDL changes, I would recommend a DDL trigger instead of auditing. Much simpler to implement and consume. I wrote a tip about this here which covered stored procedures but could easily be extended to other objects: mssqltips.com/sqlservertip/2085/… Sorry, but I haven't done anything like this with auditing.
    – Aaron Bertrand
    Commented Aug 28, 2012 at 12:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.