Have you ever wondered if a database in your SQL Server environment is actually being used? It can be a challenging task to determine the usage of a database, especially if nobody seems to know what it’s for. In this article, we will explore some methods to help you determine if your SQL Server database is being used.

1. Look at Current Connections

One way to determine if a database is being used is to look at the current connections. You can use the sp_who2 stored procedure to get information about current users, sessions, and processes. By filtering the results, you can see the active connections to a specific database.

USE master;
GO
EXEC sp_who2 'active';
GO

You can also query the sys.sysprocesses system view to get information about both client and system processes running on the instance. By filtering the results based on the database name, you can find the number of active connections to a specific database.

USE master;
GO
SELECT DB_NAME(dbid) AS DBName,
       spid,
       COUNT(dbid) AS NumberOfConnections,
       loginame,
       login_time,
       last_batch,
       status
FROM sys.sysprocesses
WHERE DB_NAME(dbid) = 'AdventureWorks2016' --insert your database name here
GROUP BY dbid, spid, loginame, login_time, last_batch, status
ORDER BY DB_NAME(dbid)

2. Capture Connections Over Time

Instead of just looking at the current connections, it might be more beneficial to capture the connections to a database over a period of time. You can achieve this by creating a trace through SQL Server Profiler or by setting up a SQL Server Audit. By analyzing the captured data, you can get a better understanding of the usage patterns of your database.

3. Observe Index Usage

Another way to determine if your database is being used is to look at the usage of its indexes. The sys.dm_db_index_usage_stats DMV provides information on index usage since the last server reboot. By querying this DMV, you can see when the indexes for your database were last used.

SELECT DB_NAME(database_id) AS DatabaseName,
       last_user_seek,
       last_user_scan,
       last_user_lookup,
       last_user_update
FROM sys.dm_db_index_usage_stats
WHERE db_name(database_id) = 'AdventureWorks2016' --insert your database name here

4. Check Transaction Count

Monitoring the transaction count for a database can also give you insights into its usage. You can query the sys.dm_os_performance_counters DMV for the Transactions/sec counter to see if the count is increasing over time.

SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Transactions/sec%'
AND instance_name LIKE 'AdventureWorks2016%' --insert your database name here

5. Find Database Dependencies

Occasionally, other databases or linked servers may connect to your database. By querying the sys.sql_expression_dependencies system view, you can find objects in your database that are referenced by other databases. This can give you an idea of the dependencies and connections to your database.

SELECT 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;

These are just a few methods to determine if your SQL Server database is being used. There are many other techniques you can explore, such as checking execution plans, monitoring reads/writes, and analyzing lock events. By taking the time to investigate these factors, you can make an informed decision about the usage of your database.

Remember, it’s always better to gather data and analyze it rather than simply taking a database offline or deleting it without understanding its usage. By following these methods, you can gain valuable insights into who/what is connecting to your database and whether it is actively being used.