5

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
8

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

  • I would request you to have a look at scripts provided by Ola Hallengren for DB Backup, Maintenance job etc; you will find lot many options and also you can customize it as per your needs. In their base code itself, you would be able to achieve most(if not all) your needs. – Learning_DBAdmin Jan 30 at 9:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.