How to Find Unused Databases in SQL Server

Problem

You would like to find out if you have any SQL Server databases that are no longer in use and just taking up space. It happens. Databases get created, sometimes put in production, a project gets cancelled and the database is abandoned.

Solution

We’ll take a look a look at one simple method of looking for SQL Server databases that have not had any connections over a period of time.

Why do you need to find unused databases?

It’s really a matter of housekeeping. There is really no reason to perform maintenance on and consume disk space with a database that is unused.

Methodology to detect database connections

The method described here queries sys.databases and sysprocesses to look for database connections and write the output to a table.

Create table to hold connection information

To get started, I’ll create a database called dba and a table called ConnectionCounts to hold the connection information we’ll gather.

-- create database
USE [master];
GO
CREATE DATABASE [dba];
GO
 
-- create table
USE [dba];
GO
CREATE TABLE [dbo].[ConnectionCounts](
   [ServerName] [nvarchar](130) NOT NULL,
   [DatabaseName] [nvarchar](130) NOT NULL,
   [NumberOfConnections] [int] NOT NULL,
   [TimeStamp] [datetime] NOT NULL
);
GO

Populate Table

Select from sys.databases and sysprocesses and insert the results into the ConnectionCounts table.

USE [dba];
GO
 
-- populate table
INSERT INTO [dbo].[ConnectionCounts]
SELECT @@ServerName AS [ServerName]
             ,NAME AS DatabaseName 
             ,COUNT(STATUS) AS [NumberOfConnections]
             ,GETDATE() AS [TimeStamp]
FROM sys.databases sd
LEFT JOIN master.dbo.sysprocesses sp ON sd.database_id = sp.dbid
WHERE database_id NOT BETWEEN 1 AND 4
GROUP BY NAME;
GO

Querying ConnectionCounts shows our initial data collection.

-- query ConnectionCounts
SELECT [ServerName], [DatabaseName], [NumberOfConnections], [TimeStamp]
FROM [dbo].[ConnectionCounts];
GO

The results are interpreted as we currently have connections in our database, dba; SSRS databases, ReportServer and ReportServerTempDB; and SSIS database, SSISDB. The user databases UserDatabase1, UserDatabase2, and UserDatabase3 currently have no connections.

To make what we have so far useful to us we’ll need to automate the table population.

Scheduling Job to Gather Connection Information and Populate Table with It

1. Right click on SQL Server Agent
2. New
3. Job…

1. Name Job
2. Change Job owner
3. Enter Description
4. Steps

1. New

1. Name Step
2. Change database context

3. Copy and paste the INSERT statement plus a DELETE statement to limit the records kept to a period of time – I chose 1 year, but it’s up to you.

USE [dba];
GO
 
-- populate table
INSERT INTO [dbo].[DbConnectionCounts]
SELECT @@ServerName AS [ServerName]
      ,NAME AS [DatabaseName] 
      ,COUNT(STATUS) AS [NumberOfConnections]
      ,GETDATE() AS [TimeStamp]
FROM sys.databases sd
LEFT JOIN master.dbo.sysprocesses sp ON sd.database_id = sp.dbid
WHERE database_id NOT BETWEEN 1 AND 4 -- ignore system databases
                  AND state_desc LIKE 'ONLINE' -- ignore offline databases
GROUP BY NAME;
GO
 
-- delete records earlier than 1 year ago
DELETE [dbo].[ConnectionCounts]
WHERE [TimeStamp] < DATEADD(year,-1,GETDATE()); 
GO

4. OK

Create schedule.

1. Schedules
2. New

1. Name Job Schedule
2. Daily
3. Occurs every:
4. Set frequency (10 minutes is OK but if you want to populate the table more frequently choose a lower frequency)
5. OK

1. OK

Report on Connection Data

-- number of records in ConnectionCounts
SELECT COUNT(*) FROM [dba].[dbo].[ConnectionCounts];
GO
 
-- oldest record in ConnectionCounts
SELECT MIN([TimeStamp])
FROM [dba].[dbo].[ConnectionCounts];
GO
 
-- identify likely unused databases
SELECT [ServerName]
      ,[DatabaseName]
      ,MAX([NumberOfConnections]) AS [NumberOfConnections] 
FROM [dba].[dbo].[ConnectionCounts]
GROUP BY [ServerName],[DatabaseName]
HAVING MAX([NumberOfConnections]) = 0
ORDER BY [ServerName],[DatabaseName];
GO

Optionally email unused database report periodically

There are a number of ways to automate reporting on the data we’ve collected. I prefer to keep thing simple and together so I decided to periodically email myself a simple report with a PowerShell script called by the SQL Agent.

1. Right click on SQL Server Agent
2. New
3. Job…

1. Name job
2. Change owner
3. Enter a description
4. Steps

1. New

1. Name Step
2. Choose PowerShell from the dropdown
3. Paste the following into the Command box and configure $ServerInstance and $PSEmailServer variables

$ServerInstance = "WIN-MBEN3T4BDIM" 
$PSEmailServer = "mymailserver.smtp.com" 
$From = "$ServerInstance@mydomain.com" 
$subject = "$ServerInstance Unused Database Report" 
$To="name@domain.com" 
$sql="SELECT [ServerName],[DatabaseName] ,CONCAT (MAX([NumberOfConnections]) , + ' connections since ' + CONVERT(VARCHAR ,MIN([TimeStamp]),1)) AS [Connections] 
FROM [dba].[dbo].[ConnectionCounts] 
GROUP BY [ServerName],[DatabaseName] 
HAVING MAX([NumberOfConnections]) = 0 
ORDER BY [ServerName],[DatabaseName]; 
" 
$Body = Invoke-SqlCmd -ServerInstance $ServerInstance -Query $sql | Format-List | Out-String 
If (! $Body) {$Body = "There are no unused databases detected on $ServerInstance."} # populates $Body with string so Send-MailMessage will not choke 
Send-MailMessage -From $From -To $To -Subject $Subject -Body $Body 

4. OK

1. Schedules
2. New

1. Name Schedule
2. Choose frequency
3. Choose time to run
4. OK

1. OK

1. Right click on Job
2. Start Job at Step…

Make sure you have a Status of Success

Now, you’ll get a scheduled email from your server@domain.com with the name of any databases that have not had any connections since you have been gathering data.

What to do if/when you discover likely unused databases

I follow this general pattern if I have a database that hasn’t had any connections in it for a number of months:

• Restore latest full backup of database(s) in question to another test database to validate backup
• Copy backup file to a location where any automated backup file deletion is not taking place, i.e. D:\DONOTDELETE
• Rename database to db_name_renamed_mmddyy
• Take database offline

1. Right click on database
2. Rename

1. Append ‘_renamed_mmddyy’ to database name

1. Right click on database again
2. Tasks
3. Take Offline

Or simply run this T-SQL:

ALTER DATABASE [UserDatabase3] MODIFY NAME = [UserDatabase3_renamed_0427421]; -- old name and new name
GO
ALTER DATABASE [UserDatabase3_renamed_0427421] SET  OFFLINE; -- new name 
GO

In the event access to the database is needed, simply reverse the process.

1. Right click on database
2. Tasks
3. Bring Online

1. Right click on database again
2. Rename

1. Remove _renamed_mmddyy

Or simply run this T-SQL:

ALTER DATABASE [UserDatabase3_renamed_0427421] SET  ONLINE; -- renamed name 
GO
ALTER DATABASE [UserDatabase3_renamed_0427421] MODIFY NAME = [UserDatabase3]; -- renamed name and original name
GO

If the database has been offline and no one has needed access to it for a period of time you’re comfortable with, go ahead and drop it.

1. Right click on database
2. Delete

1. OK

Or simply run this T-SQL

DROP DATABASE [UserDatabase3_042721]; -- set database name
GO

Next Steps

We now have a simple yet effective way to look for unused databases. Here are some links with more information:

• Identify SQL Server databases that are no longer in use
• How to rename a SQL Server database
• Script to Drop and Delete Offline SQL Server Databases and Data Files