In this article, I am going to show how we can identify unused SQL databases. In our organization, after release deployment, we receive the database refresh requests. To refresh the database, we were instructed to perform the following tasks:

1. Generate a full backup of the source database (UAT/Prod) and restore it on the Development server with a new name
2. Delete the old database (previous version) after 15 days of release deployment

Sometimes, the old version of the databases left unattended, and due to that, the disk was getting full very frequently. To handle this issue, we have defined a process to identify the unused databases from SQL Server and email the list to senior team members for approval. To identify the unused databases, we considered two approaches:

1. Get the number of the user connection by querying sys. sysprocesses DMV. In this approach, we must create a SQL Server Agent job that queries a DMV and save the query output in the table
2. Create a SQL Logon trigger to keep track of the connected users on each database. If any user or application does not access the database, we can drop it

We implemented the first approach to establish the process, but I will explain both approaches in this article.

Use the count of user connection

In this approach, we must create two SQL Server Agent jobs. Every ten minutes, one job will execute and insert the list of users connected to the SQL database in the table. The second job will execute every week and email the list of the databases not used by any application or user.

SQL Job to populate the user connection details

To populate the number of the user connection, we are going to use sys.databases and sys.sysprocesses DMVs. The output of the query will be stored in a table named tblconnectioncount. We will create a SQL Job that executes every 10 mins and inserts the query’s output in a table. The following query populates the Server Name, Database Name, Number of connection, and current date-time of the query execution (getdate()).

The following is the output of the query.

We will save the output in the tblconnectioncount table. The following script creates a table.

We will create the stored procedure named sp_getconnectioncount to insert the output in the tblconnectioncount table. The code of the stored procedure is the following:

Now, we can schedule the execution of the stored procedure using SQL Server Agent. I have explained how we can create a SQL Server agent job in my previous articles, so I will not include it in this article. The job step should be configured as shown below:

The Job schedule configured, as shown below.

Now, let the SQL Job run for a few days, and based on the output of the following query, you can determine the list of unused SQL databases.

SQL Job to email the unused SQL databases

Now, let us create another job to email the list of the unused databases. As we do not have sufficient data to monitor, we will populate the list of databases that have not been accessed for a few hours. The list of unused databases can be populated by querying the tblconnectioncount table. The following is the query.

To display, the unused databases in HTML table, I have created a stored procedure. The code is following:

Now, as shown above, create a SQL Job. The job step should be configured as shown below:

The Job schedule configured, as shown below:

To test the job, execute the following query:

If the job executes successfully, we will receive an email. Below is the screenshot:

Now, let us understand the second approach.

Use SQL Server Logon trigger

In the second approach, we are going to use the SQL Server logon trigger. This approach will create one SQL job to populate the unused database and email it to the desired recipients. You can read this article to understand more about Logon Triggers.

I have created a table named tblconnection to save the login information generated by the logon trigger. The following code creates the table.

Whenever a user establishes the connection to the SQL Server instance, the logon trigger inserts the following details in the tblconnection table.

1. Login Name
2. Hostname
3. Username
4. Session ID
5. Application name
6. Database name

I have added the logic to insert the trigger events in the tblconnection table in the Logon trigger code. To create the Logon trigger, execute the following code.

To view the trigger using SQL Server Management Studio (SSMS), expand database instance expand Server Objects Expand Triggers. You can see that the trigger has been created. See the following image:

We want to get the list of SQL databases that have not been used in the last 15 days and email the list to desired recipients. In the query, we have excluded the databases that have been accessed by SQL Server Agent User. The code of the stored procedure is the following:

To automate the process, create a SQL Server Agent job. The job step executes the above stored procedure, as shown in the below image.

Configure the schedule as shown below image:

Once the job is created, run the following query to email the list of unused databases.

The email looks like the following image:

Summary

In this article, I have explained two approaches that can be used to identify the unused SQL databases’ list by using the SQL Server logon trigger and by querying the sys.sysprocesses and sys.databases DMVs.

• Author
• Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

Latest posts by Nisarg Upadhyay (see all)

• Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
• Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
• Changing the location of FILESTREAM data files in SQL Database - October 14, 2022