I am always notified by my blog readers whenever they see something unusual and I try to help them so that I can learn something new. Recently, one of my blog reader told that his drive is getting filled with the regular backups taken via SQL Server Maintenance Plans.
My simple answer was – You should delete or move older backups and Maintenance Cleanup Task is available in the Toolbox. And I was shocked when he said that he already has that task, but it is not working as expected. It is supposed to delete files with extension “bak” which are 7 days old, but its not deleting any of those files. My next question was – is there any error message? The answer was – “When I run the job, it gives me a success message, but older .bak files are still present.”
I asked him to look into the log files generated by maintenance plan. They are generated under LOG folder (which contains SQL ERRORLOGs as well). Here is what was shared.
Microsoft(R) Server Maintenance Utility (Unicode) Version 12.0.2381
Report was generated on “local”.
Maintenance Plan: MaintenancePlan
Duration: 00:00:00
Status: Succeeded.
Details:
Maintenance Cleanup Task (local)
Maintenance Cleanup on Local server connection
Cleanup Database Backup files
Age: Older than 4 Weeks
Task start: 2014-10-28T10:14:59.
Task end: 2014-10-28T10:14:59.
Success
Command:EXECUTE master.dbo.xp_delete_file 0,N”E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup”,N”.bak”,N”2014-09-30T10:14:59”
GO
When I configured the plan on my machine and looked at the log, I got similar output. There is a little difference there. Notice the extension of the file given as .bak (notice a dot before bak). I have highlighted in the output.
Then I asked my friend to share the screenshot of the cleanup task and here is what he shared.
And that was the problem. I asked him to keep bak as extension (by removing dot before it) and run the below command.
EXECUTE MASTER.dbo.xp_delete_file 0,N'E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup',N'bak',N'2014-09-30T10:14:59'
GO
The command is same as what was shown in the log file. I have just replaced two single quotes with one single quote. As soon as that was run, all BAK files older than 2014-09-30T10:14:59 got deleted successfully.
Moral of the story: Doesn’t use dot before the extension of the file in the UI.
Reference: Pinal Dave (https://blog.sqlauthority.com)
23 Comments. Leave new
Wow I Found this very Impressive, Thanks Mr Pinal Dave for sharing this wonderful post. Helped me a lot.
An issue I encountered was that the SQL Agent did not have write permissions to the folders but SQL Database did. The backups were created fine, but the cleanup failed WITHOUT ERRORS.
Legend , Thanks this was annoying me :)
I am glad it helped you Declan
Doesn’t Work for me. The BAK files is on a NAS drive. Backup Works fine, but files is not deleted. (.bak) is “not” used… When press LOCATE: TITLE: Locate Folder – NAV-SIS-02
——————————
\storage-02data2sql2
Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.
If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box.
——————————
BUTTONS:
OK
——————————
Drive is also mapped as drive Y:…
Service account should have permission.
A user with Domain Admins permission is in use allready. But the problem is solved: Under File extension: we use “BAK”, and INCLUDE FIRST-LEVEL subfolders was also used, but the BAK files where in a sub-sub folder. By change FOLDER: to the first subfolder. Is Work Again…
great. thanks for the update.
For those that are trying everithyng and still the maintenance plan does not delete the old backup files:
I had this problem and i solved today, after few hours of searching and testing.
But my problem was not because we have a “dot” at extension, or because it was checked “INCLUDE FIRST-LEVEL …”. My problem was because i have the cleanup maintenance plan on a sql 2005 server, and this maintenance plan it tries to delete backups for multiple servers, from wich ones are compressed backups. And sql server 2005 does not have a clue about compression… So, compressed backup must be deleted trough maintenance plan from a sql server that are aware about compression…
Brilliant ! Your post solved my issue the d… dot !!
Thank you!! Was driving me nuts!
I am only having difficulty with the DB Backups deleting on SQL 2014 systems. I have made sure there is no ‘.’ before the BAK file extension and that the checkmark is placed in the 1st Level Subfolders. I’ve had no issues with backups not being deleted until I began utilizing SQL2014. Ideas?
I wonder if my issue is related to what another person wrote: ‘An issue I encountered was that the SQL Agent did not have write permissions to the folders but SQL Database did. The backups were created fine, but the cleanup failed WITHOUT ERRORS.’
I don’t know how to check for that.
I have the same issue now. Can you please let me know how you solved this issue
i faced .bak issue. after replacing with bak it worked.
Awesome thanks!!!!
Great. Thanks for the confirmation.
My bak files are deleting fine…However, my .txt log files are not being removed. I setup the maintenance plan the same as for backup files. The log files are in the same path, just a level below, such as s:\backup\logs any reason why the .txt files would not be removed? There is no ‘.’ before txt extension.
Thanks
Did you choose “maintenances plan text reports” checkbox?
Hello! i have the same probleme of TD
where can i found the “maintenance plan text reports” checkbox ?
have a look at the image in the blog. There are two check boxes on the top. “Backup files” and “maintenance plan text reports”
Hello Pinal, I hope this message can reach you, since it’s been published a while ago.
I cannot remove the text reports (txt files) in spite of doing everything right: I have checked the “maintenance plan text reports” checkbox; I do have . in the extension (that is, “txt” only). The system says “Query executed successfully” but no text file being removed. Could you shed some light on the matter?
Many thanks!
Yvonne
Excellent article! Thanks, solved my issue!
I did not use .bak in my maintenance plan but the backups are still not deleted. i’m using sql server 2014 version.
Thanks a lot Pinal, your articles are very easy to follow and helpful for non-DBA SQL admins like me. I had the same issue with SQL maintenance task not deleted any .BAK or .TRN files. I ran the T-SQL statement in SSMS as you mentioned below as voila it worked!
EXECUTE MASTER.dbo.xp_delete_file 0,N’E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup’,N’bak’,N’2014-09-30T10:14:59′
GO
I wonder if the Maintenance plan would now work.