patternsqlModerate
Maintenance Plan Wizard generates invalid script?
Viewed 0 times
wizardgeneratesscriptplanmaintenanceinvalid
Problem
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):
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:
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''
GOPlease 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'
GOSolution
The
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:
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:- File Type = 0 for backup files or 1 for report files.
- Folder Path = The folder to delete files. The path must end with a backslash "\".
- File Extension = This could be 'BAK' or 'TRN' or whatever you normally use.
- Date = The cutoff date for what files need to be deleted.
- 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:
Context
StackExchange Database Administrators Q#130050, answer score: 13
Revisions (0)
No revisions yet.