• richardmgreen1 - Thursday, April 19, 2018 4:06 AM

    ScottPletcher - Wednesday, April 18, 2018 9:27 AM

    For that type of table definition, I'd suggest forcing all LOB columns completely out of the main table space (since SQL doesn't allow you to customize column-by-column).  That should avoid any overly-large row size, I would think, although theoretically SQL should have pushed all of them out to LOB space if required anyway.

    Specifically, issue this command on the table:

    USE <db_name_containing_table>
    EXEC sp_tableoption 'tbl_MT_EMR_Document_Tto_Medications_GENMT', 'large value types out of row'

    Hi Scott

    I ran that command against all of our tables (got a few error messages where the data types were inappropriate but I was expecting that).

    Unfortunately, it didn't work and I'm still error messages along the lines of:-
    Cannot create a row of size 8220 which is greater than the allowable maximum row size of 8060.

    I ran the command and then ran our load.  Did I need to restart the server/service before running the load (I assumed not)?

    No, don't need to restart.

    Yes, that table is definitely too large.  Every MAX pointer still takes 16 bytes.  I have code that calcs roughly the max possible overhead bytes, and it returns this:
    total_max_row_length    data_length    var_lengths    bit_map    general_row_overhead
    9599    8465    1054    69    11
    This might be overstated, if the 2-byte varchar length counter is in the overflow area when you force the overflow data there, but either way, it could be past 8060 bytes.
    Basically you'll need to split this table into two tables, putting roughly half the column data into each one.

    Btw, I wouldn't -- and didn't -- suggest running that code arbitrarily against all tables, it's not a generic solution.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.