How to Find Unused Databases in SQL Server
Problem
In my life I’ve lost flights, trains, my wallet, keys… and yes, sometimes is normal to forget databases here and there.
Especially in a data warehouse scenario where multiple IT consultants are allowed to copy/paste and create databases, you end up with hundreds of them and you cannot really tell what are in use and what are not.
As I have some spare time lately I took the occasion to modernize one of my old tools: SQL_Spring_Cleaning
Solution
The old SQL_Spring_Cleaning was targeting the size of the databases and the number of users. It wasn’t really providing insights about the connections to each database.
This is what the new version of SQL_Spring_Cleaning does:
— Creates an account (SQL_Spring_Cleaning_Account) and a profile (SQL_Spring_Cleaning_Profile) in the SQL Server Database Mail (keep in mind that Outlook and Gmail e-mails will not work)
— Creates the table .. in which we will store the connections
— Creates the Agent Job GatherDbConnections_Local that run every 10 minutes and saves all current connections of each database into ..
— Creates the Agent Job SQL_Spring_Cleaning_Local that query the table .. and sends the results to your inbox every day at 8:00am
To install of SQL_Spring_Cleaning just follow the instructions on the GitHub page.
The result looks like this:
And because we don’t like to leave the mess around you can decommission SQL_Spring_Cleaning in a second:
EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = ‘SQL_Spring_Cleaning_Profile’;
EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = ‘SQL_Spring_Cleaning_Account’;
EXEC sp_delete_job @job_name = N’SQL_Spring_Cleaning_Local’;
EXEC sp_delete_job @job_name = N’GatherDbConnections_Local’;
USE ;
GO
DROP TABLE .
Conclusion
It’s a shame that SQL Server doesn’t provide an easy out-of-the-box solution to track connections to your SQL Server. The frustration was so palpable that I had to write something tailored for me. Hopefully it will fit for you too.
Feedbacks are welcome.