As a SQL Server consultant, I am always excited to work with customers who challenge me and push me out of my comfort zone. Recently, I had the opportunity to assist a customer who wanted to upgrade to a newer release of SQL Server and disable the 15k partitions they had enabled on their database. This requirement intrigued me and I believe it’s worth discussing in this blog post.

In SQL Server 2008 SP2 and SQL Server 2008 R2 versions, a stored procedure called sp_db_increased_partitions was introduced. This procedure allows you to enable or disable 15k partition support for a database. To enable 15k partition support, you can execute the following query:

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

Prior to the introduction of the 15k partition feature, SQL Server only supported a maximum of 1000 partitions. If you tried to create more than 1000 partitions, you would receive an error message. However, with the 15k partition feature, you can now create up to 15,000 partitions.

There are a few scenarios where you might want to disable 15k partitions:

1. If you need to restore a database among different instances with different versions of SQL Server that do not support 15k partitioning.
2. If you encounter performance problems related to the 15k partition feature and the performance degradation becomes unacceptable.

If you find yourself in one of these scenarios, you can follow these steps to disable the 15k partition feature on your database:

1. Backup the database: It’s always a good practice to backup the database before making any changes.
2. Reduce the number of partitions: Merge your existing partitions to ensure that you have 1000 or fewer partitions. The documentation for the ALTER PARTITION FUNCTION command provides guidance on how to merge partitions.
3. Set the database to Simple Recovery Model: Before you can disable 15k partitions, you must set the database to the Simple Recovery Model. Failure to do so will result in an error message.
4. Run the stored procedure: Execute the sp_db_increased_partitions stored procedure with the second parameter set to false to disable 15k partitions.
5. Reset the original recovery model and start a new backup chain: Set the database recovery model back to its original setting (e.g., full recovery model), take a full database backup, and start a new log backup chain.

By following these steps, you can successfully disable the 15k partition feature on your database.

Working on this use case reminded me of the importance of continuously learning and exploring new features and functionalities of SQL Server. I am grateful to my customers for pushing me to expand my knowledge and share it with others through this blog.

Thank you for reading and stay tuned for more SQL Server tips and tricks!