T-SQL scripts to identify unused database files

In this article, I am going to show how we can automate the report of unused database files using T-SQL.

Introduction

Often organizations have a well-defined process to decommission the client database. I had helped one of the customers to establish the process of decommissioning the client database. Before decommissioning the client database, they want me to generate the backup of the customer database and detach the primary and secondary datafiles and transactional log files. I have created a SQL Server job to automate the entire process.

In this process, there is a glitch. After decommissioning the database, the data files and log files are left unattended. Due to that, the disk drives start getting full. To fix this issue, we decided to create another SQL Job to generate the list of the unused database files and log files and email them to the stack holders. They verify the list of files and provide the approval to delete the files.

I had created a stored procedure to identify the list of database files and log files that are not attached to any database and display the output in the HTML formatted table and email it using the database mail.

The script performs the following tasks:

Define temp tables to save the list of the drives and database files
Use xp_fixeddrives to save the drive letter and free space in the #tbldrive table
Use the dir command to get the list of all database files and store them in the #tblFiles table
Compare the list of the files to the output of sys.master_files to get the physical files that are not in the database

Create temp tables

First, let us create a temp table named #tbldrive to save the list of the drives and free space in the drive and #tblFiles to insert the list of the physical location of files that has *.mdf, *.ndf, and *.ldf extensions. Following is the T-SQL query to create the tables:

create table #tbldrive (ID INT IDENTITY(1,1), [DriveLetter] VARCHAR(1), [Free_Space] INT)

create table #tblFiles (ID INT IDENTITY(1,1), [FilePath] NVARCHAR(max))

Insert drive letter details in temp table

Run the following T-SQL query to insert the list of drives and free space in the #tbldrive table.

1	INSERT INTO #tbldrive ([DriveLetter], [Free_Space]) EXEC xp_fixeddrives;

Insert list of files in temp table

To insert the list of the physical location of in temp table, we must create a dynamic T-SQL query that uses the drive letters stored in #tbldrive and create a dir command. In the dir command, we are going to use /S /B flags. The /S /B returns the full path of the files with *.mdf and *.ldf extensions. The following code generates the dir command.

DECLARE @DriveLetter NVARCHAR(1);

DECLARE @DriveCommand NVARCHAR(4000);

DECLARE @i INT;

WHILE ISNULL(@i, 0) > 0

BEGIN

--get next available drive

SET @DriveLetter = (SELECT [DriveLetter] FROM #tbldrive WHERE ID = @i);

--create the command to get directory information

SET @DriveCommand = N'dir ' + @DriveLetter + ':\*.*df /S/B';

--get directory information for the current drive

select @DriveCommand

SET @i = (SELECT [ID] + 1 FROM #tbldrive WHERE ID = @i);

IF @i IS NULL

SET @i = 0;

END;

Command Output:

Now, as mentioned, we will insert the output of the command in #tblFiles. To do that, add the following T-SQL query block in the while loop.

1	INSERT INTO #tblFiles ([FilePath]) EXEC xp_cmdshell @DriveCommand;

The entire code block is as the following:

set nocount on;

DECLARE @DriveLetter NVARCHAR(1);

DECLARE @DriveCommand NVARCHAR(4000);

DECLARE @i INT;

SET @i = 1;

WHILE ISNULL(@i, 0) > 0

BEGIN

--get next available drive

SET @DriveLetter = (SELECT [DriveLetter] FROM #tbldrive WHERE ID = @i);

--create the command to get directory information

SET @DriveCommand = N'dir ' + @DriveLetter + ':\*.*df /S/B';

--get directory information for the current drive

INSERT INTO #tblFiles ([FilePath]) EXEC xp_cmdshell @DriveCommand;

SET @i = (SELECT [ID] + 1 FROM #tbldrive WHERE ID = @i);

IF @i IS NULL

SET @i = 0;

END;

select FilePath from #tblFiles

drop table #tbldrive

drop table #tblFiles

Output of the list of physical location of the mdf and ldf file are the following:

Compare the list with an output of sys.master_files

Now, we will compare the list of the physical locations in #tblfile with the list of the values of the physical_name column in the sys.master_files DMV. Following is the code:

1	select FilePath from #tblFiles where FilePath not in (select physical_name from sys.master_files) and FilePath not like 'C:\%' and (FilePath like '%mdf' OR FilePath like '%ndf' OR FilePath like '%ldf' )

The output is the following:

Now, to display the output in email, we will use HTML code. The HTML code the table will be stored in the @HTMLTable variable. The data type of the variable is nvarchar(max). Following is the code:

SET @UnusedDatabaseFiles =

'<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="50%" border="1"> <tr> <td width="27%" bgColor="#D3D3D3" height="15"> Database Files </td> </tr>   List of unused database files'

SELECT @UnusedDatabaseFiles = @UnusedDatabaseFiles

+ '<tr><td>'

+ CONVERT(VARCHAR, filepath)

+ '</td></tr>'

FROM #tblfiles

WHERE filepath NOT IN (SELECT physical_name

FROM sys.master_files)

AND filepath NOT LIKE 'C:\%'

AND ( filepath LIKE '%mdf'

OR filepath LIKE '%ndf'

OR filepath LIKE '%ldf' )

To send the email, we will use the SQL Server database mail. I have already created a database mail profile named OutlookMail. The code to email the list of unused database files is as following:

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'yourmailprofile',

@recipients='n******87@outlook.com',

@subject = 'List of unused database files',

@body = @UnusedDatabaseFiles,

@body_format = 'HTML' ;

Create a SQL Server Agent Job

Once the stored procedure is created, we will use the SQL Server Agent job to automate it. For that, Open SQL Server Management studio Expand SQL Server Instance Expand SQL Server Agent Right-click on Jobs Select New Job.

On the New Job dialog box, provide the desired name of the SQL job in Jab Name text box. Click on Steps and click on New to create a job step.

In the New Job Step dialog box, choose Transact-SQL from the Type drop-down box and enter the following T-SQL code in command textbox.

Use DBA

Exec sp_getunuseddatabases

Click OK to save the step and close the dialog box. We will schedule the execution of this job every week on Monday at 9:00 AM; therefore, configure the schedule accordingly. To do that, click on Schedules in the New Job dialog box.

On the New Schedule dialog box, enter the desired schedule name in Name textbox choose weekly from the occurs drop-down box click on Monday checkbox enter 09:00:00 in Occurs once at the textbox. See the following screenshot

Click OK to save the schedule and close the New Job Schedule dialog box. Click OK to save the SQL Job. Now let us test the SQL job. To do that, right-click on the SQL job and click ok Run job at step.

Once the job completes the execution, you will receive the email, as shown below.

Summary

In this article, I have shown a T-SQL Script that is used to generate a list of unused database data files and log files. Moreover, I have also explained how we can display the list of the files in an HTML formatted table and automate the report using SQL Server Agent Job.

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