How to Find Unused Databases in SQL Server


By:   |   Updated: 2021-06-01   |   Comments (14)   |   Related: More > Database Administration


Troubleshooting the most common SQL Server Problems

Free MSSQLTips Webinar: Troubleshooting the most common SQL Server Problems

In this educational webinar, we will cover some of the common issues you may face when working with SQL Server and some of the ways you can troubleshoot these issues.


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
Populate ConnectionCounts

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…
Schedule Populate Job - 1
  1. Name Job
  2. Change Job owner
  3. Enter Description
  4. Steps
Schedule Populate Job -  2
  1. New
Schedule Populate Job - 3
  1. Name Step
  2. Change database context
Schedule Populate Job -  4
  1. 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
  1. OK

Create schedule.

  1. Schedules
  2. New
Schedule Populate Job - 5
  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
Schedule Populate Job - 6
  1. OK
Schedule Populate Job - 7

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
Report Connection Data

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…
Schedule Report - 1
  1. Name job
  2. Change owner
  3. Enter a description
  4. Steps
Schedule Report - 2
  1. New
Schedule Report - 3
  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 
  1. OK
Schedule Report - 4
  1. Schedules
  2. New
Schedule Report - 5
  1. Name Schedule
  2. Choose frequency
  3. Choose time to run
  4. OK
Schedule Report - 6
  1. OK
Schedule Report - 7
  1. Right click on Job
  2. Start Job at Step…
Schedule Report - 8

Make sure you have a Status of Success

Schedule Report - 9

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
Rename Database 1
  1. Append ‘_renamed_mmddyy' to database name
Rename Database 2
  1. Right click on database again
  2. Tasks
  3. Take Offline
Database Offline 1

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
Database Back Online
  1. Right click on database again
  2. Rename
Rename Database Back to Original Name 1
  1. Remove _renamed_mmddyy
Rename Database Back to Original Name 2

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
Drop Database 1
  1. OK
Drop Database 2

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:






get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips


Article Last Updated: 2021-06-01

Comments For This Article




Tuesday, June 15, 2021 - 5:03:32 PM - Joe Gavin Back To Top (88861)
Steve, there is a little more to auditing than just turning it on. This is just one method to look for unused databases that's served me well.

Monday, June 14, 2021 - 5:07:51 PM - steve Back To Top (88854)
Can't turning on auditing yield essentially the same information?

Monday, June 14, 2021 - 5:40:13 AM - Salam Back To Top (88850)
Hi Joe, thanks for this useful post, everything works like a charm. Just maybe if possible updating the rporting with a nice html table and alternative colored lines would be great

Friday, June 11, 2021 - 3:09:19 PM - Francesco Mantovani Back To Top (88846)
@Joe, PowerShell is not working neither :(
By the way your article still helped me

Thursday, June 10, 2021 - 12:21:58 PM - Joe Gavin Back To Top (88840)
Francesco, that's exactly why I prefer Send-MailMessage. It's nice and simple. You may find this tip helpful: https://www.mssqltips.com/sqlservertip/5533/anatomy-of-a-sql-agent-email-notification/.

Thursday, June 10, 2021 - 5:26:28 AM - francesco Back To Top (88836)
@Joe Gavin, I've been trying to setup the SQL Server Database Mail account using T-SQL for days now and I'm done. It seems like it's not working any more.
Is this the reason why you have opted for PowerShell?

Tuesday, June 8, 2021 - 5:56:21 AM - Francesco Mantovani Back To Top (88818)
Error: change [dbo].[DbConnectionCounts] to [dbo].[ConnectionCounts] in the Job step

Tuesday, June 8, 2021 - 5:51:53 AM - Francesco Mantovani Back To Top (88817)
I receive the error: The job failed. Unable to determine if the owner (DOMAIN\MyUser) of job GatherDbConnections has server access (reason: Could not obtain information about Windows NT group/user 'DOMAIN\MyUser', error code 0x54b. [SQLSTATE 42000] (Error 15404)).

Thursday, June 3, 2021 - 10:15:42 AM - Joe Gavin Back To Top (88794)
Jeff, this method has not failed me finding unused database candidates on databases I'm still doing backups and index maintenance on. Thanks for the auto-close suggestion. Something I had not looked at.

Thursday, June 3, 2021 - 10:13:07 AM - Joe Gavin Back To Top (88793)
Andrey, I'd think a cross database query would be more of an ad-hoc query rather than the database being used. That said, I'm merely looking for unused database candidates. I like your other suggestions too.

Tuesday, June 1, 2021 - 1:08:55 PM - Jeff Moden Back To Top (88785)
Just counting connections might be seriously misleading because things like index maintenance and backups all take connections. With that in mind, you should also capture the code that the connections are running so you can figure things like that out.

A better way might be to ensure that index and database maintenance/backup routines are specifically designed to on operate on Online databases and to set suspected unused databases to "Auto_Close". Then, just monitor the log for starts of the database.

Tuesday, June 1, 2021 - 10:23:12 AM - Andrey Samykin Back To Top (88782)
There could be cross-database queries. For example: being connected to master db query a table from msdb database.

So, it's worth to combine such approach with buffer pool analysis at least - monitor cached data per database.

Also, in the default trace there are several types of events which could help to decide whether a db was used or not.

Tuesday, June 1, 2021 - 10:15:24 AM - Joe Gavin Back To Top (88780)
Glad you found this useful Francesco.

Tuesday, June 1, 2021 - 3:13:15 AM - Francesco Back To Top (88772)
WOW, I was discussing exactly this topic yesterday with my colleagues.
I told them that the best way to find unused database was to monitor the connections for a few weeks but they keep saying that maybe there was some logs that we could look into.
Thank you for confirming that the best way is this.


download














get free sql tips
agree to terms