Post reply

maintenance plan with rebuild index failed

  • December 12, 2011 at 2:34 am

    #248010

    HI ,

    Every weekend we are running a maintenance plan with rebuild index ,update statistics and database integrity .Last week plan is failed with below error

    " failed with the following error: "A severe error occurred on the current command. The results, if any, should be discarded.

    A severe error occurred on the current command. The results, if any, should be discarded.".

    Possible failure reasons: Problems with the query, "ResultSet" property not set correctly,

    parameters not set correctly, or connection not established correctly.

    Thanks ,

    Lavanya

  • December 12, 2011 at 3:33 am

    #1419922

    Probable reasons:

    •Upgrade issues / BUG fix

    •Corruption DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

    •Or Restart required.

    Please follow the discussion on SSC.

    A severe error occurred on the current command.The results, if any, should be discarded.

    http://www.sqlservercentral.com/Forums/Topic521583-149-1.aspx

  • December 12, 2011 at 4:25 am

    #1419941

    Could be corruption, could be that someone killed the session (it gives the same error). If you suspect corruption, do not restart SQL until you've checked for it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 12, 2011 at 8:41 am

    #1420115

    Firstly do a check db integrity, then do rebuild index

    You don't need an update stats if you doing a rebuild index.

    Regards,

    Skybvi

    Regards
    Sushant Kumar
    MCTS,MCP

  • December 12, 2011 at 8:43 am

    #1420117

    SKYBVI (12/12/2011)

    Firstly do a check db integrity, then do rebuild index

    You don't need an update stats if you doing a rebuild index.

    Regards,

    Skybvi

    Except for all the stats that are not updated by the index rebuild. Which is usually a lot of them 😉

  • December 12, 2011 at 8:46 am

    #1420120

    Ninja's_RGR'us (12/12/2011)

    SKYBVI (12/12/2011)

    Firstly do a check db integrity, then do rebuild index

    You don't need an update stats if you doing a rebuild index.

    Regards,

    Skybvi

    Except for all the stats that are not updated by the index rebuild. Which is usually a lot of them 😉

    Ya but mostly it will be redundunt.

    Its advisable though to keep both rebuild index and update stats in diff jobs at diff times.

    Regards,

    Skybvi

    Regards
    Sushant Kumar
    MCTS,MCP

  • December 12, 2011 at 8:51 am

    #1420125

    SKYBVI (12/12/2011)

    Ninja's_RGR'us (12/12/2011)

    SKYBVI (12/12/2011)

    Firstly do a check db integrity, then do rebuild index

    You don't need an update stats if you doing a rebuild index.

    Regards,

    Skybvi

    Except for all the stats that are not updated by the index rebuild. Which is usually a lot of them 😉

    Ya but mostly it will be redundunt.

    Its advisable though to keep both rebuild index and update stats in diff jobs at diff times.

    Regards,

    Skybvi

    If they're really redundant, then delete them. :hehe:

    If SS recreates them it's because they are needed.

  • December 12, 2011 at 8:58 am

    #1420132

    SKYBVI (12/12/2011)

    Ninja's_RGR'us (12/12/2011)

    Except for all the stats that are not updated by the index rebuild. Which is usually a lot of them 😉

    Ya but mostly it will be redundunt.

    Disagree. You can easily have more column statistics than index statistics, and the maint plan does allow for just column statistics to be updated.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 12, 2011 at 9:08 am

    #1420140

    GilaMonster (12/12/2011)

    SKYBVI (12/12/2011)

    Ninja's_RGR'us (12/12/2011)

    Except for all the stats that are not updated by the index rebuild. Which is usually a lot of them 😉

    Ya but mostly it will be redundunt.

    Disagree. You can easily have more column statistics than index statistics, and the maint plan does allow for just column statistics to be updated.

    It seems contradictory....

    Sometimes, you advice not to run update stats after rebuild index ( when some1 asks the order of all maintenance tasks)

    and

    sometimes you tell to run it.

    So, is it that there should be alternate jobs with that adjustment.?

    Regards,

    Skybvi

    Regards
    Sushant Kumar
    MCTS,MCP

  • December 12, 2011 at 9:13 am

    #1420143

    SKYBVI (12/12/2011)

    GilaMonster (12/12/2011)

    SKYBVI (12/12/2011)

    Ninja's_RGR'us (12/12/2011)

    Except for all the stats that are not updated by the index rebuild. Which is usually a lot of them 😉

    Ya but mostly it will be redundunt.

    Disagree. You can easily have more column statistics than index statistics, and the maint plan does allow for just column statistics to be updated.

    It seems contradictory....

    Sometimes, you advice not to run update stats after rebuild index ( when some1 asks the order of all maintenance tasks)

    and

    sometimes you tell to run it.

    So, is it that there should be alternate jobs with that adjustment.?

    Regards,

    Skybvi

    She never said (meant) not to update stats. She said not to update the stats that were just updated in the index rebuild.

  • December 14, 2011 at 12:07 am

    #1421030

    Hi,

    Please let me know good maintenance plan for Production Database size >5GB

    Any proper order like (rebuild index ,update statistics and database integrity etc...) Every weekend or Daily

    Thanks in advance

  • December 14, 2011 at 12:12 am

    #1421033

    jamessdba (12/14/2011)

    Hi,

    Please let me know good maintenance plan for Production Database size >5GB

    Any proper order like (rebuild index ,update statistics and database integrity etc...) Every weekend or Daily

    Thanks in advance

    Please don't hijack the thread. Start a new thread for your question. It helps us not mixing up scenarios / solutions.

  • December 14, 2011 at 12:58 am

    #1421062

    Ok thanks

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply