Anmelden
 
HomeLibraryDownloadsSupportWebcasts & EventsCommunityForenBlogs
Note: Forums will be making significant UX changes to address key usability improvements surrounding search, discoverability and navigation. To learn more about these changes please visit the announcement which can be found HERE.
Ressourcen für IT-Professionals >
> 
> 
Report Maintenance Plan out of order in SQL Server 2005

Beantwortet Report Maintenance Plan out of order in SQL Server 2005

  • Mittwoch, 29. Februar 2012 13:18
     
     
    Anmelden
    0

     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

     

Alle Antworten

  • Mittwoch, 29. Februar 2012 19:16
    Moderator
     
     
    Anmelden
    0

    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 22:31
     
     
    Anmelden
    0

    Hello Sethu,

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

    Best regards.

     
  • Donnerstag, 1. März 2012 08:37
     
      Enthält Code
    Anmelden
    0

    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

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

    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

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

    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

     
  • Samstag, 10. März 2012 14:38
     
     Beantwortet
    Anmelden
    0

    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