Steps to create a maintenance plan in SQL Server to clear old SQL Backup files or Log files

Hi Everyone in this post I will show how to use Maintenance Plan in SQL Server to delete old SQL Server backups periodically.

Writing this post as we were facing the issue in our office where the SQL Backups were taken periodically and because of backups the size in the drive reduces, hence we had to delete the old backups

 

Database Maintenance plans can be created through a wizard in SQL Server Management Studio (SSMS)

They are handled mainly through the Business Intelligence Studio (BIDS),

Two Plans that are of much interest–

1.       History Cleanup Task

2.       Maintenance Cleanup Task

1) History Cleanup Task - By using the History Cleanup task, a package can delete historical data related to backup and restore activities, SQL Server Agent jobs, and database maintenance plans.

This task executes the sp_delete_backuphistory system stored procedure and passes the specified date to the procedure as an argument.

2) The Maintenance Cleanup Task – A package that can remove the backup files or maintenance plan reports on the specified server. The Maintenance Cleanup task includes an option to remove a specific file or remove a group of files in a folder. Optionally you can specify the extension of the files to delete.

This task executes the sp_delete_file system stored procedure

We will focus on Maintenance Cleanup task and below are the steps to create

Step 1) In Object Explorer, expand the server, and then expand Management.
Right-click Maintenance Plans and select New Maintenance Plan

Step 2) In the New Maintenance Plan dialog box, type a name for the plan.

 

Step 3) The Toolbox opens, and the DeleteFilesPlan [Design] opens with the default Subplan_1created.

Note - A Plan can have multiple Sub Plans. Edit the Sub Plan.

 If the Default sub plan does not open, you can open by Right-click and modify-

 Step 4) To build the sub plan, drag and drop task flow element “Maintenance Cleanup Task” from the Toolbox to the plan design surface to define the tasks that will be performed.

 Step 5) On the Maintenance Clean-up Task, Right Click and Click on Edit, the maintenance Clean-up Task Window will open

Step 6) Enter the Folder path where the files will be searched for and deleted.

Enter the File Extension as .bak – since we are trying to delete SQL Server backup files

Note – Using “Maintenance clean up task ” we can delete only the database and transaction log backup files, we cannot delete any other kind of files.

File age – Specifies that files older than the one specified will be deleted.

It does not indicate the frequency of execution of maintenance plan

Example1 – If we specify File age as 1 Hour and the maintance plan is executed at 3 PM then any files  that was created before 2 PM will be deleted.

Example2 - If we specify File age as 1 Day and the maintance plan is executed on 2 PM on 20th July then any files  that was created before 2 PM 19^th July will be deleted.

 Step 7) You can view the query generated by the configuration by clicking on View T-SQL

As you can see It uses an extended system stored procedure xp_delete_file. It reads the file header to check what type of file it is and will only delete certain types based on the extension

Parameter list of xp_delete_file Stored Procedure –

File Type = 0 for backup files or 1 for report files.

Folder Path = The folder to delete files.

File Extension = This could be 'BAK' or 'TRN' or whatever you normally use.

Date = The cutoff date for what files need to be deleted.

After viewing click on Ok and save the sub plan by pressing Ctrl+S.

 Step 8) To execute a created Maintenance Plan Right click on the plan and click on Execute.

It will delete the files as configured in the Maintenance Plan.

 Advantages of SQL Server Maintenance Plan –

·         Maintenance plans can be run manually or automatically at scheduled intervals by using SQL Jobs.

·         Each plan lets you create or edit task workflows.

·         Tasks in each plan can be grouped into subplans

·         We can execute the Plans in multi-server

·         Supports both Windows Authentication and SQL Server Authentication

Further Reading

https://docs.microsoft.com/en-us/sql/integration-services/control-flow/integration-services-tasks

https://www.mssqltips.com/sqlservertip/1094/sql-server-database-maintenance-plans-and-backup-file-management/

http://sql-bi-dev.blogspot.ae/2010/07/send-mail-task-in-ssis-2008.html

https://bradmcgehee.com/2010/01/13/how-to-use-sql-backup-inside-a-maintenance-plan/