Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Delete Partitions Issue - HotFix not working. Expand / Collapse
Author
Message
Posted Monday, December 30, 2013 5:58 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:58 AM
Points: 490, Visits: 976
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
Location: (http://hotfixv4.microsoft.com/SQL%20Server%202012/nosp/2012_RTM_DAC_CU4_2758687_11_0_2383_x64/11.0.2383.0/free/454240_intl_x64_zip.exe)
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




Post #1526471
Posted Monday, December 30, 2013 6:43 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:47 AM
Points: 41,525, Visits: 34,442
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1526481
Posted Monday, December 30, 2013 8:32 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:58 AM
Points: 490, Visits: 976
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
Post #1526513
Posted Monday, December 30, 2013 8:54 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:41 PM
Points: 22,491, Visits: 30,185
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1526522
Posted Monday, December 30, 2013 9:08 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:47 AM
Points: 41,525, Visits: 34,442
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1526527
Posted Monday, December 30, 2013 3:14 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:58 AM
Points: 490, Visits: 976
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
Post #1526627
Posted Monday, December 30, 2013 3:28 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:47 AM
Points: 41,525, Visits: 34,442
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1526631
Posted Monday, December 30, 2013 7:44 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:41 PM
Points: 22,491, Visits: 30,185
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1526649
Posted Tuesday, December 31, 2013 3:43 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:58 AM
Points: 490, Visits: 976
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
Post #1526711
Posted Tuesday, December 31, 2013 3:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:47 AM
Points: 41,525, Visits: 34,442
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1526713
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse