By: Brady Upton | Comments (26) | Related: > Auditing and Compliance
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
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
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
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.
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
- 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:
About the author
Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
