Deleting Files with a SQL Job in SQL Server-2008

Suppose you want to delete some files from a directory. This could be one of the easiest task for anyone who is familiar with windows. But what if we want to delete the files in a regular interval, to do this we have some options like:

·         By a windows service that keeps on deleting files in a regular interval.

·         By a SQL Job (Taking advantage of SQL Server Agent Service) that deletes files in a certain interval.

Here I am going to discuss “Deleting files through SQL Job”. In SQL job, you need to put some SQL query in your SQL Job Steps.

In the SQL query to delete the files, you need to call a predefined stored procedure “xp_cmdshell”. But before calling “xp_cmdshell”, we also need to enable the Command Shell of your SQL Server if it is not enabled in your system.

Here is the full SQL query that is used for deleting the files.

[Note: 

To know “How to create SQL job”, you can go to my article in www.c-sharpcorner.com site at:

“Delete Files with a SQL Job in SQL Server-2008” ]

----------------------------------- Enabling the COMMAND SHELL ------------------------------

--Script to enable the XP_CMDSHELL
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
----------------------------------------------------------------------------------------------

----------------------------------- Deleting the files in ------------------------------------
-- Suppose we need to delete all .txt files under "D:\temp\Testing\"

xp_cmdshell 'DEL D:\temp\Testing\*.txt'

----------------------------------------------------------------------------------------------