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?


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
  • 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 Aug 28 '12 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 Aug 28 '12 at 12:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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