Sql-server – SQL Server: how to monitor activity in several databases

monitoringsql servertransaction-log

I have several dbs in production and I'd like to have a high level overview of the activity.

Ideally I'd like to know:

  • number of users accessing the db per day
  • number of inserts per table per day
  • number of updates per table per day

I thought about a little script that would run a query to get the rowcount of each table, and then save it to another db. I could execute it at night.

That would only give the number of inserts, but at least would be a first step.

Is there some tool, logging device, system table, or something that would let me get this kind of info?

I have SQL Server from 2008 onward.

Best Answer

To track the inserts and updates, you can use the below query which will collect the SUM of inserts, updates and deletes against the HEAP or CLUSTERED INDEX for each user table in the database.

Log this to a table and then query the deltas over time to see the inserts\updates\deletes per day. NOTE: This would have to be run against each database you wish to monitor as these DMVs are database-scoped.

USE [Database]
GO
SELECT 
    DB_NAME() AS [DATABASE NAME],
    SUM(A.LEAF_INSERT_COUNT) AS INSERTS, 
    SUM(A.LEAF_UPDATE_COUNT) AS UPDATES, 
    SUM(A.LEAF_DELETE_COUNT) AS DELETES
FROM
    SYS.DM_DB_INDEX_OPERATIONAL_STATS (db_id(),NULL,NULL,NULL ) A 
    INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = A.[OBJECT_ID] AND I.INDEX_ID = A.INDEX_ID
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1
    AND I.INDEX_ID IN (0,1)

The user access is a little harder. If your users access the databases through an application, and that application uses a single login for SQL Server access, then you can't just track logins to determine user access. You would need to track this via your application.

If you're using individual logins, then you could use a login trigger to add a record to a tracking table to record the user, login time and initial database. See the documentation for more info. A good example can be found here. This is actually for logging sysadmin logins, but could easily be modified for your purpose.

Related Question