I am unable to drop partitioned index due to partition scheme and partition function existence.

Table has a Primary Key, a Clustered Index and non clustered indexes.

I am running the following query:

drop partition scheme scheme_name

I am getting this error:

Msg 7717, Level 16, State 1, Line 1
The partition scheme "scheme_name" is currently being used to partition one or more tables.

I tried recreating indexes by dropping them but I still get the same error.

  • 1
    Please include the schema of the table in question. – Daniel Hutmacher Feb 18 '16 at 13:00

After performing one of many partition Switch operations in a Pruning process I had problems with 1 table where I could not remove the Partition Scheme at the end of the process.

Turns out, when re-building the Clustered Index back onto a single FileGroup you MUST explicitly refer to a filegroup in the statement and not assume it will go to the Default filegroup. If FileGroup is Empty the existing Partition Scheme is assumed.

-- Re-Create the Clustered Index with no Partition

ON MyTable([MyID]) 
ON MustReferToMyFileGroupHere;

Hope this helps someone. Applies to at least SQL 2012

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.