Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Identify SQL Server databases that are no longer in use


By:   |   Read Comments (19)   |   Related Tips: More > Auditing and Compliance

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

I have just inherited a SQL Server database server with numerous databases on it and I need to know if all the databases are in use or if they are old and can be removed. What is the best way to find out if databases are still active on my SQL Server?

Solution

I have come across this problem a few different times in my career. I'll change jobs and they'll be numerous database servers that I inherit that I know nothing about. It's a process to learn what each server does, what applications use them, and what databases are no longer used and can be removed.

There is no "tried and true" method to knowing if a database is truly no longer used, but I have three different suggestions that may help with your research. These suggestions are all based around capturing user connections.

SQL Server User Connection Count

One suggestion to finding orphan databases is to get connection counts. In most cases, if a database has zero user connections over a long period of time, it may be time to look into removing this database. The following query will capture server name, database name, number of connections, and time the query was executed and it will also filter out system databases because they are needed:

SELECT @@ServerName AS server
 ,NAME AS dbname
 ,COUNT(STATUS) AS number_of_connections
 ,GETDATE() AS timestamp
FROM sys.databases sd
LEFT JOIN sysprocesses sp ON sd.database_id = sp.dbid
WHERE database_id NOT BETWEEN 1 AND 4
GROUP BY NAME
        


What is the best way to find out if databases are still active?

I'm using a server named BUADMIN for this example. As you can see I have 3 active connections to the database SQLCensus. This is a good indication that this database is in use. MonitorDB and SSISDB have 0 connections, so I may need to monitor them further. The easiest way to monitor these databases is to create a stored procedure using this query so I can schedule it. You can also put this query directly into a SQL Server Agent Job and set a schedule.

Before setting a schedule, you will need to create a table that will hold the results. To create a table using the following code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Connections](
 [server] [nvarchar](130) NOT NULL,
 [name] [nvarchar](130) NOT NULL,
 [number_of_connections] [int] NOT NULL,
 [timestamp] [datetime] NOT NULL
) ON [PRIMARY]
GO
        

Next, create a stored procedure that will INSERT the results into the table:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE usp_ConnectionsCount 
AS
BEGIN
 SET NOCOUNT ON;
INSERT INTO Connections 
  SELECT @@ServerName AS server
 ,NAME AS dbname
 ,COUNT(STATUS) AS number_of_connections
 ,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
END
        

Once the stored procedure is created you can create a SQL Server Agent Job and set it to run on a schedule. I'll set it to run every 10 minutes.

Let this run a few days, a few months or however long you think is appropriate and then go back and examine the results. Once you are happy with the timeframe chosen, use the following query to select the MAX number of connections per database:

SELECT NAME
 ,MAX(number_of_connections) AS MAX#
FROM Connections
GROUP BY NAME
        


you can create a SQL job and set it to run on a schedule

From here you will be able to determine if any databases have not had a user connection in the timeframe specified.

Detailed SQL Server Connection Information

The above suggestion is good if you just need connection counts. However, sometimes a count isn't good enough. Sometimes you need to know exactly what is connecting. This suggestion helps in that aspect.

It's basically setup the same way, create a stored procedure, insert data into a table, set a schedule and examine the results.

The following query gives you more information:

SELECT @@ServerName AS SERVER
 ,NAME
 ,login_time
 ,last_batch
 ,getdate() AS DATE
 ,STATUS
 ,hostname
 ,program_name
 ,nt_username
 ,loginame
FROM sys.databases d
LEFT JOIN sysprocesses sp ON d.database_id = sp.dbid
WHERE database_id NOT BETWEEN 0
  AND 4
 AND loginame IS NOT NULL
        


The following query gives you more information

This query is a detailed view of what is happening. As you can see, currently I have 10 connections to the SQLCensus database. You can view information such as login time, last batch, status hostname, program name, and login name. I've resized some of the columns for security purposes, but this is a good way to see where connections are coming from and what login is making the connection.

If you choose to use this suggestion, simply do what I explained above. Create a stored procedure, table, and job.

SQL Server Logon Trigger

The third suggestion is to create a simple logon trigger that records an entry every time a user logins.

Logon triggers fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established. Therefore, all messages originating inside the trigger that would typically reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL Server error log. Logon triggers do not fire if authentication fails. - Technet

There are many different variations of logon triggers. Some are used for auditing purposes and some are used to restrict access. The logon trigger used in this example is for logging purposes.

First, create a table that will store the logon information:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ServerLogonHistory](
 [SystemUser] [varchar](512) NULL,
 [HostName] [varchar](512) NULL,
 [DBUser] [varchar](512) NULL,
 [SPID] [int] NULL,
 [LogonTime] [datetime] NULL,
 [AppName] [varchar](512) NULL,
 [DatabaseName] [varchar](512) NULL
) ON [PRIMARY]
GO
        

Next, create the logon trigger:

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [Connections]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON 
AS
BEGIN
INSERT INTO ServerLogonHistory
SELECT ORIGINAL_LOGIN(), HOST_NAME(),USER, @@SPID, GETDATE(), APP_NAME(), DB_NAME()
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [Connections] ON ALL SERVER
GO
        

This trigger will fire every time someone logs into SQL Server capturing login, computer name, user, SPID, date/time, application, and database name. It will INSERT this information into our newly created table.

Once created, you can verify this trigger in SSMS under Server Objects > Triggers:

This trigger will fire every time someone logs in capturing Login

The result set should look something like this:

Once created you can verify this trigger in SSMS under Server Objects, Triggers

Keep in mind, this table can grow exponentially because it's creating a record for every time a login occurs. If there is a login that connects periodically (for example, the login that an application uses) and you don't want to capture the data you can create a WHERE clause in the trigger that ignores a login:

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [Connections]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON 
AS
BEGIN
INSERT INTO ServerLogonHistory
SELECT ORIGINAL_LOGIN(), HOST_NAME(),USER, @@SPID, GETDATE(), APP_NAME(), DB_NAME()
WHERE ORIGINAL_LOGIN() <> 'sa'
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [Connections] ON ALL SERVER
GO
        

There are numerous ways to create logon triggers. I suggest you research and experiment with logon triggers on a test server before implementing one in production.

Summary

All of these methods are just suggestions and will help you track user connections. Just because a database has 0 connections doesn't mean it is no longer used. Also, before dropping ANY database, make sure to create a backup and test the backup then store it in a safe place just in case someone needs data off of it.

If anyone has any other suggestions regarding this topic, feel free to add them to the comment section below.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Brady Upton Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, June 09, 2017 - 3:10:57 PM - Ranger Back To Top

 Brady , hi thanks for the scripts been very useful.

I am running the Detailed SQL Server Connection Information SP, and that is working great. Could anyone help me get the Hostname IP address in there as well ?

Thanks

Ranger

 


Wednesday, May 24, 2017 - 11:51:11 AM - Jon Ellison Back To Top

I run this script against the sys.dm_db_index_usage_stats to find out when, or if, a database was last accessed.  Hope this simplifies some things.

SELECT TOP 10000 s.[database_id]
,d.name
,d.create_date
,s.[index_id]
,s.[object_id]
,s.[user_seeks]
,s.[user_scans]
,s.[user_lookups]
,s.[user_updates]
,s.[last_user_seek]
,s.[last_user_scan]
,s.[last_user_lookup]
,s.[last_user_update]


  FROM [master].[sys].[dm_db_index_usage_stats] as s right outer join sys.databases d on s.database_id = d.database_id
  where d.database_id > 4
  order by d.name

 


Wednesday, October 26, 2016 - 9:56:13 AM - Tracey Sockey Back To Top

 Can you elaborate on the "last_batch" field, please? 

 


Tuesday, October 11, 2016 - 5:35:37 PM - NatZ Back To Top

 

 SQL Server Logon Trigger - you saying "  First, create a table that will store the logon information:" but there NO INFORMATION where you want to create this table in? what database?


Friday, September 18, 2015 - 6:24:27 AM - Alan Schofield Back To Top

I've not tested this on anything other than SQL2008 (r1) but it seems to give everything I need to check for redundant databases.

 

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW DatabaseUsageStats
AS
SELECT 

DB_NAME(s.database_id) AS DbName, 

s.database_id AS DbID,

db.create_date AS CreateDate,

last_user_seek = MAX(last_user_seek),

    last_user_scan = MAX(last_user_scan),

    last_user_lookup = MAX(last_user_lookup),

    last_user_update = MAX(last_user_update)

FROM sys.dm_db_index_usage_stats s

join sys.databases db on s.database_id=db.database_id

group by s.database_id

, db.create_date 

GO

and no additional triggers required....

Tuesday, January 20, 2015 - 10:37:58 AM - BK Back To Top

Could someone elaborate on "create a SQL Server Agent Job and have it set it to run on a schedule."

I plugged in the code for command, but for some reason it's not pulling the information into the table.

(New DBA - sorry :))


Friday, August 08, 2014 - 12:09:27 PM - Shannon Waldrop Back To Top

Thanks for the idea, David M.  I added a couple things to your query and posting here if helpful.

 -- run this script in text mode, copy results and run in grid mode

SELECT 'SELECT '''+object_name(object_id)+''' AS ObjectName, '''+c.name+''' AS ColumnName, MAX('

   +c.name+') AS MaxDate FROM '+object_name(object_id) +' with (nolock)

   UNION ALL'

FROM sys.columns c 

JOIN sys.types t ON c.system_type_id = t.system_type_id 

JOIN sys.sysobjects s ON c.object_id = s.id and s.xtype = 'u' 

WHERE t.system_type_id IN 

(SELECT system_type_id FROM sys.types

WHERE name LIKE '%date%' OR name LIKE '%time%')

ORDER BY object_name(object_id)

 

Thursday, July 31, 2014 - 5:16:57 PM - David Munzinger Back To Top

I found the following useful. Running this in the db in question will return a series of queries which in turn will return the last time a date field was updated in the db. In my case it was several years earlier.

select 'select max('+c.name+') from '+object_name(object_id)
 from sys.columns c join sys.types t on c.system_type_id = t.system_type_id
 join sysobjects s  on c.object_id = s.id and s.xtype = 'u'
  where t.system_type_id IN (61,58,189) order by object_name(object_id)


Thursday, July 03, 2014 - 11:22:15 AM - lindsay scott Back To Top

Really useful stuff! Thanks loads for making this available.


Saturday, March 29, 2014 - 1:54:48 PM - Rizwan Back To Top

 

Great article. Thank you for sharing


Monday, March 03, 2014 - 1:54:42 PM - TimothyAWiseman Back To Top

Excellent tip.  I think its worth reemphasizing the importance of keeping those backups and perhaps even checking with potential users before taking a database offline.  I have seen many cases of databases being maintained for historical and heard of needs to maintain them for regulatory purposes.  Those databases may go well over a year without ever being used and then suddenly have significant, and even occassionally urgent, needs to access them again.


Monday, March 03, 2014 - 1:22:04 AM - Sivaprasad S Back To Top

 

How about the below;


 

-- Query to find when the database last accessed on SQL Server 2005 / 2008 ---

select d.name, x1 =

(select X1= max(bb.xx)

from (

    select xx = max(last_user_seek)

        where max(last_user_seek) is not null

    union all

    select xx = max(last_user_scan)

        where max(last_user_scan) is not null

    union all

    select xx = max(last_user_lookup)

        where max(last_user_lookup) is not null

    union all

        select xx = max(last_user_update)

        where max(last_user_update) is not null) bb)

FROM master.dbo.sysdatabases d

left outer join

sys.dm_db_index_usage_stats s

on d.dbid= s.database_id

group by d.name

 


Wednesday, February 26, 2014 - 10:56:45 AM - Brady Back To Top

Thanks for the feedback Aaron. I look forward to reading your tip regarding this subject. It's always been difficult to try and determine if a database is in use or if it's old news especially when moving to a new company.


Wednesday, February 26, 2014 - 10:16:43 AM - Aaron Bertrand Back To Top

Of course there are some issues - you can easily miss connections that are in and out before your job runs again.

Logons don't always connect directly to the database they're going to later query.

And sysprocesses can only indicate one database. What if I'm running queries across 12 different databases?

Not trying to be critical, just pointing out that there are a lot of ways to check for "activity" but none of them are complete on their own. I was actually working on a tip that approached this from a different angle; could be a good complement.


Wednesday, February 26, 2014 - 10:08:37 AM - SneakyPete Back To Top

Ah, good to know about the depreication threat and the join/aggregation difficulty.  I had not yet tried to do much with them other than view the data they generated.

 

Thanks for the education!  I am sure there will be more questions to come on other posts. :)


Wednesday, February 26, 2014 - 9:28:41 AM - Brady Back To Top

Rafael Z - You still use SQL Server 2000? I kid, I kid. SQL Server 2000 does not have sys.databases but it does have sysdatabases and you can probably get some information from this. For example, this query will work in SQL Server 2000:

SELECT @@ServerName AS server
 ,NAME AS dbname
 ,COUNT(sp.status) AS number_of_connections
 ,GETDATE() AS timestamp
FROM sysdatabases sd
LEFT JOIN sysprocesses sp ON sd.dbid = sp.dbid
WHERE sd.dbid NOT BETWEEN 1 AND 4
GROUP BY NAME

SneakyPete - sp_who and sp_who2 have been in SQL since the beginning of time, at least as long as I've been working in it. Every new release I keep hearing these procs are going to be deprecated so I try not to use them, however, they will work fine as another alternative except you cannot JOIN to other tables and it's alot more difficult to aggregrate the data because it's a stored procedure.

 


Wednesday, February 26, 2014 - 9:05:37 AM - Chhavi_MCITP Back To Top

Thanks a bunch for your post. You are very true that we come across this situation many time but we generally ignore them.

What a thought to start monitoring such databases and if found no connection for 3 to 6 months then I'll put that database in offline status before dropping it and then again wait for 3-6 months.

Anyway, thanks again ..

 


Wednesday, February 26, 2014 - 7:53:36 AM - SneakyPete Back To Top

In SQL Server 2005 and SQL Server 2008/r2 there are a stored procedures called sp_who and sp_who2 which seem to give everything you would want in determining if the DB is no longer in use, not to mention some good information for general support/performance etc. operations.  As a fairly new SQL DBA are there advantages of these queries over thos procedures or visa versa or does it simply come down to there being multiple ways to get the information?


Tuesday, February 25, 2014 - 3:18:51 PM - Rafael Z Back To Top

Hi Brady, thanks for the article. Your script to get the count of connections works great in servers running SQL Server 2005 version or greater but there is an Invalid object name 'sys.databases' when running it in SQL Server 2000 servers. Just an observation.


Learn more about SQL Server tools