locked
Cleanup task /xp_delete_file doesn't delete maintenance reports in SQL Server 2012 (German) RRS feed

  • Question

  • The cleanup Task in Maintenance Plans doesn't delete silently reports of maintenance report.

    When created normally, the 2<sup>nd</sup> parameter of xp_delete_file points to the \LOG folder and when executing the plan/task, it runs successfully, but w/o deleting any txt files qualifying for deletion.

    You only get a somewhat useful error message, when you call xp_delete_file with a real report file of a Maintenance Plan (and a qualifying deletion date!):

    Error 22049:

    Error executing xp_delete_file extended stored procedure: Specified file is not a Maintenance Plan report file.

    Can anybody confirm/deny this behavior in an English installation or another language?

    Notes:

    - I had only a chance to test this on various German SQL Server installations. If this issue was introduced by localization it may work in an US English installation. Feedback welcome!

    This scenario also fails in German SP3 for SQL 2012, but it works fine in a German SQL 2014

    Steps to reproduce:

    1. Create a Maintenance Plan with a little dummy task (runtime < 1 min.) on a small DB. E.g. Running "DBCC CheckDB" w/o index on MSDB. Schedule this plan to run every minute
    2. Note that the report files will start to fill your \LOG folder after some minutes
    3. Create a 2<sup>nd</sup> Maintenance Plan with a Cleanup Task, that deletes Maintenance Plan report files from your \LOG folder, which are older than 1 hour and schedule it to run every 5 minutes
    4. Wait some hours and you'll see that no  Maintenance Plan report file gets deleted!
    5. To get error 22049, you need to change the Maintenance Plan to delete only a specific report file which is older than 1 hour
    Friday, January 15, 2016 10:02 AM

Answers

  • Hi Johann,

    I test the scenario as yours in SQL Server 2012 SP3 which is a English installation, everything works as expected. The cleanup Task in Maintenance Plans deletes maintenance report files successfully. Please make sure that you specify the correct path(in my scenario, it is C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log) for maintenance report files and specify txt as file extension as follow. Also ensure that SQL Server Service account has full control permission on the folder & maintenance report files.


    However, if the issues still persists in the German SQL Server installations, I recommend you submit a feedback to the Microsoft Connect at this link https://connect.microsoft.com/SQLServer/Feedback. Your feedback enables Microsoft to offer the best software and deliver superior services.


    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support



    Monday, January 18, 2016 8:32 AM

All replies

  • https://ola.hallengren.com   very useful...

    Please click Mark As Answer if my post helped.

    Friday, January 15, 2016 10:46 AM
  • Hi Johann,

    I test the scenario as yours in SQL Server 2012 SP3 which is a English installation, everything works as expected. The cleanup Task in Maintenance Plans deletes maintenance report files successfully. Please make sure that you specify the correct path(in my scenario, it is C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log) for maintenance report files and specify txt as file extension as follow. Also ensure that SQL Server Service account has full control permission on the folder & maintenance report files.


    However, if the issues still persists in the German SQL Server installations, I recommend you submit a feedback to the Microsoft Connect at this link https://connect.microsoft.com/SQLServer/Feedback. Your feedback enables Microsoft to offer the best software and deliver superior services.


    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support



    Monday, January 18, 2016 8:32 AM
  • Hi Lydia,

    many thanks for your answer. I'd like to follow your Suggestion and file a formal Feedback on connect.Microsoft.com/SQLServer, but I don't get the "Submit Feedback" button on the left pane.

    Do you know, who can I contact about the missing "Feedback" button?

    Many thanks in advance, Johann

    Wednesday, August 24, 2016 10:48 AM
  • As Dinesh mentioned in other post Olla Hallengren's scripts are very good and widely used all over the community. Why don't you think over to change classic SQL Server's Maintenace Plans in favour or some solution totally based on T-SQL like Olla's one?

    Wednesday, August 24, 2016 10:54 AM