In this article, we will discuss how to identify unused SQL databases in SQL Server. Unused databases can take up valuable disk space and impact performance, so it’s important to regularly identify and remove them.

Approach 1: Counting User Connections

One approach to identify unused databases is by counting the number of user connections. We can create a SQL Server Agent job that queries the sys.sysprocesses DMV and saves the query output in a table.

Here’s an example query to get the number of user connections:

SELECT @@ServerName AS [Server Name],
       NAME AS [Database Name],
       COUNT(STATUS) AS [Total Number of User Connections],
       GETDATE() AS [Query Execution Time]
FROM sys.databases
LEFT JOIN sys.sysprocesses ON sys.databases.database_id = sys.sysprocesses.dbid
WHERE sys.databases.database_id < 4
GROUP BY NAME
ORDER BY COUNT(STATUS) DESC

We can schedule the execution of this query using a SQL Server Agent job. The job should be configured to run every 10 minutes and insert the query output into a table.

Once the job has run for a few days, we can determine the list of unused SQL databases by querying the table and selecting the databases with the maximum number of connections as 0.

Approach 2: SQL Server Logon Trigger

Another approach to identify unused databases is by using a SQL Server logon trigger. This approach involves creating a logon trigger that inserts login details into a table whenever a user establishes a connection to the SQL Server instance.

Here’s an example code to create a logon trigger:

CREATE TRIGGER [InsertLoginDetails] ON ALL SERVER FOR LOGON AS
BEGIN
    INSERT INTO [DBA].[dbo].[tblconnection]
    SELECT ORIGINAL_LOGIN(), HOST_NAME(), USER, @@SPID, GETDATE(), APP_NAME(), ORIGINAL_DB_NAME()
    WHERE ORIGINAL_LOGIN() <> 'sa'
END

We can then create a SQL Server Agent job that queries the tblconnection table to get the list of databases that have not been used in the last 15 days. The job can be scheduled to run at regular intervals and email the list of unused databases to the desired recipients.

Conclusion

In this article, we have discussed two approaches to identify unused SQL databases in SQL Server. By counting user connections or using a logon trigger, we can regularly monitor and remove unused databases to optimize disk space and improve performance.