7

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
Paul White
  • 94,921
  • 30
  • 437
  • 687
Álvaro González
  • 1,089
  • 5
  • 17
  • 32

2 Answers2

13

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:

Maintenance Cleanup Task

Álvaro González
  • 1,089
  • 5
  • 17
  • 32
0

Old thread I know, but in case this helps anyone... I definitely typed my path out by hand because I just have a dedicated drive "f:\" in my case. When I read this and looked at my job, it was empty as well. This time I selected it via the ... button and it fixed the problem.

ebol2000
  • 203
  • 2
  • 4