Identify SQL Server databases that are no longer in use
By: Brady Upton | Updated: 2014-02-25 | Comments (22) | Related: More > Auditing and Compliance
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?
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
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
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
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.
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
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:
The result set should look something like this:
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.
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.
- I encourage any one that is looking to remove a SQL Server from production to use at least one of the suggested methods in this tip to make sure nothing is connecting to the SQL Server.
- MSSQLTips.com offers some great tips that relate to this one:
Last Updated: 2014-02-25
About the author
View all my tips