none
update statistics failing intermittently in SQL Server 2005

    Question

  • We have a job that runs nightly to updates statistics. It takes about 1.5 hrs to run on a 30GB db. It is failing about every 3 or 4 days with the following error message in job history:

     

    Message

    Executed as user: CH\sqlbackup. Microsoft (R) SQL Server Execute Package Utility  Version 9.00.4035.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  12:00:00 AM  Progress: 2011-04-06 00:00:02.15     Source: {1214E6AF-3610-46FB-8F10-F4A88F29AD42}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp".: 100% complete  End Progress  Error: 2011-04-06 01:15:01.18     Code: 0xC0024104     Source: Update Statistics Task      Description: The Execute method on the task returned error code 0x80131501 (An exception occurred while executing a Transact-SQL statement or batch.). The Execute method must succeed, and indicate the result using an "out" parameter.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  12:00:00 AM  Finished: 1:15:01 AM  Elapsed:  4500.77 seconds.  The package execution failed.  The step failed.


    PatM Reno
    Wednesday, April 6, 2011 4:50 PM

Answers

All replies

  • Hi,

    Please follow the workaround mentioned here to get more closer to the cause of the error. And keep us posted on your findings.

    Sometimes this type of Issues will come in case of limitations on 64-bit computers some features are available only in 32 bit version.
    For reference use this link.


    Thanks & Regards, Pramilarani.R
    Thursday, April 7, 2011 4:56 AM
  • Hi PatM-Reno,

    You might try to delete the job and create another one. Here is the thread has the same symptom: http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/0eca38cf-fa4a-4d8f-99fc-f642d8652d6c/.

    If you have any question, please feel free to let me know.

    Best Regards,
    Stephanie Lv

    Wednesday, April 13, 2011 11:16 AM
  • This may be a dumb question, but how did you get the full message?  When I drill in on the history and message of my failing job, it's full of ellipses, mid sentence, obscuring any bit of what might be useful information:

    Message

    Executed as user: WP2\SYSTEM. ...Version 9.00.3042.00 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  1:00:02 AM  Progress: 2012-03-22 01:00:11.52     Source: {4E20BA0D-142B-44C4-AE76-8407F2031FA3}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp".: 100% complete  End Progress  Progress: 2012-03-22 01:00:54.01     Source: Update Statistics Task      Executing query "use [WP]  ".: 0% complete  End Progress  Progress: 2012-03-22 01:00:54.45     Source: Update Statistics Task      Executing query "UPDATE STATISTICS [wpdb].[ApiKey]   WITH FULL".: 0% complete  End Progress  Progress: 2012-03-22 01:00:54.45     Source: Update Statistics Task      Executing query "use [WP]  ".: 0% complete  End Progress  Progress: 2012-03-22 01:00:56.28     Source: Update Statistics Task      Executing query "UPDATE STATISTICS [wpdb].[AuthToken]   WITH F".: 1% complete  End Progress  Progress: 2012-...  The package execution fa...  The step failed.

    Friday, March 30, 2012 5:47 PM
  • Check to see that from maintenance plan history , right click the maint plan and view history

    Thanks, Leks

    Friday, March 30, 2012 10:22 PM
  • We have a very large database (3TB) & need to run update stats. For few tables in database the update stats should not be run this is requirement from the application team. Can anyone help with script that will skip the tables on which update stats is not be run & run update stats on all other tables in database. 
    Thursday, May 3, 2012 9:25 PM
  • Why do you need to run update statistics?  Did you turn off auto update and auto update asynchronously?  If so why not turn them back on?  If you are rebuilding indexes then you may not need to do this.

    Francis Hanlon SQL DBA

    Thursday, May 10, 2012 8:25 PM