Confluence indexes are bigger than 900 bytes on MSSQL which is not recommended

XMLWordPrintable

    • 5
    • Severity 3 - Minor
    • 0

      MSSQL database have a maximum size of 900 bytes for the indexes according to this KB article.

      There are customers receiving warning messages of indexes that surpass the total size of 900 bytes:

      Warning! The maximum key length is 900 bytes. The index 'u2u_relation_unique' has maximum length of 1530 bytes. For some combination of large values, the insert/update operation will fail.
Warning! The maximum key length is 900 bytes. The index 'u2c_relation_unique' has maximum length of 1029 bytes. For some combination of large values, the insert/update operation will fail.
Warning! The maximum key length is 900 bytes. The index 'c_si_ct_pv_cs_cd_idx' has maximum length of 1046 bytes. For some combination of large values, the insert/update operation will fail.
Warning! The maximum key length is 900 bytes. The index 'idx_group_attr_dir_name_lval' has maximum length of 1029 bytes. For some combination of large values, the insert/update operation will fail.
Warning! The maximum key length is 900 bytes. The index 'idx_user_attr_dir_name_lval' has maximum length of 1029 bytes. For some combination of large values, the insert/update operation will fail.
Warning! The maximum key length is 900 bytes. The index 'sp_comp_idx' has maximum length of 1020 bytes. For some combination of large values, the insert/update operation will fail.

      Checking the first index for example (u2u_relation_unique), we can see the index is composed of 3 columns Nvarchar 255 types, each one size being 510 bytes. The sum of all 3 is about 1530 bytes which exceeds the 900 bytes maximum recommended value.

      If the insertion/update surpasses the 900 bytes (rare but could happen) it will fail.

      Possible solution

      We should decrease the column size as such the total sum of columns will become smaller than 900 bytes total.

            Assignee:
            Unassigned
            Reporter:
            Rodrigo Girardi Adami
            Votes:
            3 Vote for this issue
            Watchers:
            6 Start watching this issue

              Created:
              Updated:
              Resolved: