Cannot delete "system" partition schemes created by FullText catalogs

Question

So we've got a bunch of apps running on our SQL servers, and today we realised that a number of them had a bunch of Partition Schemes/Functions that we didn't create.

The partition schemes and functions were called ifts_comp_fragment_data_space_{hash} and ifts_comp_fragment_partition_function_{hash} respectively.

Digging deeper, we realised that they are marked as system entries (is_system set to 1 in sys.partition_schemes) which means we can't even delete them.

After some research we found out that SQL server will create them to partition the fulltext catalogs if they become too large, or something like that see here. The problem is - we just deleted all the catalogs, and these were left abandoned, with NO way of clearing them out.

I wouldn't worry too much, except I NEED to delete them, since I'm trying to export our DB as a .bacpac file, and that crashes complaining that the DB contains partition schemes/functions and they're not supported.

Is there ANY way of forcing the SQL server to drop those objects, or any other alternative that I could do?

Answer 1

You can change that is_system flag from 1 to 0 and then drop the partition scheme like any other. To do this:

First allow updates on your server:

exec sp_configure 'allow updates', 1
go
reconfigure with override
go

1. Shutdown your SQL server
2. Start it back up in Single User mode by running "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe -m" from a console with elevated privs.
3. Login to the server using the SQL Server DAC http://technet.microsoft.com/en-us/library/ms178068(v=sql.105).aspx
4. If we do an SP_HELPTEXT on the sys.partition_schemes view, you'll see that the is_system column is based on a status flag in the sys.sysclsobjs table. "sysconv(bit, o.status & 0x4) AS is_system,"

5. So to change the flag, we have to look at what the current value of the status is and unmark the 4 bit. My value was 4 so I updated it to 0.

   update sys.sysclsobjs set status = 0 where name = 
     'ifts_comp_fragment_data_space_033D368C'
   

Now you can shutdown the single user mode SQL Server process by just closing your console window and start your sql server windows service. Then just login as you normally would and drop the partition scheme.

Finally, set your 'allow updates' setting back to 0.

This might need to be planned downtime for a production server.

DISCLAIMER This probably isn't a Microsoft supported way of doing this, you may want to test on some non-prod servers before diving in.