October 18, 2018 at 4:06 pm
Unable to delete 7 days old files( .BAK and .TRN) using maintenance cleanup was task.
"Env :SQL server 2016 SP2"
Findings: The T-sql below runs fine. T-sql for .BAK deletes files when run manually BUT .trn T-sql just runs and no file is deleted.
The extension is also set and owner has read and write permission on folder where files are saved and 'sub folder' option is also checked.
TSQL-For Log files cleanup task( .TRN):
EXECUTE master.dbo.xp_delete_file 0,N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\ABK\Backup\',N'.TRN',N'2018-10-11T16:15:00',1
TSQL- For backup cleanup task( .BAK)
EXECUTE master.dbo.xp_delete_file 0,N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\ABK\Backup\',N'.TRN',N'2018-10-11T16:15:00',1
I know it is a problem which lot of people reported but I cannot find the clear solution yet. PLEASE HELP!! 'if it is permission issue then what can we do to resolve it" if not then is it a bug in Sql server 2016 SP2. Thank you!
October 18, 2018 at 4:48 pm
sizal0234 - Thursday, October 18, 2018 4:06 PMUnable to delete 7 days old files( .BAK and .TRN) using maintenance cleanup was task.
"Env :SQL server 2016 SP2"
Findings: The T-sql below runs fine. T-sql for .BAK deletes files when run manually BUT .trn T-sql just runs and no file is deleted.
The extension is also set and owner has read and write permission on folder where files are saved and 'sub folder' option is also checked.
TSQL-For Log files cleanup task( .TRN):
EXECUTE master.dbo.xp_delete_file 0,N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\ABK\Backup\',N'.TRN',N'2018-10-11T16:15:00',1
TSQL- For backup cleanup task( .BAK)
EXECUTE master.dbo.xp_delete_file 0,N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\ABK\Backup\',N'.TRN',N'2018-10-11T16:15:00',1I know it is a problem which lot of people reported but I cannot find the clear solution yet. PLEASE HELP!! 'if it is permission issue then what can we do to resolve it" if not then is it a bug in Sql server 2016 SP2. Thank you!
Remove the dot before the extension BAK or TRN.
If you didn't have a copy and paste issue above then you are not specifying BAK for backup files. You're using TRN.
Sue
October 18, 2018 at 4:53 pm
Thanks would like to confirm do you mean remove the "." in TSQL for BAK and TRN??
I am so sorry it is copy paste issue with .BAK ( I pasted .trn T- sql twice).
October 18, 2018 at 4:56 pm
sizal0234 - Thursday, October 18, 2018 4:53 PMThanks would like to confirm do you mean remove the "." in TSQL for BAK and TRN??
No in the maintenance plan cleanup task. It should be just
BAK
Sue
October 18, 2018 at 5:07 pm
Ok understood, before your reply, I ran the T-sql without "." in it ( for log files) and it cleared the log files which are older than 7 days and the maintenance plan has .TRN extension. Not sure now what is the solution? Please can you please be little detailed in your response. Many many thanks!!!
October 18, 2018 at 8:39 pm
sizal0234 - Thursday, October 18, 2018 5:07 PMOk understood, before your reply, I ran the T-sql without "." in it ( for log files) and it cleared the log files which are older than 7 days and the maintenance plan has .TRN extension. Not sure now what is the solution? Please can you please be little detailed in your response. Many many thanks!!!
Open up your maintenance plan to modify it. Select the maintenance plan cleanup task on the design page. Right click and select edit to edit the maintenance plan cleanup task.
Select backup files for file types to delete
When you select search folder and delete files based on an extension, you already entered the folder and you selected to first level subfolders based on your earlier post. In this same section, there is a text box called File Extension. That's where you put the file extension. You use BAK with no dot before it. Nothing other than BAK.
Sue
October 19, 2018 at 12:37 am
Thanks so much. I removed the "." and maintenance plan now only has 'BAK' for backups and 'TRN' for log files.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply