Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unhappy Unanswered: "Possible schema corruption. Run DBCC CHECKCATALOG." when ALTERing a column

    When changing a column from char(6) to varchar(7) via T-SQL:
    Code:
    ALTER TABLE dbo.table_name
      ALTER COLUMN existing_column varchar(7) NULL
    I recieve the following error message
    Code:
    Msg 211, Level 23, State 7, Line 1
    Possible schema corruption. Run DBCC CHECKCATALOG.
    Msg 0, Level 20, State 0, Line 0
    A severe error occurred on the current command.  The results, if any, should be discarded.
    I have dun various DBCC commands (checkcatalog, checkdb) and neither has reported any issues. I have also run INDEXDEFRAG on the two indexes on this table (1 clustereed, 1 non-clustered - neither are on this column) successfully.

    The database in question is a restore from the production database to the development server. I have restored this development database to my local VM and the same command ran successfully.

    On the dev environment, @@Version returns:
    Microsoft SQL Server 2005 - 9.00.1399.06 (X64) Oct 14 2005 00:35:21 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

    As you can see, we are a tad behind on Service Packs - I have requested that the DBAs upgrade and have been waiting over 6 months for this to occur (whole other saga).

    My local VM @@Version is:
    Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    So as you can see, the environments to differ, but I don't believe that this is the problem.

    Essentially I will need to run this alter command on live release at some point, but if I can't get it to work on test, then there's no way I'll get to run it live...

    Any suggestions?

    If you require any more information to assist me, then just ask and I'll see what I can do!
    George
    Home | Blog

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Dropping and re-creating the existing indexes has had no effect...
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    CHECKCATALOG reports no errors either!
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It appears that this table is the only one affected.

    The most recent change to the schema was to change a text data type to varchar(250) which I fear may be the culprit...

    I have since run DBCC CLEANTABLE to try reclaim any page space but again, with no luck.

    If I can prove that this is the only table that is affected then I am willing to drop and re-create it. I want to prove this if at all possible because it's a bit of a beast (3M+ rows)...
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    What happens if you change from CHAR(6) to CHAR(7)? i.e. Don't do the datatype change, but alter the length. Also, what happens if you go from char(6) to varchar(6) (not changing the length)?

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have tried them both previously, both still fail. Have even tried null-ifying the column and making the change, all to no avail!
    Thanks for the suggestion though.

    I think I have cracked it - If I re-create the column all seems to be happy in the world...

    Would like to also say thanks to Jonathan Kehayis and Paul Randal for their help via the twittersphere
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Because of previous incompetent coders - I cannot simply get away with dropping and recreating the column as this affect the column "order" of the table, and there are too many dependant SELECT * scripts that fall over.

    Re-create the table and all its constraints looks like my only option; fan-ruddy-tastic!

    glad that this DB gets superseeded in the next 6-12 months!
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    P.S. I am very aware that I should change the sprocs, but this isn't my database or objects, so I don't have a lot of choice in the matter.

    Fun.
    George
    Home | Blog

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Quote Originally Posted by gvee
    Have tried them both previously, both still fail. Have even tried null-ifying the column and making the change, all to no avail!
    Thanks for the suggestion though.

    I think I have cracked it - If I re-create the column all seems to be happy in the world...

    Would like to also say thanks to Jonathan Kehayis and Paul Randal for their help via the twittersphere
    pics, or it didn't happen.

    OK. A link would be nice, though.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Link to what exactly? *confused*

    Scripted out the table-recreation and ran it on test and all is fine AFAIK...
    Will hand the code off to the DBAs to toy with
    George
    Home | Blog

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Twitter has no ability to link to tweets?

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Jonathan forwarded my question on to Paul Randal who replied with:
    Quote Originally Posted by Paul Randall (via Twitter)
    @SQLSarg @gveeYou've got system table corruption of some kind. Could be a bug. Try exporting the data into a new table +drop old one.
    As for linking directly to a tweet, or indeed a conversation/mention set, I have no idea
    George
    Home | Blog

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can click on Show Conversation here:
    from:sqlsarg to:gvee since:2009-06-16 - Twitter Search

    Poots - Interweb Stalker
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •