none
Server Maintenance Plan Failed on SQL 2005 SP2

    Question

  • Iam getting the following error message while executing maintenance plan .Iam using same account which SQL agent runs.

     

    90.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 19:47:05 Progress: 2008-07-21 19:47:50.99 Source: {6D9455A1-ED47-4569-8786-D9D5724D477E} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress DTExec: The package execution returned DTSER_FAILURE (1). Started: 19:47:05 Finished: 19:47:51 Elapsed: 46.703 seconds. The package execution failed. The step failed.

     

    NEW COMPONENT OUTPUT
    Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3054
    Report was generated on "P11002".
    Maintenance Plan: DB_MaintenancePlan_dbcc_user_db
    Duration: 00:00:00
    Status: Warning: One or more tasks failed..
    Details:
    Check Database Integrity Task (P11002)
    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 user databases
    Include indexes
    Task start: 2008-07-21T19:47:51.
    Task end: 2008-07-21T19:47:51.
    FailedSad0) Alter failed for Server 'P11002'.

     

     

    Regards

    PLN

    Monday, July 21, 2008 6:44 PM

Answers

All replies

  • It looks as if one of the SQL Tasks has failed. It refers to an ALTER statement failure. Do you have any log file output from the Maintenance Plan? This should provide more information on where the problem occurred. There is a facility within the Maintenance Plan to output the progress of the Plan.

     

    Tuesday, July 22, 2008 7:31 AM
  • Hi Niall

     

    Thanks for your reply..

     

    Iam keeping only one Task on this (ie..Check Database integrity Task)..There is no Alter statement..

     

    Out put of the logfile i have mentioned below..only this information iam getting in the log..

     

    NEW COMPONENT OUTPUT
    Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3054
    Report was generated on "P11002".
    Maintenance Plan: DB_MaintenancePlan_dbcc_user_db
    Duration: 00:00:00
    Status: Warning: One or more tasks failed..
    Details:
    Check Database Integrity Task (P11002)
    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 user databases
    Include indexes
    Task start: 2008-07-21T19:47:51.
    Task end: 2008-07-21T19:47:51.
    Failed0) Alter failed for Server 'P11002'.

     

     

    Any Quick help on this ?

     

    Regards

    PLN

    Wednesday, July 23, 2008 5:03 PM
  • Edit the Maintenance plan and go to Reporting & logging option (you can find in the top), there you can find a checkbox for "Log extended information", enable this and save the package and execute it. The new log file will have all the details.
    Thursday, July 24, 2008 1:25 AM
    Moderator
  • I am having the exact same problem.  We have a database maintenance plan with 1 task in it named "Check Database Integrity Task".  Task details: Connection = Local server connection, Database(s): Specific databases (I only have master checked), include indexes is unchecked.  When I right click on the plan and click Execute, the plan fails.  If I right click on the plan and click View History, it displays the information: Server FH_SQL\Veritas, Task detail: Check Database integrity on Local server connection Databases: master, Error number: 0, Error message: Alter failed for Server 'FH_SQL\\Veritas'.  I do not know why this message displays the double '\\' for the named instance, and this may be part of the problem with the job failing.  I have also checked "Reporting & logging option" and here are the details:
    NEW COMPONENT OUTPUT
    Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3042
    Report was generated on "FH_SQL\Veritas".
    Maintenance Plan: MaintenancePlan Integrity Check Test
    Duration: 00:00:00
    Status: Warning: One or more tasks failed..
    Details:
    Check Database Integrity (FH_SQL\Veritas)
    Check Database integrity on Local server connection
    Databases: master
    Task start: 2008-07-28T10:00:54.
    Task end: 2008-07-28T10:00:54.
    FailedSad0) Alter failed for Server 'FH_SQL\\Veritas'.

    Please let me know what you think and thank you!
    Monday, July 28, 2008 2:04 PM
  • I am having the exact same problem.  We have a database maintenance plan with 1 task in it named "Check Database Integrity Task".  Task details: Connection = Local server connection, Database(s): Specific databases (I only have master checked), include indexes is unchecked.  When I right click on the plan and click Execute, the plan fails.  If I right click on the plan and click View History, it displays the information: Server FH_SQL\Veritas, Task detail: Check Database integrity on Local server connection Databases: master, Error number: 0, Error message: Alter failed for Server 'FH_SQL\\Veritas'.  I do not know why this message displays the double '\\' for the named instance, and this may be part of the problem with the job failing.  I have also checked "Reporting & logging option" and here are the details:
    NEW COMPONENT OUTPUT
    Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3042
    Report was generated on "FH_SQL\Veritas".
    Maintenance Plan: MaintenancePlan Integrity Check Test
    Duration: 00:00:00
    Status: Warning: One or more tasks failed..
    Details:
    Check Database Integrity (FH_SQL\Veritas)
    Check Database integrity on Local server connection
    Databases: master
    Task start: 2008-07-28T10:00:54.
    Task end: 2008-07-28T10:00:54.
    FailedSad0) Alter failed for Server 'FH_SQL\\Veritas'.

    Please let me know what you think and thank you!
    Monday, July 28, 2008 2:05 PM
  • The last thing I did on the server was to move tempdb to another drive and I also changed the accounts that SQL server runs under.  The SQL server and SQL server agent were localsystem and they are now different domain accounts.
    Monday, July 28, 2008 2:07 PM
  • Here are the Job History details:
    Date        7/28/2008 10:00:53 AM
    Log        Job History (MaintenancePlan Integrity Check Test.Subplan_1)

    Step ID        1
    Server        FH_SQL\VERITAS
    Job Name        MaintenancePlan Integrity Check Test.Subplan_1
    Step Name        Subplan_1
    Duration        00:00:01
    Sql Severity        0
    Sql Message ID        0
    Operator Emailed      
    Operator Net sent      
    Operator Paged      
    Retries Attempted        0

    Message
    Executed as user: TROTTLAW\fh_veritas_agent. Microsoft (R) SQL Server Execute Package Utility  Version 9.00.3042.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  10:00:53 AM  Progress: 2008-07-28 10:00:54.14     Source: {F1D53ECB-F1F2-46E5-841D-6388CE3E8A3C}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp".: 100% complete  End Progress  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  10:00:53 AM  Finished: 10:00:54 AM  Elapsed:  1.563 seconds.  The package execution failed.  The step failed.
    Monday, July 28, 2008 2:32 PM
  • Hi

     

    go this link and download the lastest hotfix and install.. This will short out this "Check Database Integrity" issue..

     

    http://www.microsoft.com/downloads/details.aspx?FamilyId=A7C903FE-35A7-4BB2-8E73-932770099711&displaylang=en&displaylang=en

     

     

    Thanks

    Nagu

    Friday, August 1, 2008 6:27 PM
  • I was able to resolve my error by running the following statement per Microsoft article:

    sp_configure

     

    'allow updates', 0

    reconfigure

     

    with override

    • Proposed as answer by Aviel Iluz Monday, August 10, 2009 5:35 AM
    Thursday, July 2, 2009 2:17 PM
  • we had exactly the same problem after applying sp3.

    I managed to solve the problem following the instructins suggested by a former thread:

    sp_configure  'allow updates', 0

    reconfigure

     

    with override


    DBA
    Monday, August 10, 2009 5:40 AM