March 31, 2017 at 4:27 pm
help!
I have moved the filegroup to another table, dropped table, how do I remove the filegroup from the partition function and scheme? I think I need to do a merge file command. Oh ya, I deleted the actual file because I'm stupid and shouldn't have. (((yelling really loud at myself))) thank fully I did this in a dev environment, UGH, help me please!
I did a
DBCC SHRINKFILE (Filename, EMPTYFILE);
ALTER
DATABASE DB
REMOVE FILEGROUP Filename
and it won't drop
MCSE SQL Server 2012\2014\2016
March 31, 2017 at 7:58 pm
Yes... you need a MERGE of partitions before you can drop a filegroup that's involved in the partitioning.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2017 at 10:15 am
do you have the query to merge? I have read many blogs but it's never straight forward and can be over whelming.
MCSE SQL Server 2012\2014\2016
April 1, 2017 at 11:01 am
alter partition function blah merge range blah (this is the range that was associated to the file you dropped).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 1, 2017 at 11:22 am
that's were I am confused, were do I get the range? the queries I see are
alter partition function [function name]() merge range (100,200,300)
were do they get this range? the sys.partition tables?
select
* from sys.partition_functions
select * from sys.partition_parameters
I know after this completes I can run this;
ALTER
DATABASE database name
REMOVE FILEGROUP filegroup name
I have done allot of research on this and it's the merge that got me.
MCSE SQL Server 2012\2014\2016
April 1, 2017 at 1:46 pm
You can use this to help find your range values. You will only be able to merge one range at a time.
SELECT pf.name AS pf_name ,
ps.name AS partition_scheme_name ,
p.partition_number ,
ds.name AS partition_filegroup ,
OBJECT_NAME(si.object_id) AS object_name ,
rv.value AS range_value ,
SUM(CASE WHEN si.index_id IN ( 1, 0 ) THEN p.rows
ELSE 0
END) AS num_rows
FROM sys.destination_data_spaces AS dds
JOIN sys.data_spaces AS ds ON dds.data_space_id = ds.data_space_id
JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_id
JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
LEFT JOIN sys.partition_range_values AS rv ON pf.function_id = rv.function_id
AND dds.destination_id = CASE pf.boundary_value_on_right
WHEN 0 THEN rv.boundary_id
ELSE rv.boundary_id + 1
END
LEFT JOIN sys.indexes AS si ON dds.partition_scheme_id = si.data_space_id
LEFT JOIN sys.partitions AS p ON si.object_id = p.object_id
AND si.index_id = p.index_id
AND dds.destination_id = p.partition_number
LEFT JOIN sys.dm_db_partition_stats AS dbps ON p.object_id = dbps.object_id
AND p.partition_id = dbps.partition_id
GROUP BY ds.name ,
p.partition_number ,
pf.name ,
pf.type_desc ,
pf.fanout ,
pf.boundary_value_on_right ,
ps.name ,
si.object_id ,
rv.value;
GO
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 1, 2017 at 5:41 pm
the num_row says 0, I'll restore the database again and start over, I suspect I have to write the row number before I switch it to the temp table.
MCSE SQL Server 2012\2014\2016
April 1, 2017 at 5:43 pm
lkennedy76 - Saturday, April 1, 2017 5:41 PMthe num_row says 0, I'll restore the database again and start over, I suspect I have to write the row number before I switch it to the temp table.
can you post your results?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 1, 2017 at 6:02 pm
I've already started the restore but the two files that I moved had 0 in the num_row column and the other files that I hadn't moved had large numbers that were in order. I will post the results after the restore, it'll be tomorrow. I'm doing all this on a non-production server so I can screw it up again. First timer on partitions but I am learning!
MCSE SQL Server 2012\2014\2016
April 2, 2017 at 5:05 pm
With the help of SQLRNNR I was able to create steps to remove partitions. Below are the steps I took and it worked. I hope this helps someone like me who was handed this never having to do this before, getting DW knowledge, thank you SQLRNNR!!!
/**take files out of read only mode, no one can be connected**/
/**Find partition # with this query below, it will always drop back to one if starting with one, usually oldest file, Find the partition you want to delete and copy date, you will need this in the merge**/
SELECT pf.name AS pf_name ,
ps.name AS partition_scheme_name ,
p.partition_number ,
ds.name AS partition_filegroup ,
OBJECT_NAME(si.object_id) AS object_name ,
rv.value AS range_value ,
SUM(CASE WHEN si.index_id IN ( 1, 0 ) THEN p.rows
-- ELSE 0
END) AS num_rows
FROM sys.destination_data_spaces AS dds
JOIN sys.data_spaces AS ds ON dds.data_space_id = ds.data_space_id
JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_id
JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
LEFT JOIN sys.partition_range_values AS rv ON pf.function_id = rv.function_id
AND dds.destination_id = CASE pf.boundary_value_on_right
WHEN 0 THEN rv.boundary_id
ELSE rv.boundary_id + 1
END
LEFT JOIN sys.indexes AS si ON dds.partition_scheme_id = si.data_space_id
LEFT JOIN sys.partitions AS p ON si.object_id = p.object_id
AND si.index_id = p.index_id
AND dds.destination_id = p.partition_number
LEFT JOIN sys.dm_db_partition_stats AS dbps ON p.object_id = dbps.object_id
AND p.partition_id = dbps.partition_id
GROUP BY ds.name ,
p.partition_number ,
pf.name ,
pf.type_desc ,
pf.fanout ,
pf.boundary_value_on_right ,
ps.name ,
si.object_id ,
rv.value;
GO
/**Create holding table, has to be just like Orginal Table table with Index**/
USE [DatabaseName]
GO
/*Object: Table [dbo].[OrigTableToMovePartitionFileFrom] Script Date: 4/2/2017 9:52:09 AM ******/ scripted Orignal table that partitions are on*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblNewTempTableToMovePartitions](
[Col1Date] [int] NOT NULL,
[Col2ID] [date] NOT NULL,
[Col3ID] [int] NOT NULL,
[Col4] [int] NULL,
[Col5] [int] NULL,
CONSTRAINT [PK_tblNewTempTableToMovePartitions] PRIMARY KEY CLUSTERED
(
[Col1Date] ASC,
[Col2ID] ASC,
[Col3ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) on [INVENTORY_FILE01]--<<<<--has to be filegroup you are deleting
)
GO
/***************************turn compression on table************************************/
ALTER TABLE [tblNewTempTableToMovePartitions]
REBUILD WITH (DATA_COMPRESSION = PAGE);
/****************************Run to move\SWITCH partition******************************/
ALTER TABLE dbo.OrigTableToMovePartitionFileFrom
SWITCH PARTITION 1 TO [tblNewTempTableToMovePartitions];
PRINT 'Switched from partitioned table to non-partitioned table';
/*************************Alter Function and Run Date Merge**********************************************/
alter partition function [PartitionFuntionName]() merge range ('Date from Partition # File')
/**********************************Drop table******************************************/
Drop table [tblNewTempTableToMovePartitions]
/*******************Drop\Remove filegroup from database properties*****************/
MCSE SQL Server 2012\2014\2016
April 3, 2017 at 12:33 pm
Well done. Congrats.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply