Nisarg Upadhyay
Configure Job Schedule

T-SQL scripts to identify unused database files

October 30, 2020 by

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:

  1. Define temp tables to save the list of the drives and database files
  2. Use xp_fixeddrives to save the drive letter and free space in the #tbldrive table
  3. Use the dir command to get the list of all database files and store them in the #tblFiles table
  4. 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:

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.

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.

Command Output:

Get DIR command

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.

The entire code block is as the following:

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

List of database files

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:

The output is the following:

Unused database files

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:

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:

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.

New 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.

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.

New job schedule

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

Configure Job Schedule

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.

Test the Job

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

List of unused database files

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.

Nisarg Upadhyay
168 Views