December 30, 2013 at 5:58 am
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 ran the above code to find my partitions.
delete from sys.objects
where name = 'ifts_comp_fragment_544538265_2260';
I ran the above code to delete the partitions and received the following error.
Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.
sp_configure 'allow updates',0
go
reconfigure
go
I then ran the above code to allow updates, but still get the following error message.
Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.
Package:
-----------------------------------------------------------
-----------------------------------------------------------
KB Article Number(s): 2406666, 2661644, 2671318, 2714634, 2723979, 2728419, 2728534, 2731068, 2733630, 2733673, 2737499, 2737580, 2738197, 2739940, 2740507, 2752511, 2754444, 2754939, 2755079, 2756097, 2758569, 2758628, 2758687, 2762557, 2762559, 2762593
Language: All (Global)
Platform: x64
NOTE Make sure that you include all the text between "(" and ")" when you visit this hotfix location.
Found the above Hotfix.
Now i get a 'you are at a higher 2012 data tier app-framework' error.
Any fix to this?
Thanks
December 30, 2013 at 6:43 am
Updates to the system tables are not allowed.
What are you trying to do? If you want to remove partitions you should be using ALTER TABLE and merge the partitions or rebuild the table/clustered index onto a filegroup to remove partitioning entirely. If you want to remove a table, DROP TABLE.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 30, 2013 at 8:32 am
I partitioned a table some time ago to see if i could get any performance gains.
Since i did not, i deleted the table from the DB and the partitions from my hard drive, assuming that would do the job.....WRONG !
When i backup the DB on my server, which is running SQL Server 2012 Enterprise x64, then try to restore the backup on my laptop, for testing, my laptop uses SQL Server 2012 BI Edition, which does not support table partitioning, so the restore fails.
I want to remove the table partitioning from the server, so i can restore the DB on my laptop.
Thanks
December 30, 2013 at 8:54 am
isuckatsql (12/30/2013)
I partitioned a table some time ago to see if i could get any performance gains.Since i did not, i deleted the table from the DB and the partitions from my hard drive, assuming that would do the job.....WRONG !
When i backup the DB on my server, which is running SQL Server 2012 Enterprise x64, then try to restore the backup on my laptop, for testing, my laptop uses SQL Server 2012 BI Edition, which does not support table partitioning, so the restore fails.
I want to remove the table partitioning from the server, so i can restore the DB on my laptop.
Thanks
Have you also removed the partition functions and partition schemes from the database? Even if these are not used, if they exist it will prevent an Enterprise Edition database from being restored on a lower edition server.
December 30, 2013 at 9:08 am
isuckatsql (12/30/2013)
Since i did not, i deleted the table from the DB and the partitions from my hard drive, assuming that would do the job.....WRONG !
That will indeed do the job, though I'm concerned about how you deleted the partitions from the hard drive, since partitions are stored (like tables) in filegroups and you delete filegroups through SQL Server with the ALTER DATABASE statement. You don't delete the files from the drive unless you want a badly damaged database as a result
When i backup the DB on my server, which is running SQL Server 2012 Enterprise x64, then try to restore the backup on my laptop, for testing, my laptop uses SQL Server 2012 BI Edition, which does not support table partitioning, so the restore fails.
Check to make sure you have no other partitioned tables. Delete all partition schemes, delete all partition functions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 30, 2013 at 3:14 pm
SELECT t.name AS TableName, ps.name AS PartitionScheme,
ps.data_space_id, pf.name AS PartitionFunction, pf.function_id
FROM sys.TABLES t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
I ran the above query and get no partition functions or schemes.
Somehow i need to remove the objects, but don't know how.
Thanks
December 30, 2013 at 3:28 pm
That query just shows that you have no tables or indexes created on any partition schemes, not that you have no partition schemes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 30, 2013 at 7:44 pm
We had a database on an Enterprise version of SQL Server 2005. They had tested partitioning and dropped the partitions. While attempting to move that database to a new server running Standard Edition, it failed. Ran a similar query as you and it showed no partitioned tables or indexes. Digging deeper I found the partition schemes and functions were still in the databases. Once those were deleted and a new backup was taken we were able to restore the database to a server running Standard Edition.
December 31, 2013 at 3:43 am
To delete a partition scheme
Click the plus sign to expand the database where you want to delete the partition scheme.
Click the plus sign to expand the Storage folder.
Click the plus sign to expand the Partition Schemes folder.
Right-click the partition scheme you want to delete and select Delete.
In the Delete Object dialog box, ensure that the correct partition scheme is selected, and then click OK.
I followed the above instructions, but it will still not allow me to delete or drop either the partition schemes or partition functions.
Msg 15151, Level 16, State 1, Line 3
Cannot drop the partition function 'ifts_comp_fragment_partition_function_159848CA', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 3
Cannot drop the partition scheme 'ifts_comp_fragment_data_space_159848CA', because it does not exist or you do not have permission.
I get the same type of message with delete!
I am logged in under an 'sa' account and have full DB access.
Any ideas on how to get rid of this stuff ?
Thanks
December 31, 2013 at 3:49 am
ifts? You have full text enabled (and possibly set up with partitioning?) If so, drop your full text indexes, you can always rebuild them later if you need them
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 31, 2013 at 4:49 am
SELECT
t.name AS TableName,
c.name AS FTCatalogName ,
i.name AS UniqueIdxName,
cl.name AS ColumnName
FROM
sys.tables t
INNER JOIN
sys.fulltext_indexes fi
ON
t.[object_id] = fi.[object_id]
INNER JOIN
sys.fulltext_index_columns ic
ON
ic.[object_id] = t.[object_id]
INNER JOIN
sys.columns cl
ON
ic.column_id = cl.column_id
AND ic.[object_id] = cl.[object_id]
INNER JOIN
sys.fulltext_catalogs c
ON
fi.fulltext_catalog_id = c.fulltext_catalog_id
INNER JOIN
sys.indexes i
ON
fi.unique_index_id = i.index_id
AND fi.[object_id] = i.[object_id];
I ran the above query to identify the FTI catalogs and deleted them.
I tried again to delete the partition schemes and partition functions, without success.
Thanks
December 31, 2013 at 5:21 am
And the error you got was....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 31, 2013 at 8:24 am
The same as last time.
Msg 15151, Level 16, State 1, Line 3
Cannot drop the partition function 'ifts_comp_fragment_partition_function_159848CA', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 3
Cannot drop the partition scheme 'ifts_comp_fragment_data_space_159848CA', because it does not exist or you do not have permission.
Thanks
December 31, 2013 at 8:38 am
I added an image from the DB.
December 31, 2013 at 10:33 am
Just curious but in which order are you trying to drop them? Sounds like you are trying to drop the functions first then the schemes. Have you tried dropping the schemes first then the functions? Just curious as you need the functions to create the schemes only makes sense that you might need to drop the schemes before the functions.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply