Help with DEL command -- getting the subdirectories

dbaSQL
dbaSQL used Ask the Experts™
on
I use Ola Hallengren's maintenance for all of my customer's, https://ola.hallengren.com/sql-server-backup.html.
Have used it for years, never any real problems -- until now.  For some reason the @CleanupMode and @CleanupTime are not working. The backup step is configured to retain bak files for only 24hrs, and to cleanup the old bak before generating the new.  For some reason, it is not working, and the job keeps failing due to insufficient disk space.

My temporary workaround is below.  I am deleting the largest bak file from disk as a first step in the job, until I get Ola's logic corrected.  Well, it works great as-is, but I've found I need to run it for all subdirectories.

The way it works now is to del the bak file in this path explicitly:
SET @path = 'dir \\backups\servername\dbname\FULL\'

I need it to delete the bak files from all subdirectories within \\backups\servername\

Any help?



SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#files') IS NOT NULL
DROP TABLE #files;
CREATE TABLE #files (Line VARCHAR(512)) 
DECLARE @path VARCHAR(49) 
DECLARE @cmd1 VARCHAR(155)
DECLARE @cmd2 VARCHAR(155)
DECLARE @filename VARCHAR(255)
SET @path = 'dir \\backups\servername\dbname\FULL\'
SET @cmd1 =  @Path+' /A-D  /B' 
--PRINT @cmd1

INSERT #files
EXEC master..xp_cmdshell @cmd1
DELETE #files WHERE Line IS NULL
--SELECT * FROM #files

SET @filename = (SELECT Line FROM #files)
SET @cmd2 = 'del \\backups\servername\dbname\FULL\' + @FileName
--PRINT @cmd2

IF EXISTS(SELECT 1 FROM #files WHERE Line = @filename)
BEGIN
--	SELECT @cmd2
	EXEC master..xp_cmdshell @cmd2
END

SET NOCOUNT OFF;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
HainKurtSr. System Analyst

Commented:
del *.bak /s
Sr. System Analyst
Commented:
cd \\backups\servername\dbname\FULL\
del *.bak /s

or

del \\backups\servername\dbname\FULL\*.bak /s

not sure about the second...

Author

Commented:
Thank you, Huseyin.  I need to test it first.  Back soon.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
You will want to add /Q to the DEL command as well when batch deleting multiple files...


»bp
HainKurtSr. System Analyst

Commented:
/Q            Quiet mode, do not ask if ok to delete on global wildcard
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
I wouldn't do the CD approach, better with the single DEL to the full path.  The risk with the CD approach in this context is if for some reason it fails, say the path was invalid, etc.  The the process will continue on and delete all *>BAK files from whatever the current directory happens to be when that command was launched.  Not desirable...

And typically good practice to throw double quotes around the path specified to the DEL command, just in case at some point it contained spaces.


»bp

Author

Commented:
I didn't need the Q.  I ran it like this within an Agent job, and it got all .txt files within \servername.  There are 11 subdirectories and I placed one txt file into every one of them, and even put two txt files into three to test if it would get them all.  It did.  All files removed were output in my log file.  Couldn't ask for anything more, Huseyin.  Pending the correction to the larger problem, this is exactly what I needed.  Thank you very much.


SET NOCOUNT ON;

DECLARE @cmd VARCHAR(155)
SET @cmd = 'del \\backups\servername\*.txt /s'

EXEC master..xp_cmdshell @cmd

SET NOCOUNT OFF;

Open in new window

Author

Commented:
Exactly what I needed.  Thank you very much.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial