none
Report Maintenance Plan out of order in SQL Server 2005

    Frage

  •  Hello,

    I've found in the forum an old discussion about the same problem (in 2008 ! ) , but anybody didn't give any solution.

    Now, I have a maintenance plan with 9 tasks. It runs in corrcet order, but the view of the log is out of order.

    the log steps are :3,1,8,2,9,7,4

    the version of sql server is : 9.00.5000.00 S Enterprise Edition (64-bit)

    Thanks in advance

    Mittwoch, 29. Februar 2012 13:18

Antworten

  • Hi,

    Thanks for you reply.

    I agree with you answer about the switch between Full and Simple recovery models, but the maintenance plan runs every night and no users works on the application.

    I think that the goal of the person who has made this plan was to limit the duration of the tasks in order to be sure that the plan was finished before the morning and the opening of the office.

    I can do benchmark between the two methods,also without switching full to simple,simple to Full, and without changing the recovery model for the fun and verify !!.

    About the dependecies, we have successful completion steps only. Do you think the problem come from this ?

    Best regards

    Regardless of whether or not users are accessing the system, switching to simple recovery model breaks the log chain.  If you have to restore to a prior backup because the latest available backup file is corrupted you cannot restore past a break in the log chain.

    It is a risk you don't need to take.  Switching does not improve performance in any way - since everything is logged in all recovery models.  The only advantage to using simple recovery is that you could get minimally logged transactions which is not a performance improvement - rather it would reduce how large the log file gets.  That could be accomplished by switching to bulk_logged instead - which won't break the log chain.

    Note: there are issues with switching to bulk_logged also.  The issue here is that you cannot restore to a point in time where minimally logged transactions have occurred.  But, that would be a whole lot better than breaking the log chain.

    As long as you have constraints defined for each task - you are fine.  The only issue you have then is that the report is not ordered - but the times appear to be correct.


    Jeff Williams

    • Als Antwort markiert Stephanie Lv Donnerstag, 15. März 2012 08:36
    Samstag, 10. März 2012 14:38

Alle Antworten

  • Can you check if you have the same issue in SQL 2008 R2?. I created a simple maintainance plan that had 9 tasks and i was able to see the logs in same order of execution.

    If you can share additional details about maintainance plan, tasks involved or export the SSIS package and send it to us, we could take a look.

    Thanks

    Sethu Srinivasan [MSFT]

    SQL Server

    http://blogs.msdn.com/sqlagent

    Mittwoch, 29. Februar 2012 19:16
  • Hello Sethu,

    I'm not at the office now, but tomorrow I'll send to you the copy of my maintenance report.

    Best regards.

    Mittwoch, 29. Februar 2012 22:31
  • Can you check if you have the same issue in SQL 2008 R2?. I created a simple maintainance plan that had 9 tasks and i was able to see the logs in same order of execution.

    If you can share additional details about maintainance plan, tasks involved or export the SSIS package and send it to us, we could take a look.

    Thanks

    Sethu Srinivasan [MSFT]

    SQL Server

    http://blogs.msdn.com/sqlagent

    See Below and thanks in advance for help

    NEW COMPONENT OUTPUT
    Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.5000
    Report was generated on "DATASERVER".
    Maintenance Plan: Sauvegarde application
    Duration: 00:34:55
    Status: Succeeded.
    Details:
    Update Statistics Task (DATASERVER)
    Update Statistics on DATASERVER
    Databases: siriusbd
    Object: Tables and views
    All existing statistics
    Task start: 2012-03-01T01:32:50.
    Task end: 2012-03-01T01:53:04.
    Success
    Execute T-SQL Statement Task (DATASERVER)
    Execute TSQL on DATASERVER
    Execution time out: 0
    Task start: 2012-03-01T01:30:41.
    Task end: 2012-03-01T01:30:41.
    Success
    Command:USE [master]
    GO
    ALTER DATABASE [siriusbd] SET RECOVERY SIMPLE WITH NO_WAIT
    GO
    Tâche de nettoyage d'historique (DATASERVER)
    Cleanup history on DATASERVER
    History type: Backup,Job,Maintenance Plan
    Age: Older than 2 Weeks
    Task start: 2012-03-01T02:05:29.
    Task end: 2012-03-01T02:05:30.
    Success
    Back Up Database Task (DATASERVER)
    Backup Database on DATASERVER
    Databases: siriusbd
    Type: Transaction Log
    Append existing
    Task start: 2012-03-01T01:30:37.
    Task end: 2012-03-01T01:30:41.
    Success
    Rebuild Index Task (DATASERVER)
    Rebuild index on DATASERVER
    Databases: siriusbd
    Object: Tables and views
    Original amount of free space
    Task start: 2012-03-01T01:53:12.
    Task end: 2012-03-01T01:58:02.
    Success
    Tâche Sauvegarder la base de données (DATASERVER)
    Backup Database on DATASERVER
    Databases: siriusbd
    Type: Full
    Append existing
    Task start: 2012-03-01T01:58:02.
    Task end: 2012-03-01T02:05:29.
    Success
    Tâche Vérifier l'intégrité de la base de données (DATASERVER)
    Check Database integrity on DATASERVER
    Databases: siriusbd
    Include indexes
    Task start: 2012-03-01T01:30:41.
    Task end: 2012-03-01T01:32:22.
    Success
    Execute T-SQL Statement Task 1 (DATASERVER)
    Execute TSQL on DATASERVER
    Execution time out: 0
    Task start: 2012-03-01T01:58:02.
    Task end: 2012-03-01T01:58:02.
    Success
    Command:USE [master]
    GO
    ALTER DATABASE [siriusbd] SET RECOVERY FULL WITH NO_WAIT
    GO
    Tâche de nettoyage de maintenance BAK (DATASERVER)
    Maintenance Cleanup on DATASERVER
    Cleanup Database Backup files
    Age: Older than 2 Days
    Task start: 2012-03-01T02:05:30.
    Task end: 2012-03-01T02:05:30.
    Success

    Donnerstag, 1. März 2012 08:37
  • First, I think your maintenance plan tasks are not correct.  You should not be switching recovery models from full to simple - and back again.  That breaks your log chain and will prevent point in time recovery past that break.

    For example, if you backup takes 3 hours - and one minute prior to completing the backup your system crashes, you will only be able to restore to the last transaction log backup performed before the backup started.  You could end up losing 3 hours of data...and worse of all, if the previous backup file is corrupted and you cannot restore from it - you have to go to the backup file before that and you cannot restore any transaction logs after the switch in recovery model.

    As to your issue with the order of the tasks, have you insured that each task has a dependency on the previous task?  Either a successful or completion step?

    If that is all correct, then the issue is just where in the report that task is reported and you just have to verify the times. 


    Jeff Williams

    Samstag, 3. März 2012 14:11
  • Hi,

    Thanks for you reply.

    I agree with you answer about the switch between Full and Simple recovery models, but the maintenance plan runs every night and no users works on the application.

    I think that the goal of the person who has made this plan was to limit the duration of the tasks in order to be sure that the plan was finished before the morning and the opening of the office.

    I can do benchmark between the two methods,also without switching full to simple,simple to Full, and without changing the recovery model for the fun and verify !!.

    About the dependecies, we have successful completion steps only. Do you think the problem come from this ?

    Best regards

    Freitag, 9. März 2012 14:49
  • Hi,

    Thanks for you reply.

    I agree with you answer about the switch between Full and Simple recovery models, but the maintenance plan runs every night and no users works on the application.

    I think that the goal of the person who has made this plan was to limit the duration of the tasks in order to be sure that the plan was finished before the morning and the opening of the office.

    I can do benchmark between the two methods,also without switching full to simple,simple to Full, and without changing the recovery model for the fun and verify !!.

    About the dependecies, we have successful completion steps only. Do you think the problem come from this ?

    Best regards

    Regardless of whether or not users are accessing the system, switching to simple recovery model breaks the log chain.  If you have to restore to a prior backup because the latest available backup file is corrupted you cannot restore past a break in the log chain.

    It is a risk you don't need to take.  Switching does not improve performance in any way - since everything is logged in all recovery models.  The only advantage to using simple recovery is that you could get minimally logged transactions which is not a performance improvement - rather it would reduce how large the log file gets.  That could be accomplished by switching to bulk_logged instead - which won't break the log chain.

    Note: there are issues with switching to bulk_logged also.  The issue here is that you cannot restore to a point in time where minimally logged transactions have occurred.  But, that would be a whole lot better than breaking the log chain.

    As long as you have constraints defined for each task - you are fine.  The only issue you have then is that the report is not ordered - but the times appear to be correct.


    Jeff Williams

    • Als Antwort markiert Stephanie Lv Donnerstag, 15. März 2012 08:36
    Samstag, 10. März 2012 14:38