sql server - Cannot delete "system" partition schemes created by FullText catalogs -

so we've got bunch of apps running on our sql servers, , today realised number of them had bunch of partition schemes/functions didn't create.

the partition schemes , functions called ifts_comp_fragment_data_space_{hash} , ifts_comp_fragment_partition_function_{hash} respectively.

digging deeper, realised marked system entries (is_system set 1 in sys.partition_schemes) means can't delete them.

after research found out sql server create them partition fulltext catalogs if become large, or see here. problem - deleted catalogs, , these left abandoned, no way of clearing them out.

i wouldn't worry much, except need delete them, since i'm trying export our db .bacpac file, , crashes complaining db contains partition schemes/functions , they're not supported.

is there way of forcing sql server drop objects, or other alternative do?

you can change is_system flag 1 0 , drop partition scheme other. this:

first allow updates on server:

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

1. shutdown sql server
2. start in single user mode running "c:\program files\microsoft sql server\mssql11.mssqlserver\mssql\binn\sqlservr.exe -m" console elevated privs.
3. login server using sql server dac http://technet.microsoft.com/en-us/library/ms178068(v=sql.105).aspx
4. if sp_helptext on sys.partition_schemes view, you'll see is_system column based on status flag in sys.sysclsobjs table. "sysconv(bit, o.status & 0x4) is_system,"

5. so change flag, have @ current value of status , unmark 4 bit. value 4 updated 0.

   update sys.sysclsobjs set status = 0 name = 'ifts_comp_fragment_data_space_033d368c'

now can shutdown single user mode sql server process closing console window , start sql server windows service. login , drop partition scheme.

finally, set 'allow updates' setting 0.

this might need planned downtime production server.

disclaimer isn't microsoft supported way of doing this, may want test on non-prod servers before diving in.