Sql-server – SQL Server 2008 R2 – Weekly / Monthly Statistics

sql serversql-server-2008-r2statisticst-sql

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?

Best 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
Related Question