Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

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
share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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