By: Nirali Shastri | Updated: 2023-12-26 | Comments | Related: More > Database Administration
Problem
In this article, we will learn how to identify unused SQL Server databases. In my organization, after a production release deployment, we must manually refresh the database on lower environments (non-production) so the application development and QA team can perform smoke tests and regression tests. There are many automation tools available, but we use old-school technology, so we had to use legacy methods.
As per the process, after the database refresh is completed, we must keep an old database for 15 days. Sometimes we face instances when we did not delete the old database and we start running low on storage and we have to investigate and manually delete the database.
To improve the process, we decided to create an SQL Job that generates a list of databases that are not being used by any user. I created two SQL Jobs for that which perform the following tasks.
- The first SQL Job gets the list of the databases that are used by any user process. To do that, I created a stored procedure named sp_get_user_processes. The procedure uses sys.dm_exec_sessions DMV to get the list of users that are connected to the database. I execute the stored procedure every 1 hour, but this could be changed as needed. The stored procedure inserts the data into a table named tbl_user_processes.
- The second job will run every week and send the list of databases that are not in a tbl_user_processes (the databases not be used).
Solution
Now, let us review how to create the stored procedure to save a list of user connections. First, we must create a table named tbl_user_processes. The table holds the list of user processes that are connected to the SQL Server instance at a specific point in time.
Here is the code to create the table.
CREATE TABLE tbl_user_processes ( session_id int, login_time datetime, host_name varchar(150), program_name varchar(1500), client_interface_name varchar(150), original_login_name varchar(5000), database_name varchar(500) )
Once the table is created, let us create the stored procedure. The stored procedure gets the list of processes that are connected to the database, but we must make sure that it includes only user processes. To do that, I have added a filter that excludes the process run by the SQL Server agent, this can be fined tuned as needed.
The query to populate the user connections is the following:
SELECT session_id,
login_time,
convert(varchar, host_name) [Host Name],
convert(varchar, program_name) [Program Name],
client_interface_name,
original_login_name,
b.NAME
FROM sys.dm_exec_sessions a
LEFT JOIN sys.databases b ON a.database_id = b.database_id
WHERE is_user_process = 1
AND program_name NOT LIKE 'SQLAgent%'
AND nt_domain <> 'NT SERVICE'
Here is the sample output of the above query
We are inserting the above data into the tbl_user_processes table. To do that, we use the following query:
INSERT INTO tbl_user_processes
SELECT session_id,
login_time,
convert(varchar, host_name) [Host Name],
convert(varchar, program_name) [Program Name],
client_interface_name,
original_login_name,
b.NAME
FROM sys.dm_exec_sessions a
LEFT JOIN sys.databases b ON a.database_id = b.database_id
WHERE is_user_process = 1
AND program_name NOT LIKE 'SQLAgent%'
AND nt_domain <> 'NT SERVICE'
I have wrapped this code in a stored procedure named sp_get_user_processes. The final code of the stored procedure is the following:
CREATE PROCEDURE sp_get_user_processes
as
BEGIN
INSERT INTO tbl_user_processes
SELECT session_id,
login_time,
convert(varchar, host_name) [Host Name],
convert(varchar, program_name) [Program Name] ,
client_interface_name,
original_login_name,
b.NAME [Database Name]
FROM sys.dm_exec_sessions a
LEFT JOIN sys.databases b ON a.database_id = b.database_id
WHERE is_user_process = 1
AND program_name NOT LIKE 'SQLAgent%'
AND nt_domain <> 'NT SERVICE'
END
Now, let us create an SQL Job to add details of user connection.
SQL Job to store user database connection details in a table
As I mentioned at the beginning of the article, the SQL job will execute the sp_get_user_processes procedure every 1 hour. So, configure the SQL Job accordingly. You can read Create a Job – SQL Server article to understand how to create an SQL Job.
The SQL Job step looks like following screenshot.
The SQL Job schedule looks like following screenshot.
The next step is to create an SQL Job which executes every Sunday and email the list of databases that are not being used.
SQL Job to populate to send email
In this step, we will create an SQL Job that executes every Sunday and emails the list of databases that are not being used.
Here is the query:
DECLARE @FirstDayOfWeek DATE = DATEADD(DAY, 1 - DATEPART(WEEKDAY, GETDATE()), GETDATE());
DECLARE @LastDayOfWeek DATE = DATEADD(DAY, 7 - DATEPART(WEEKDAY, GETDATE()), GETDATE());
select name, create_date
from sys.databases
where database_id > 5 -- do not include system databases
and name not in
(select database_name from tbl_user_processes where login_time between @FirstDayOfWeek and @LastDayOfWeek)
As you can see, the above query selects the list of databases which does not exist in the tbl_user_processes table. Now, the SQL Job executes every Sunday so it should display the list of databases that are not used for the past week.
Also, we want to show the details in a tabular format in an email therefore we are embedding HTML tags in the SQL query. The final query to send email looks like the following image. I wrapped the code into a stored procedure named sp_email_unused_databases. This code uses database mail to send the email.
CREATE PROCEDURE sp_email_unused_databases
AS
BEGIN
DECLARE @xml_Text NVARCHAR(MAX)
DECLARE @Emailbody NVARCHAR(MAX)
DECLARE @FirstDayOfWeek DATE = DATEADD(DAY, 1 - DATEPART(WEEKDAY, GETDATE()), GETDATE());
DECLARE @LastDayOfWeek DATE = DATEADD(DAY, 7 - DATEPART(WEEKDAY, GETDATE()), GETDATE());
declare @EmailSubject varchar(max)='Unused databases on ' + @@Servername + ' Server'
SET @xml_Text = CAST(( SELECT name AS 'td','',create_date AS 'td','' From
sys.databases where database_id > 5 and name not in (select database_name from tbl_user_processes where login_time between @FirstDayOfWeek and @LastDayOfWeek)
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @Emailbody ='<html><body><H3>SQL Server Unused Database on ' + @@ServerName + '</H3>
<table border = 1>
<tr>
<th> Database Name </th> <th> Create Date </th> </tr>'
SET @Emailbody = @Emailbody + @xml_Text +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Database_Mail_Profile', -- replace with your SQL Database Mail Profile
@body = @Emailbody,
@body_format ='HTML',
@recipients = '[email protected]', -- replace with your email address
@subject = @EmailSubject ;
END
Now, we will create an SQL Job to email the list of unused databases. The screenshot of the SQL job step looks like the following image:
The screenshot of the SQL job schedule looks like the following image:
Test Run
After collecting some data about databases that are not being used, I manually executed the SQL job to send the email as follows:
use msdb go exec sp_start_job 'Email Unused Databases'
Once the job executes successfully, you will receive an email. The screenshot of the email is below:
Summary
In this article, we learned how to get a list of unused databases. There might be different ways to find unused databases, but I found this method easy to implement. In the next article, we will learn how to find unused tables and objects in SQL Server.
Next Steps
- Read Generate HTML Formatted Emails from SQL Server to learn how to create HTML formatted email using T-SQL script.
- Read Setup SQL Server Database Mail to use a Gmail, Hotmail, or Outlook account to learn more about SQL Server database mail and how to configure it.
- Read Working with SQL Server Agent in SQL Server Management Studio to learn how to create SQL Server agent jobs
About the author
Nirali Shastri is an experienced Core Database Administrator having more than 4 years of experience managing large SQL Databases.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2023-12-26
