SQL SERVER – Disabling 15000 Partitions (15k)

I am really having fun working with customers who are pushing the learning barrier in my new consulting role. The more customers I meet; merrier I am because I am getting interesting challenges that push me from the normal comfort zone I am in. Learning is never stopped, but the learning can never be facilitated if we are not in that zone to learn. This customer had a requirement of upgrading to a newer release and in the process was looking at disabling the 15k partitions he had enabled on the DB because this was out-of-box supported in the latest versions. I thought this was important to bring to life am I talking here. In this blog we will learn about Disabling 15000 Partitions.

The stored procedure sp_db_increased_partitions was introduced in SQL Server 2008 SP2 and SQL Server 2008 R2 versions. This procedure takes the first parameter as the database name and the second parameter as true or false. The following query will enable 15K partition support for database myDB:

EXEC sp_db_increased_partitions @dbname = N'myDB', @increased_partitions = true

Prior to the 15K partition improvement, you could only create a maximum of 1000 partitions. If you attempted to create more than 1000 partitions, you would receive the following error message:

With the 15K partition feature, you can create a maximum of 15,000 partitions. That is what the name suggests. There are a couple of scenarios where a user may wish to disable 15K partition based on my understanding:

• Since some versions of SQL Server 2008 or SQL Server 2008 R2 do not support 15K partition support, a customer may wish to restore database among different instances with different versions.
• Another scenario is that a customer uncovers some performance problems related to the 15K partition feature and this performance degradation becomes unacceptable.

In this scenario, I had followed a simple 5 step process that I felt is worth sharing with you over this blog. The following steps disable the 15K partition feature on a database:

1. Backup the database: Backup the database as a precaution step. I always like to play safe when it involved customer databases.
2. Reduce the number of partitions: You need to merge your existing partitions to make sure it has 1000 or fewer partitions. Books online documentation on “ALTER PARTITION FUNCTION” shows how do merge partitions.
3. Set the database to Simple Recovery Model: Before you can disable 15K partitions, you must set the database to Simple Recovery Model. Otherwise, you will receive the following error message when you try to disable 15K partition:

Msg 657, Level 16, State 8, Procedure sp_db_increased_partitions, Line 30

Could not disable support for increased partitions in database ‘TestDatabase_LargePartition’ because it is not in SIMPLE recovery mode. Change the recovery model to SIMPLE and try again.

4. Run the stored procedure: Now you can run the following stored procedure with 2^nd parameter set to false:

   sp_db_increased_partitions @dbname = N'TestDatabase_LargePartition', @increased_partitions = false

5. Resetting the original recovery model and start the new backup chain: You should immediately set the database recovery model to the original (such as full recovery model). Take a full database backup right away and start new log backups.

I thought personally there is nothing special about this use case, but there was an interesting learning for me when working with SQL Server 2008 versions after a long time. I must sincerely thank you folks for pushing me cross the line to keep exploring more and learning more. This is the only way I think I can sustain adding more content onto this blog for reference.

Reference: Pinal Dave (https://blog.sqlauthority.com)