Post reply

Error while altering a table - maximum row size exceeds

  • October 13, 2008 at 5:13 am

    #72899

    Hi,

    I am trying to delete one column from my table using ALTER statement and the following is the SQL statement.

    alter table scTabForm0 drop column DataCaptureAutoTaggingXML

    But i got the following error

    Warning: The table 'scTabForm0' has been created but its maximum row size (18248)

    exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table

    will fail if the resulting row length exceeds 8060 bytes.

    Please help me out from this bug.

    Thanks in advance.

    Regards,

    Hema,

  • October 13, 2008 at 5:22 am

    #884075

    Check the following link

    http://www.sqlservercentral.com/Forums/Topic212912-169-1.aspx

  • October 13, 2008 at 5:29 am

    #884080

    I checked the above link and tried this

    Select SUM(C.Length) as Length from dbo.SysColumns C where C.id = object_id('ScTabForm0')

    I got the grid values as - 17876.

    Please justify the case and explain me the solution.

    Many thanks

    Hema

  • October 13, 2008 at 5:36 am

    #884085

    run the following DBCC commands:

    DBCC CHECKDB

    DBCC CHECKTABLE

    DBCC CHECKCATALOG

    DBCC UPDATEUSAGE

    try it, may solve your problem.

  • October 13, 2008 at 5:45 am

    #884091

    Hi,

    As of now its not working in my server and i don't know how far it is help full to my case.please explain me a bit more.

    Thanks,

    Hema

  • October 13, 2008 at 11:45 pm

    #884413

    It will be helpful for me if any body finds the solution.

    Thanks

    Hema

  • October 15, 2008 at 5:27 am

    #885124

    It is only warning. Your table has so many columns (or so big columns) that it si possible you will not be able to store all data by INSERT statement. You can accept this possible danger if you are sure that your INSERT statements never exceed this limitation but can you really? You can avoid this warning by redesign the table. Row size check is done with column definition changes and that is why you have seen this warning when deleting column too. That means you already had this situation before trying to delete column.

  • October 15, 2008 at 8:37 am

    #885267

    hemakrmmc (10/13/2008)

    Please help me out from this bug.

    It's not a bug. It's a warning that your table's too wide. The max size of a row in SQL server is 8060 bytes. Your table has a maximum possible row size of much larger. If you try to insert a row that's bigger than 8060 bytes, the insert will fail.

    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

  • October 11, 2012 at 12:13 am

    #1547893

    run

    DBCC CLEANTABLE (0,

    )

    Ian Cockcroft
    MCITP BI Specialist

Viewing 9 posts - 1 through 8 (of 8 total)

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