question

sakuraime avatar image
0 Votes"
sakuraime asked ErlandSommarskog commented

SQL Server 2019 elevate_online and elevate_resumable

Hi there is new database option elevate_online and elevate_resumable
117070-image.png




May I know what kind off DDL (Rebuild index ) is not support online ? Actually quite confuse about this option , because rebuild index can choose online or offline.
Introducing these two option means, when I rebuild index online, some index would failed on it ?

sql-server-general
image.png (251.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered ErlandSommarskog commented

Hi @sakuraime,

ELEVATE_ONLINE and ELEVATE_RESUMABLE does not apply to XML index operations.

Both ELEVATE_ONLINE and ELEVATE_RESUMABLE only apply to DDL statements that support the online and resumable syntax respectively. For example, if you attempt to create an XML index with ELEVATE_ONLINE=FAIL_UNSUPORTED, the operation will run offline since XML indexes don't support the ONLINE= syntax. The options only effect DDL statements that are submitted without specifying an ONLINE or RESUMABLE option. For example, by submitting a statement with ONLINE=OFF or RESUMABLE=OFF, the user can override a FAIL_UNSUPPORTED setting and run a statement offline and/or non-resumably.

Refer to MS document Guidelines for online index operations.


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

which means that only apply to xml index ? thanks

0 Votes 0 ·

XML is one example. I don't have the time to test, but a partition split that requires data move should also fail with ELEVATE_ONLINE = FAIL_UNSPOORTED, since it cannot be performed online as far as I know.

0 Votes 0 ·