Auditing Database Usage on an SQL Server Instance

During a recent project with a Central Government client I was required to carry out some analysis on a legacy SQL Server instance. This was with a view to either migrate or delete the stored databases and eventually retire the instance.

The SQL instance had once been the main production location for databases to be stored but had been superseded by a new data store. There was no documentation available on the history of the databases so I needed to carry out auditing of usage to determine which, if any, databases were still in use, by whom and the volume of that usage.

Since SQL Server 2016, all versions of SQL Server support server and database level auditing as an built-in feature that can be activated. In this case due to the resources required to implement, it was not a possible option. You can read more about this here – SQL Server Audit (Database Engine) – SQL Server | Microsoft Learn

Instead a more lightweight approach was required. I created my own audit database within the instance to store various tables of usage data and then created a number of scripts to capture that data.

Data to Capture

Connections to a Database

SQL Server provides access to a server-scope view that shows information about all active user connections and internal tasks called sys.dm_exec_sessions. As well as showing which users are connecting to which databases it also provides information on the program used to connect.

I used the following query to retrieve the active user connections and stamp them with date & time of collection.

SELECT
NAME
  ,login_time
  ,getdate() AS DATE
  ,STATUS
  ,host_name
  ,program_name
  ,nt_user_name
  ,login_name
FROM sys.databases d
LEFT JOIN sys.dm_exec_sessions s ON d.database_id = s.database_id
WHERE s.database_id NOT BETWEEN 0
AND 4
AND login_name IS NOT NULL

I extended this script to insert the results into the audit database and used it to create a stored procedure. As this information is only a snapshot in time I scheduled this stored procedure to run every 30 minutes using the SQL Server Agent Job feature which would then capture connections over a period of time.

Transactions within a Database

SQL Server contains a range of performance counters that can be accessed by querying sys.dm_os_performance_counters. I wanted to record the transactions carried out within each database as this would provide a good information of how much they were being used.

I used the following query to retrieve the Transactions/sec performance counter and stamp them with a date of collection.

SELECT
  instance_name
  ,counter_name
  ,cntr_value
  ,GETDATE()
FOM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Transactions/sec%'
AND instance_name NOT IN ('tempdb', 'master', 'msdb', 'model', '_Total')

Again I extended this script to insert the data into the audit database, created a stored procedure and scheduled to run periodically. This time on a daily basis so I could measure the daily growth of the transaction counter for each database.

Dependencies within a Database

Before any migration and retirement plan could be created it was important to find out any dependencies between each database as we would not want to move or remove something that in turn stopped another process from running.

Dependencies can be accessed by querying sys.sql_expression_dependencies.

Querying this catalogue view returns results for the current database, not all databases. I wanted to capture this information for all databases in the instance and I wanted to do this programmatically so I needed to use the built-in stored procedure sp_MSforeachdb. This stored procedure allows a query to be passed which is then run against all databases in the instance. The ‘?’ placeholder substitutes the database name as the procedure is run.

I used the following query to get dependencies for all except system databases.

EXEC sp_MSforeachdb
'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')
BEGIN USE [?]
SELECT  
  DB_NAME() AS Source_Database
  ,OBJECT_NAME (referencing_id) AS referencing_object 
  ,referenced_database_name 
  ,referenced_schema_name 
  ,referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referenced_database_name IS NOT NULL
AND is_ambiguous = 0;
	END'

I extended again to insert the data into the audit database and scheduled to run daily. In this case the old data was truncated as I did not want to keep any history of dependencies, I just wanted to record what was currently active.

Storage Size of a Database

To correctly plan for migration I wanted to know the size of each database. This information can be accessed by querying sys.master_files.

I used the following query to retrieve the information.

SELECT
  database_id,
  ,name
  ,size
  ,size * 8/1024 as 'Size (MB)'
  ,GETDATE() as DateRecorded
FROM sys.master_files
WHERE database_id NOT IN (1,2,3,4)

Note here that the size field within sys.master_files provides a figure relating to each 8KB page so I added an additional column to calculate this in MB.

I again modified this script to insert the data into the audit database and scheduled to run daily. Capturing this data with a time stamp and keeping a history over time would provide extremely useful data on the growth rate of each database. In this particular case I did not capture the history as the databases within this instance held static reference data so were not growing, therefore this wasn’t going to assist me with any meaningful information.

Analysing & Visualising the Audit Data

Once my usage data had started to be recorded within my audit database I needed to effectively analyse it to understand what volume and type of usage was happening within the SQL Server instance.

To carry out this analysis I used Microsoft Power BI to connect to my audit database and produce a report providing a number of visualisations.

A summary page displays visualisations of the entire instance to see usage overall and quickly see which databases are heavily used and by whom.
A database specific page allowing a database to be chosen and then all detail for that specific database to be displayed.

I will cover the production of this Power BI Report in a future blog post.

The capturing of this information and then produce these visualisations has allowed planning to move forward rapidly on retiring this database instance and has left the client in a much better position to progress this work.

If you have an SQL Server requirement, are looking at a data migration and don’t know where to start or just need some advice around digital transformation, come and talk to us at risual about how we can help – risual.com/contact/

About the author