Post reply

updatestats job

  • September 27, 2008 at 12:59 pm

    #126525

    Hi All,

    we have updatestats job on a production server. Its SQL Server 2005

    Today it got failed with below error :

    Msg 601, Sev 12, State 3, Line 1 : Could not continue scan with NOLOCK due to data movement. [SQLSTATE 42000]

    can any body have any idea on this ?

    Please help me in this ?

    Thanks In advance,

    Raghavender Chavva

    Thank You.

    Regards,
    Raghavender Chavva

  • September 27, 2008 at 1:30 pm

    #878091

    Please help in this as this is a seviour issue.

    Thank You.

    Regards,
    Raghavender Chavva

  • September 27, 2008 at 3:26 pm

    #878095

    Make sure you have a backup nearby.

    Run diagnostics on the DB to validate the integrity.

    The 2 options I found to repair this problems are use a good backup to restore DB to previous valid state, or rebuild the indexes (not updatestats).

    Let us know how it went for you... there's not a lot of documentation on this site for that error.

  • September 27, 2008 at 5:36 pm

    #878102

    Unfortunately the Good backup file has deleted, but we have the recent backup file. when i ran DBCC Checkdb on the database found 85 consistency errors.

    We are facing this consistency issues from last 4 weeks on this database.

    Thank You.

    Regards,
    Raghavender Chavva

  • September 28, 2008 at 5:08 am

    #878138

    What have you tried to repair them?

  • September 28, 2008 at 5:40 am

    #878140

    Please run the following and post the output here

    DBCC CheckDB(<Database Name> ) WITH NO_INFOMSGS

    Do you have any backup that doesn't have corruptions in 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

  • September 28, 2008 at 11:23 am

    #878192

    Unfortunately we dont have any backups with no consistency errors.

    We keep only latest backups.

    when i ran DBCC Checkdb(MDB) on the database found 85 consistency errors.

    Output of the Checkdb is as below:

    CHECKDB found 0 allocation errors and 85 consistency errors in database 'DBNAME'.

    repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (dbname).

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Thank You.

    Regards,
    Raghavender Chavva

  • September 28, 2008 at 11:41 am

    #878193

    Are those errors in the non clustered indexes or in the base table?

  • September 28, 2008 at 12:55 pm

    #878194

    Raghavender (9/28/2008)

    Output of the Checkdb is as below:

    CHECKDB found 0 allocation errors and 85 consistency errors in database 'DBNAME'.

    repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (dbname).

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Was that the entire output? There should have been details on each of the consistency errors found. If there was, please post that.

    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

  • September 28, 2008 at 2:35 pm

    #878201

    There is approximate 500KB of output file and I habe tried to paste here but no luck.

    total summery of output(DBCC CHECKDB) is the Database is having 85 consistency errors.

    Thank You.

    Regards,
    Raghavender Chavva

  • September 28, 2008 at 2:36 pm

    #878202

    And we are having consistency errors in Indexes as well as Tables also.

    Thank You.

    Regards,
    Raghavender Chavva

  • September 28, 2008 at 2:37 pm

    #878203

    And we are having consistency errors in Indexes as well as in Tables also.

    Thank You.

    Regards,
    Raghavender Chavva

  • September 28, 2008 at 2:45 pm

    #878205

    500kb of output even with no_infomsgs? Wow.

    If the errors are in the base tables, and you have no clean backup, then your only option is to run checkDB with the repair_allow_data_loss option. The DB has to be in single user mode for it to run.

    You will lose data. Without seeing the full output of checkDB, I can't even guess at how much and from what tables, but with 78 consistency errors it's going to be a fair bit.

    Once the repair finishes, you will need to examine the DB carefully. There's a chance that you will have some referential key violations caused by parent rows been discarded by the checkdb. Sepending on what the DB stores, theere may be other problems too.

    Once you've repaired and fixed everything, it may be a good idea to implement regular database integrity checks so that you know exactly when corruption occurs and you can always recover to a clean backup. Restoring from backup is the preferred way to recover from corruption.

    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

  • September 28, 2008 at 3:00 pm

    #878206

    And we are having consistency errors in Indexes as well as in Tables also.

    Thank You.

    Regards,
    Raghavender Chavva

  • September 28, 2008 at 3:07 pm

    #878208

    Thank you.

    But will these consistency errors effect the updatestats job ?

    When I executed the stored procedure directly it got executed successfully.

    Thank You.

    Regards,
    Raghavender Chavva

Viewing 15 posts - 1 through 15 (of 16 total)

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