none
job log, how to get the details of failure in order to find cause.

    Question

  • NEW COMPONENT OUTPUT
    Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.4053
    Report was generated on " ".
    Maintenance Plan: checkdb
    Duration: 00:42:56
    Status: Warning: One or more tasks failed..
    Details:
    Check Database Integrity Task ()
    Check Database integrity on Local server connection
    Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
    Databases: All databases
    Include indexes
    Task start: 2011-12-13T02:00:13.
    Task end: 2011-12-13T02:43:06.
    Failed:(100) An exception occurred while executing a Transact-SQL statement or batch.

      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp".: 100% complete  End Progress  Progress: 2011-12-13 02:00:13.06     Source: Check Database Integrity Task      Executing query "USE [ADTI]  ".: 50% complete  End Progress  Progress: 2011-12-13 02:00:55.96     Source: Check Database Integrity Task      Executing query "DBCC CHECKDB WITH NO_INFOMSGS  ".: 100% complete  End Progress  Progress: 2011-12-13 02:00:56.00     Source: Check Database Integrity Task      Executing query "USE [ADTI-TEST]  ".: 50% complete  End Progress  Progress: 2011-12-13 02:01:02.18     Source: Check Database Integrity Task      Executing query "DBCC CHECKDB WITH NO_INFOMSGS  ".: 100% complete  End Progress  Progress: 2011-12-13...  The package execution fa...  The step failed.

     


    • Edited by George Zhou Monday, January 30, 2012 8:15 PM
    Wednesday, December 14, 2011 2:01 PM

Answers

  • Enable logging to a file in the file system.  Edit the Job Properties, then Edit the Step Properties, then click on Advanced and provide an Output File name to write the information to.

    Personally, I'd recommend that you stop using Database Maintenance Plans entirely, and switch to a much more robust maintenance implementation using TSQL scripts.  A free set of scripts that I recommend to all of my consulting clients can be found on Ola Hallengren's blog:

    http://ola.hallengren.com/

    These take care of logging as a default configuration with a significant amount of detail that you don't get from Maintenance Plans.


    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by George Zhou Wednesday, December 14, 2011 2:16 PM
    Wednesday, December 14, 2011 2:09 PM
    Moderator

All replies

  • Enable logging to a file in the file system.  Edit the Job Properties, then Edit the Step Properties, then click on Advanced and provide an Output File name to write the information to.

    Personally, I'd recommend that you stop using Database Maintenance Plans entirely, and switch to a much more robust maintenance implementation using TSQL scripts.  A free set of scripts that I recommend to all of my consulting clients can be found on Ola Hallengren's blog:

    http://ola.hallengren.com/

    These take care of logging as a default configuration with a significant amount of detail that you don't get from Maintenance Plans.


    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by George Zhou Wednesday, December 14, 2011 2:16 PM
    Wednesday, December 14, 2011 2:09 PM
    Moderator
  • Hi George,

    Logging was turned on by default for me. You have to go into the maintance plan editor in order to see where it is getting logged to.

    To find that:

    1. Right click the MP that you want to log.
    2. Click Modify.
    3. When it opens you will see a tool bar across the top of the editor. It will contain "Add Subplan" and a bunch of icons. You are looking for the 7th option over. It looks like 2 pieces of paper "Reporting and Logging".
    4. Once you find that click it!
    5. That will tell you where is is already being logged to. 

    Please note that if you set the logging through the actual job any time that you update the MP you will lose this logging setting.

    It is best to log it at the actual MP level and not the Job level.

    I hope that this works for you too!! Have a good one.


    Thursday, December 15, 2011 9:04 AM