How to remove the SQL Server Partitioning implemented on a table


We need to remove the partition that has been implemented into a table in 2005. Could anyone help on the same. While trying to remove the Partition Scheme, I am getting the following error message: Msg 7717, Level 16, State 1, Line 1 The partition scheme "PS_ActivityYear" is currently being used to partition one or more tables.

Could anyone explain how to remove the partition?

Thanks in advance. Anandan

more ▼

asked Dec 09, 2011 at 12:11 AM in Default

avatar image

21 2 2 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

You have to rebuild all of your indexes that are using the partition schema, and specify a filegroup instead of the partition schema.

 CREATE INDEX indexname ON tablename (columnname_1, colummnname_2, columnname_n)
 WITH(DROP_EXISTING=ON) ON filegroupname -- specify the filegroup instead of the partition schema

If you need to find all the indexes that are using the partition schema you can use the following code:

 SELECT O.Name as TableName, I.Name as IndexName, I.Type, I.type_desc as IndexType, ps.name as PartitionSchema
 FROM sys.objects O
 INNER JOIN sys.partitions p on P.object_id = O.object_id
 INNER JOIN sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id
 INNER JOIN sys.data_spaces ds on i.data_space_id = ds.data_space_id
 INNER JOIN sys.partition_schemes ps on ds.data_space_id = ps.data_space_id
 WHERE p.partition_number = 1

I didn't have my own script with me so this is actually copied from : http://sqlfascination.com/2010/01/12/dynamic-partitioning-what-objects-are-using-a-partition-schema-sql-tuesday-002/

All credits to Andrew Hogg, read his post, it may give you some ideas on how all the partition objects relate to each other.

more ▼

answered Dec 09, 2011 at 12:12 AM

avatar image

Håkan Winther
16.6k 37 45 58

  • and tables too!

Dec 09, 2011 at 12:17 AM Kev Riley ♦♦

Hi Hakan,

Thanks for the wonderful guidance. I have identified all the indexes created on partition schema and removed those indexes. Now, I am able to drop the partition schema as well as partition function.

However, you have mentioned to rebuild all of your indexes that are using the partition schema, and specify a filegroup instead of the partition schema.

As, this table is having several around 12 Filegroups, Am I need to create the index upon all the available filegroups ?

Jul 09, 2012 at 03:30 PM skan2dan1

By rebuilding the indexes without a partition schema you will be forced to migrate the data to one filegroup - that is the reason for having partitioning in the first place.

You will issue the CREATE INDEX WITH (DROP_EXISTING=ON) command and supply one filegroup for the index to be created in.

Jul 10, 2012 at 08:58 AM WilliamD
(comments are locked)
10|1200 characters needed characters left

A simple way to fix the index is to select drop and recreate and then add the "ON [PRIMARY]" to the end of the script. This removes the index from the partitions.

Similarly indexes created by the data engine tuning wizard and there were partitions created by the DTA preventing deletion by hypothetical indexes. After dropping the hypotheticals, remove the hypothetical partitions as well (after merging them back to the primary as in ON([PRIMARY],[PRIMARY], etc)

TO FIND THEM try the code below [code} Sp_help ‘tablename’ --DROP INDEX ON for all rogue indexes. DROP PARTITION SCHEME [] DROP PARTITION FUNCTION [] {code}

more ▼

answered Sep 01 at 04:12 PM

avatar image

10 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 09, 2011 at 12:11 AM

Seen: 10366 times

Last Updated: Sep 01 at 04:12 PM

Copyright 2017 Redgate Software. Privacy Policy