Deleting text report files with Maintenance Cleanup Task in SQL Server Management Studio - Part 2

[aprao]

 

Overview

There was an alert in the RZ20 that the filesystem i.e. C: drive was nearly full.

Used space was 98% in the C: drive in the MS SQL server host.

As shown above, file size for MSDBData.mdf was approaching to 4.2 GB (normally it should not grow beyond 500MB) and the MSDBLog file size used is 6.782GB (usually, it should not go past 100MB).

MSDB is the schema database. This Database has two files Data and Log (Transaction log).

This indicates that 10.8Gb was used by MSDB database (it cannot go above 600MB).

The solution is to shrink the size of Data file and log file of MSDB database.  This is done using SSMS (SQL Server management Studio available in the Windows Server).

In the next post, the procedure for downsizing the  MSDB database will be covered.

Secondly,

As shown above,  7.78GB was used in LOG Folder. It contains Subplan related text report files as shown below.

The content of the report file is the collection of sql commands for your information.

Approximately total number of oldest report files generated prior to  31^st Dec 2022  is 46123 out of 56300 as shown below:

Total number of txt files created automatically by the SQL agent job prior to 1^st Jan 2023 was 46123 files out of 56,500 files.

Do you keep discarded clothing or old card boxes in your room?  NO, is the answer.

DO you want to build new rooms or increase the size of the current ones? NO, is the answer.

In a similar vein, it serves no purpose to retain old files created before January 1, 2023.

The LOG folder of the MS SQL Server on the C: disk needs to be cleaned out on a regular basis to get rid of old, unneeded txt files.

.It is expensive to add more disks and enlarge existing disk space.

The method to get rid of these out-of-date, pointless txt files will be described in this article.

There are two ways to get rid of or erase these unnecessary and out-of-date txt files that were generated in the Log folder.

• OS command to delete

 

• Using Maintenance Plan in SSMS (SQL Server Management Studio

Most customers do not permit BASIS administrators to use OS commands to remove outdated or undesirable files. It is not advisable to delete something at the OS level.

As a result, the Maintenance Plan in SSMS can be used to delete old, useless files.

Here is a description of the deletion process utilizing SSMS's Maintenance Plan.

This is recommended method.

Solution

The removal of outdated report text files produced by the use of maintenance plans is specifically done using the Maintenance Cleanup Task.

Based on the file extension, it enables us to delete full, differential, transaction log backup files, and report files from the specified location.

A specific, single file may also be deleted. However, each maintenance cleanup activity can only remove a single kind of file.

To delete certain particular file kinds, you can only use one type of file extension in the setup. As a result, it is not possible to remove, for example, full or differential backup files (with the.bak suffix) and transaction log files (with the.trn extension) in the same process.

In this article, we will demonstrate one  Maintenance Cleanup Task – the first one for removing the report txt files.

Procedure

Launch SSMS

Click Management.

Right click on the Maintenance Plans -àNew Maintenance Plans

Then, we choose the name for our plan. To remove these old files, we have created a new plan called "DeleteOldSubplanTextFiles".

Click OK

Select as shown above highlighted  in orange square color

Change recurring in schedule type to "one time" as shown below

Click OK

Click the "Toolbox" located in the top left corner of the screen as shown above.

we drag and drop the "Maintenance Cleanup Task" to the "Design Surface" as shown below

Double click on the Maintenance Cleanup Task . Popup screen appeared as shown below

We have chosen to remove all report files older than six months  in our example. After saving and executing the plan, we can see that only a few report files generated during last six months

In the image above, if we click on "View T-SQL", we can see that the xp_delete_file procedure is executed for this task as shown below:

Click close

or

Click Save Selected items. The Maintenance cleanup Task name "DeleteOldSubplanTestfiles" will be added to the left side of the screen under the Maintenance Plan section as shown below

Now, right-click on this task and click on "Execute":

The task is successfully executed:

 

When we open Log  folder again, we can see that the above-mentioned files have been deleted as expected as shown below

 

As shown above, free disk space available for C:drive after deleting text report files rose from 12GB  (in the previous post Filesystem Full) to 19.4GB

Conclusion:

Report text files can delete using Maintenance Plan Cleanup Task in SSMS frequently.  You an also schedule the job periodically to ensure that C: drive size should not be full.

Thanks for reading!

Follow for more such posts by clicking on FOLLOW => aprao

Please share your thoughts and comment on this blog in a comment.