Maintenance Plan Wizard generates invalid script?

Question

I've used the Maintenance Plan Wizard to create a plan. When I execute the plan, the "Maintenance Cleaup Task" fails with this error message:

Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2016..." failed with the following error: "Error executing extended stored procedure: Invalid Parameter". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Generated T-SQL is this (according to Log File Viewer):

EXECUTE master.dbo.xp_delete_file 0,N'''',N'''',N''2016-01-25T13:50:30''

GO

Please note all single quotes are doubled.

Is there a bug in log viewer or wizard is actually generating invalid T-SQL code?

---

Just realised that I get "Error executing extended stored procedure: Invalid Parameter" if I remove dupe quotes and run the query manually:

EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2016-01-25T13:50:30'

GO

Answer 1

The master.dbo.xp_delete_file procedure appears to be undocumented and it also seems to be a binary procedure (sp_helptext 'master.dbo.xp_delete_file' prints xpstar.dll) so we can't figure out arguments from source code. Unofficial sources suggest these are its arguments:

1. File Type = 0 for backup files or 1 for report files.
2. Folder Path = The folder to delete files. The path must end with a backslash "\".
3. File Extension = This could be 'BAK' or 'TRN' or whatever you normally use.
4. Date = The cutoff date for what files need to be deleted.
5. Subfolder = 0 to ignore subfolders, 1 to delete files in subfolders.

In other words, command is missing the actual path to remove. If we go to "Management/ Mainteinance Plans", right click on our plan and select "Modify" we can edit the individual tasks. In this case, the "Maintenance Cleanup Task" was missing the path (the wizard does not seem to validate whether you enter one). It should look like this: