During the migration of a client’s SQL environment to a new hosting provider, it was questioned whether all of the databases needed to be migrated. Nobody seemed to know whether several of the databases were used or what they were for. One person suggested we just not move them, and see if anybody screamed or if it broke anything. Well, I guess that’s one way to find out if a database is being used. But there should be more definitive ways to do this, right?

There really is no straightforward way to determine this, but there are several things we can look at to get a better idea.

• Look at any current connections
• Capture login/connections over period of time
• Observe index usage
• Look at transaction count
• Find dependencies with Jobs or other DBs

Current connections

sp_who2 is a well-known stored procedure that returns information about current users, sessions and processes. You can run exec sp_who2 to return all sessions belonging to the instance, or you can filter to return only the active sessions:

--Find active connections to the instance
USE master;
GO
EXEC sp_who2 'active';
GO

Information about processes on your instance can also be derived from sys.sysprocesses. This system view will be deprecated in future releases of SQL. The info in this view returns data about both client and system processes running on the instance. The following statements will filter data relating to a specific database:

--Find number of active connections to 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)
--Active Connections to Database with connecting IP address
SELECT
s.host_name,
s.program_name,
s.login_name,
c.client_net_address,
db_name(s.database_id) as DBName,
s.login_time,
s.status,
GETDATE() as date_time
FROM sys.dm_exec_sessions as s
INNER JOIN sys.dm_exec_connections as c on s.session_id = c.session_id
INNER JOIN sys.sysprocesses as p on s.session_id = p.spid
WHERE DB_NAME(p.dbid) = 'AdventureWorks2016'  --insert your database name here

Connections over time

It might be more beneficial to watch the connections to a database over a period of time instead of looking at the current connections only. In order to gather this data over time, you could create a trace through SQL Server Profiler. Simply run the trace, export it as a definition file, and import it into a table to query the results. You can also create a SQL Server Audit to record sucessful logins, but these are at the server level, not the database level. For a good explation on how to perform both of these tasks click here: https://mssqltalks.wordpress.com/2013/02/25/how-to-audit-login-to-my-sql-server-both-failed-and-successful/ . Depending upon how long you run the Profiler or Audit, these files can take up a considerable amount of space so be sure to monitor your disk space carefully.

Index Usage

Another way to see if your database is in use is to look and see if the indexes are being used. Information on index usage is held in the sys.dm_db_index_usage_stats DMV since the last server reboot, and can be queried using this statement which can be tailored to select the data you need.

SELECT
DB_NAME(database_id) 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

The statement above will show you the date and time the indexes for your database were last used. For the reverse of that, if you want to see which database have not had the indexes used since the last server reboot, run the following statement:

SELECT name DatabaseName
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')
EXCEPT
SELECT DISTINCT
DB_NAME(database_id) DatabaseName
FROM sys.dm_db_index_usage_stats
ORDER BY 1

Transaction Count for the Database

Checking to see if the number of transactions are increasing for a database is another way to see if it is being used. You can query the sys.dm_os_performance_counters DMV for Transactions/sec and run this several times to see if the count is increasing or not. Or you can open Perfmon and watch it there as well.

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

Database Dependencies

Occasionally other databases or linked servers will connect to your database. To see objects in your database that are referenced by other databases, run this statement:

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;

For finding object referencing linked servers use this:

SELECT OBJECT_NAME (referencing_id) AS referencing_object,
referenced_server_name,
referenced_database_name,
referenced_schema_name,
referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referenced_server_name IS NOT NULL
AND is_ambiguous = 0;

Database dependencies can be a very in-depth topic, and the statements above are only meant for high-level information about connections to your database. For more information about this topic click here https://www.mssqltips.com/sqlservertip/2999/different-ways-to-find-sql-server-object-dependencies/ .

Conclusion

There are countless ways to determine if your database is being used. Other methods that could be used is to see if there are execution plans are in the cache referencing the database, see if reads/writes are happening, look at lock_acquired events, and many other methods I haven’t thought of. The methods outlined above provide a useful starting point to investigate who/what is connecting and if your database is active or not. Yes, you can just take the database offline or detach, or even delete it and see if anyone fusses. But it’s much more prudent to take a look around at some simple things to make that determination.