Can't Remove File and Filegroup from Database for Partitioning a Table

Question

Can't Remove File and Filegroup from Database for Partitioning a Table

quinn.jay

SSCertifiable

Points: 5505
More actions
July 25, 2018 at 3:49 pm

#358858

Hello, I had posted about my code used to create data partitioned tables, and got past the stumper. Now, as I go to load the large volume of data, I get out of space errors on the partitions. I make adjustments to the File and Filegroup through SSMS, and get various error messages like I can't remove the File as is not there, I can't remove the FileGroup as its not empty, and I can't save the changes to the DB as FileGroup is not empty yet it's not there. I tried to shrink the DB file, but that didn't help. I've tried to remove and drop everything as to start over. I'm stuck in redoing everything till I can clear the roadblock I created.
Below is the linear flow of the code I want to re-execute after I find a way to clear out the previous attempt.
As I learned the first time, after I ran something very similar to the below, and started to load the code, I saw in SSMS on the DB props, that settings like 'unlimited' and set MB sizes had to to be changed, as they were set and not flexible. I Don't know the code/command below to change it, so I plan to do it in SSMS after I run this, unless someone knows and I can alter it here before running.
System: Windows 2012 R2 Standard, SQL Server 2016 SP1 EE

USE Database_BI;
GO
-- Adds seven new filegroups to the Database_BI database (2015,2016,2017,2018,2019,2020,2021)
--ALTER DATABASE [Database_BI] REMOVE FILE sw2020fg;
--ALTER DATABASE [Database_BI] REMOVE FILEGROUP [sw2020fg] ;
--USE [Database_BI] GO
--DBCC SHRINKFILE (N'sw2020fg', EMPTYFILE)
--GO
ALTER DATABASE Database_BI
ADD FILEGROUP sw2015fg;
GO
ALTER DATABASE Database_BI
ADD FILEGROUP sw2016fg;
GO
ALTER DATABASE Database_BI
ADD FILEGROUP sw2017fg;
GO
ALTER DATABASE Database_BI
ADD FILEGROUP sw2018fg;
GO
ALTER DATABASE Database_BI
ADD FILEGROUP sw2019fg;
GO
ALTER DATABASE Database_BI
ADD FILEGROUP sw2020fg;
go
ALTER DATABASE Database_BI
ADD FILEGROUP sw2021fg;
-- Adds one file for each filegroup. for 7 filegroups
--alter database Database_BI remove file sw2015dat1;
ALTER DATABASE Database_BI
ADD FILE
(
NAME = sw2015dat1,
FILENAME = 'G:\QD_MBISXX01_USER02\QD_MBISXX01\sw2015dat1.ndf',
SIZE = 10MB,
MAXSIZE = 250MB,
FILEGROWTH = 10MB
)
TO FILEGROUP sw2015fg;
GO
ALTER DATABASE Database_BI
ADD FILE
(
NAME = sw2016dat2,
FILENAME = 'G:\QD_MBISXX01_USER02\QD_MBISXX01\sw2016dat2.ndf',
SIZE = 10MB,
MAXSIZE = 250MB,
FILEGROWTH = 10MB
)
TO FILEGROUP sw2016fg;
GO
ALTER DATABASE Database_BI
ADD FILE
(
NAME = sw2017dat3,
FILENAME = 'G:\QD_MBISXX01_USER02\QD_MBISXX01\sw2017dat3.ndf',
SIZE = 10MB,
MAXSIZE = 250MB,
FILEGROWTH = 10MB
)
TO FILEGROUP sw2017fg;
GO
ALTER DATABASE Database_BI
ADD FILE
(
NAME = sw2018dat4,
FILENAME = 'G:\QD_MBISXX01_USER02\QD_MBISXX01\sw2018dat4.ndf',
SIZE = 10MB,
MAXSIZE = 250MB,
FILEGROWTH = 10MB
)
TO FILEGROUP sw2018fg;
GO
ALTER DATABASE Database_BI
ADD FILE
(
NAME = sw2019dat5,
FILENAME = 'G:\QD_MBISXX01_USER02\QD_MBISXX01\sw2019dat5.ndf',
SIZE = 10MB,
MAXSIZE = 250MB,
FILEGROWTH = 10MB
)
TO FILEGROUP sw2019fg;
GO
ALTER DATABASE Database_BI
ADD FILE
(
NAME = sw2020dat6,
FILENAME = 'G:\QD_MBISXX01_USER02\QD_MBISXX01\sw2020dat6.ndf',
SIZE = 10MB,
MAXSIZE = 250MB,
FILEGROWTH = 10MB
)
TO FILEGROUP sw2020fg;
GO
ALTER DATABASE Database_BI
ADD FILE
(
NAME = sw2021dat7,
FILENAME = 'G:\QD_MBISXX01_USER02\QD_MBISXX01\sw2020dat7.ndf',
SIZE = 10MB,
MAXSIZE = 250MB,
FILEGROWTH = 10MB
)
TO FILEGROUP sw2021fg;
GO
-- F Creates a partition function called myDateRangePF1 that will partition a table into six partitions
/* NOTE: must have more or less than the same number of filegroups to files to get past creation error */
--DROP PARTITION FUNCTION myDateRangePF1 ;
CREATE PARTITION FUNCTION myDateRangePF1 (int) /* interger date used */
AS RANGE LEFT FOR VALUES (2015, 2016, 2017, 2018, 2019, 2020) ; -- note 2021 is not here, as in minus one so 6
GO
-- S Creates a partition scheme called myDateRangePS1 that applies myDateRangePF1 to the 7 filegroups created above
CREATE PARTITION SCHEME myDateRangePS1 --S
AS PARTITION myDateRangePF1 --F
TO (sw2015fg, sw2016fg, sw2017fg, sw2018fg, sw2019fg, sw2020fg, sw2021fg) ;
GO
-- Create partitioned tables called BI_Sub_Actvt/Rev/Usg that uses myDateRangePS1 to partition actvt_ym
/* NOTE: After table creation, add clustered index on actvt_ym and actvt_dt on ec tbl, then compress clustered indexs, compress tables */
-- Tables for new cube
CREATE TABLE [dbo].[BI_table1](
[actvt_ym] [int] null,
[actvt_dt] [date] NULL,
...
) ON myDateRangePS1 (actvt_ym)
GO
CREATE TABLE [dbo].[BI_table2](
[actvt_ym] [int] NULL,
[actvt_dt] [date] NULL,
...
) ON myDateRangePS1 (actvt_ym)
GO
CREATE TABLE [dbo].[BI_table3](
[actvt_ym] [int] NULL,
[actvt_dt] [date] NULL,
...
) ON myDateRangePS1 (actvt_ym)
GO
-- EOL

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

quinn.jay SSCertifiable Points: 5505 More actions · Answer 1

quinn.jay - Wednesday, July 25, 2018 3:49 PM
Hello, I had posted about my code used to create data partitioned tables, and got past the stumper. Now, as I go to load the large volume of data, I get out of space errors on the partitions. I make adjustments to the File and Filegroup through SSMS, and get various error messages like I can't remove the File as is not there, I can't remove the FileGroup as its not empty, and I can't save the changes to the DB as FileGroup is not empty yet it's not there. I tried to shrink the DB file, but that didn't help. I've tried to remove and drop everything as to start over. I'm stuck in redoing everything till I can clear the roadblock I created.
Below is the linear flow of the code I want to re-execute after I find a way to clear out the previous attempt.
As I learned the first time, after I ran something very similar to the below, and started to load the code, I saw in SSMS on the DB props, that settings like 'unlimited' and set MB sizes had to to be changed, as they were set and not flexible. I Don't know the code/command below to change it, so I plan to do it in SSMS after I run this, unless someone knows and I can alter it here before running.
System: Windows 2012 R2 Standard, SQL Server 2016 SP1 EE

USE Database_BI;
GO
-- Adds seven new filegroups to the Database_BI database (2015,2016,2017,2018,2019,2020,2021)
--ALTER DATABASE [Database_BI] REMOVE FILE sw2020fg;
--ALTER DATABASE [Database_BI] REMOVE FILEGROUP [sw2020fg] ;
--USE [Database_BI] GO
--DBCC SHRINKFILE (N'sw2020fg', EMPTYFILE)
--GO
ALTER DATABASE Database_BI
ADD FILEGROUP sw2015fg;
GO
ALTER DATABASE Database_BI
ADD FILEGROUP sw2016fg;
GO
ALTER DATABASE Database_BI
ADD FILEGROUP sw2017fg;
GO
ALTER DATABASE Database_BI
ADD FILEGROUP sw2018fg;
GO
ALTER DATABASE Database_BI
ADD FILEGROUP sw2019fg;
GO
ALTER DATABASE Database_BI
ADD FILEGROUP sw2020fg;
go
ALTER DATABASE Database_BI
ADD FILEGROUP sw2021fg;
-- Adds one file for each filegroup. for 7 filegroups
--alter database Database_BI remove file sw2015dat1;
ALTER DATABASE Database_BI
ADD FILE
(
NAME = sw2015dat1,
FILENAME = 'G:\QD_MBISXX01_USER02\QD_MBISXX01\sw2015dat1.ndf',
SIZE = 10MB,
MAXSIZE = 250MB,
FILEGROWTH = 10MB
)
TO FILEGROUP sw2015fg;
GO
ALTER DATABASE Database_BI
ADD FILE
(
NAME = sw2016dat2,
FILENAME = 'G:\QD_MBISXX01_USER02\QD_MBISXX01\sw2016dat2.ndf',
SIZE = 10MB,
MAXSIZE = 250MB,
FILEGROWTH = 10MB
)
TO FILEGROUP sw2016fg;
GO
ALTER DATABASE Database_BI
ADD FILE
(
NAME = sw2017dat3,
FILENAME = 'G:\QD_MBISXX01_USER02\QD_MBISXX01\sw2017dat3.ndf',
SIZE = 10MB,
MAXSIZE = 250MB,
FILEGROWTH = 10MB
)
TO FILEGROUP sw2017fg;
GO
ALTER DATABASE Database_BI
ADD FILE
(
NAME = sw2018dat4,
FILENAME = 'G:\QD_MBISXX01_USER02\QD_MBISXX01\sw2018dat4.ndf',
SIZE = 10MB,
MAXSIZE = 250MB,
FILEGROWTH = 10MB
)
TO FILEGROUP sw2018fg;
GO
ALTER DATABASE Database_BI
ADD FILE
(
NAME = sw2019dat5,
FILENAME = 'G:\QD_MBISXX01_USER02\QD_MBISXX01\sw2019dat5.ndf',
SIZE = 10MB,
MAXSIZE = 250MB,
FILEGROWTH = 10MB
)
TO FILEGROUP sw2019fg;
GO
ALTER DATABASE Database_BI
ADD FILE
(
NAME = sw2020dat6,
FILENAME = 'G:\QD_MBISXX01_USER02\QD_MBISXX01\sw2020dat6.ndf',
SIZE = 10MB,
MAXSIZE = 250MB,
FILEGROWTH = 10MB
)
TO FILEGROUP sw2020fg;
GO
ALTER DATABASE Database_BI
ADD FILE
(
NAME = sw2021dat7,
FILENAME = 'G:\QD_MBISXX01_USER02\QD_MBISXX01\sw2020dat7.ndf',
SIZE = 10MB,
MAXSIZE = 250MB,
FILEGROWTH = 10MB
)
TO FILEGROUP sw2021fg;
GO
-- F Creates a partition function called myDateRangePF1 that will partition a table into six partitions
/* NOTE: must have more or less than the same number of filegroups to files to get past creation error */
--DROP PARTITION FUNCTION myDateRangePF1 ;
CREATE PARTITION FUNCTION myDateRangePF1 (int) /* interger date used */
AS RANGE LEFT FOR VALUES (2015, 2016, 2017, 2018, 2019, 2020) ; -- note 2021 is not here, as in minus one so 6
GO
-- S Creates a partition scheme called myDateRangePS1 that applies myDateRangePF1 to the 7 filegroups created above
CREATE PARTITION SCHEME myDateRangePS1 --S
AS PARTITION myDateRangePF1 --F
TO (sw2015fg, sw2016fg, sw2017fg, sw2018fg, sw2019fg, sw2020fg, sw2021fg) ;
GO
-- Create partitioned tables called BI_Sub_Actvt/Rev/Usg that uses myDateRangePS1 to partition actvt_ym
/* NOTE: After table creation, add clustered index on actvt_ym and actvt_dt on ec tbl, then compress clustered indexs, compress tables */
-- Tables for new cube
CREATE TABLE [dbo].[BI_table1](
[actvt_ym] [int] null,
[actvt_dt] [date] NULL,
...
) ON myDateRangePS1 (actvt_ym)
GO
CREATE TABLE [dbo].[BI_table2](
[actvt_ym] [int] NULL,
[actvt_dt] [date] NULL,
...
) ON myDateRangePS1 (actvt_ym)
GO
CREATE TABLE [dbo].[BI_table3](
[actvt_ym] [int] NULL,
[actvt_dt] [date] NULL,
...
) ON myDateRangePS1 (actvt_ym)
GO
-- EOL

Good news, I was able to clear the files and filegroups by dropping the tables, schema and the functions, then did a shrink on the remaining filegroups, then removed the filegroups and files. I've now cleared out everything.

So I'm now at my problem of running out of space as I was loading the tables with the above partitioning settings. I did notice that I had to change fixed sizes to unlimited with SSMS after I had run the code, I would like to do that in the T-SQL code if possible.

quinn.jay SSCertifiable Points: 5505 More actions · Answer 2

quinn.jay - Wednesday, July 25, 2018 5:48 PM
quinn.jay - Wednesday, July 25, 2018 3:49 PM
Hello, I had posted about my code used to create data partitioned tables, and got past the stumper. Now, as I go to load the large volume of data, I get out of space errors on the partitions. I make adjustments to the File and Filegroup through SSMS, and get various error messages like I can't remove the File as is not there, I can't remove the FileGroup as its not empty, and I can't save the changes to the DB as FileGroup is not empty yet it's not there. I tried to shrink the DB file, but that didn't help. I've tried to remove and drop everything as to start over. I'm stuck in redoing everything till I can clear the roadblock I created.
Below is the linear flow of the code I want to re-execute after I find a way to clear out the previous attempt.
As I learned the first time, after I ran something very similar to the below, and started to load the code, I saw in SSMS on the DB props, that settings like 'unlimited' and set MB sizes had to to be changed, as they were set and not flexible. I Don't know the code/command below to change it, so I plan to do it in SSMS after I run this, unless someone knows and I can alter it here before running.
System: Windows 2012 R2 Standard, SQL Server 2016 SP1 EE

USE Database_BI;
GO
-- Adds seven new filegroups to the Database_BI database (2015,2016,2017,2018,2019,2020,2021)
--ALTER DATABASE [Database_BI] REMOVE FILE sw2020fg;
--ALTER DATABASE [Database_BI] REMOVE FILEGROUP [sw2020fg] ;
--USE [Database_BI] GO
--DBCC SHRINKFILE (N'sw2020fg', EMPTYFILE)
--GO
ALTER DATABASE Database_BI
ADD FILEGROUP sw2015fg;
GO
ALTER DATABASE Database_BI
ADD FILEGROUP sw2016fg;
GO
ALTER DATABASE Database_BI
ADD FILEGROUP sw2017fg;
GO
ALTER DATABASE Database_BI
ADD FILEGROUP sw2018fg;
GO
ALTER DATABASE Database_BI
ADD FILEGROUP sw2019fg;
GO
ALTER DATABASE Database_BI
ADD FILEGROUP sw2020fg;
go
ALTER DATABASE Database_BI
ADD FILEGROUP sw2021fg;
-- Adds one file for each filegroup. for 7 filegroups
--alter database Database_BI remove file sw2015dat1;
ALTER DATABASE Database_BI
ADD FILE
(
NAME = sw2015dat1,
FILENAME = 'G:\QD_MBISXX01_USER02\QD_MBISXX01\sw2015dat1.ndf',
SIZE = 10MB,
MAXSIZE = 250MB,
FILEGROWTH = 10MB
)
TO FILEGROUP sw2015fg;
GO
ALTER DATABASE Database_BI
ADD FILE
(
NAME = sw2016dat2,
FILENAME = 'G:\QD_MBISXX01_USER02\QD_MBISXX01\sw2016dat2.ndf',
SIZE = 10MB,
MAXSIZE = 250MB,
FILEGROWTH = 10MB
)
TO FILEGROUP sw2016fg;
GO
ALTER DATABASE Database_BI
ADD FILE
(
NAME = sw2017dat3,
FILENAME = 'G:\QD_MBISXX01_USER02\QD_MBISXX01\sw2017dat3.ndf',
SIZE = 10MB,
MAXSIZE = 250MB,
FILEGROWTH = 10MB
)
TO FILEGROUP sw2017fg;
GO
ALTER DATABASE Database_BI
ADD FILE
(
NAME = sw2018dat4,
FILENAME = 'G:\QD_MBISXX01_USER02\QD_MBISXX01\sw2018dat4.ndf',
SIZE = 10MB,
MAXSIZE = 250MB,
FILEGROWTH = 10MB
)
TO FILEGROUP sw2018fg;
GO
ALTER DATABASE Database_BI
ADD FILE
(
NAME = sw2019dat5,
FILENAME = 'G:\QD_MBISXX01_USER02\QD_MBISXX01\sw2019dat5.ndf',
SIZE = 10MB,
MAXSIZE = 250MB,
FILEGROWTH = 10MB
)
TO FILEGROUP sw2019fg;
GO
ALTER DATABASE Database_BI
ADD FILE
(
NAME = sw2020dat6,
FILENAME = 'G:\QD_MBISXX01_USER02\QD_MBISXX01\sw2020dat6.ndf',
SIZE = 10MB,
MAXSIZE = 250MB,
FILEGROWTH = 10MB
)
TO FILEGROUP sw2020fg;
GO
ALTER DATABASE Database_BI
ADD FILE
(
NAME = sw2021dat7,
FILENAME = 'G:\QD_MBISXX01_USER02\QD_MBISXX01\sw2020dat7.ndf',
SIZE = 10MB,
MAXSIZE = 250MB,
FILEGROWTH = 10MB
)
TO FILEGROUP sw2021fg;
GO
-- F Creates a partition function called myDateRangePF1 that will partition a table into six partitions
/* NOTE: must have more or less than the same number of filegroups to files to get past creation error */
--DROP PARTITION FUNCTION myDateRangePF1 ;
CREATE PARTITION FUNCTION myDateRangePF1 (int) /* interger date used */
AS RANGE LEFT FOR VALUES (2015, 2016, 2017, 2018, 2019, 2020) ; -- note 2021 is not here, as in minus one so 6
GO
-- S Creates a partition scheme called myDateRangePS1 that applies myDateRangePF1 to the 7 filegroups created above
CREATE PARTITION SCHEME myDateRangePS1 --S
AS PARTITION myDateRangePF1 --F
TO (sw2015fg, sw2016fg, sw2017fg, sw2018fg, sw2019fg, sw2020fg, sw2021fg) ;
GO
-- Create partitioned tables called BI_Sub_Actvt/Rev/Usg that uses myDateRangePS1 to partition actvt_ym
/* NOTE: After table creation, add clustered index on actvt_ym and actvt_dt on ec tbl, then compress clustered indexs, compress tables */
-- Tables for new cube
CREATE TABLE [dbo].[BI_table1](
[actvt_ym] [int] null,
[actvt_dt] [date] NULL,
...
) ON myDateRangePS1 (actvt_ym)
GO
CREATE TABLE [dbo].[BI_table2](
[actvt_ym] [int] NULL,
[actvt_dt] [date] NULL,
...
) ON myDateRangePS1 (actvt_ym)
GO
CREATE TABLE [dbo].[BI_table3](
[actvt_ym] [int] NULL,
[actvt_dt] [date] NULL,
...
) ON myDateRangePS1 (actvt_ym)
GO
-- EOL
Good news, I was able to clear the files and filegroups by dropping the tables, schema and the functions, then did a shrink on the remaining filegroups, then removed the filegroups and files. I've now cleared out everything.
So I'm now at my problem of running out of space as I was loading the tables with the above partitioning settings. I did notice that I had to change fixed sizes to unlimited with SSMS after I had run the code, I would like to do that in the T-SQL code if possible.

I'm thinking of changing from my Int which is an Integer Date, which is YYYYMM, that is also the 1st column, the second column is DATE, and is YYYYMMDD. Thoughts? Int may be throwing all data to be loaded to the wrong partition and not the right one as it's not using a DATE datatype.

Also, what about the naming of my files, and filegroups, does this look solid or could a better numbering/pattern be had? As in, am I adding a incremented letter or number that doesnt need to be?

Thanks

quinn.jay SSCertifiable Points: 5505 More actions · Answer 3

quinn.jay - Wednesday, July 25, 2018 5:56 PM
quinn.jay - Wednesday, July 25, 2018 5:48 PM
quinn.jay - Wednesday, July 25, 2018 3:49 PM
Hello, I had posted about my code used to create data partitioned tables, and got past the stumper. Now, as I go to load the large volume of data, I get out of space errors on the partitions. I make adjustments to the File and Filegroup through SSMS, and get various error messages like I can't remove the File as is not there, I can't remove the FileGroup as its not empty, and I can't save the changes to the DB as FileGroup is not empty yet it's not there. I tried to shrink the DB file, but that didn't help. I've tried to remove and drop everything as to start over. I'm stuck in redoing everything till I can clear the roadblock I created.
Below is the linear flow of the code I want to re-execute after I find a way to clear out the previous attempt.
As I learned the first time, after I ran something very similar to the below, and started to load the code, I saw in SSMS on the DB props, that settings like 'unlimited' and set MB sizes had to to be changed, as they were set and not flexible. I Don't know the code/command below to change it, so I plan to do it in SSMS after I run this, unless someone knows and I can alter it here before running.
System: Windows 2012 R2 Standard, SQL Server 2016 SP1 EE

USE Database_BI;
GO
-- Adds seven new filegroups to the Database_BI database (2015,2016,2017,2018,2019,2020,2021)
--ALTER DATABASE [Database_BI] REMOVE FILE sw2020fg;
--ALTER DATABASE [Database_BI] REMOVE FILEGROUP [sw2020fg] ;
--USE [Database_BI] GO
--DBCC SHRINKFILE (N'sw2020fg', EMPTYFILE)
--GO
ALTER DATABASE Database_BI
ADD FILEGROUP sw2015fg;
GO
ALTER DATABASE Database_BI
ADD FILEGROUP sw2016fg;
GO
ALTER DATABASE Database_BI
ADD FILEGROUP sw2017fg;
GO
ALTER DATABASE Database_BI
ADD FILEGROUP sw2018fg;
GO
ALTER DATABASE Database_BI
ADD FILEGROUP sw2019fg;
GO
ALTER DATABASE Database_BI
ADD FILEGROUP sw2020fg;
go
ALTER DATABASE Database_BI
ADD FILEGROUP sw2021fg;
-- Adds one file for each filegroup. for 7 filegroups
--alter database Database_BI remove file sw2015dat1;
ALTER DATABASE Database_BI
ADD FILE
(
NAME = sw2015dat1,
FILENAME = 'G:\QD_MBISXX01_USER02\QD_MBISXX01\sw2015dat1.ndf',
SIZE = 10MB,
MAXSIZE = 250MB,
FILEGROWTH = 10MB
)
TO FILEGROUP sw2015fg;
GO
ALTER DATABASE Database_BI
ADD FILE
(
NAME = sw2016dat2,
FILENAME = 'G:\QD_MBISXX01_USER02\QD_MBISXX01\sw2016dat2.ndf',
SIZE = 10MB,
MAXSIZE = 250MB,
FILEGROWTH = 10MB
)
TO FILEGROUP sw2016fg;
GO
ALTER DATABASE Database_BI
ADD FILE
(
NAME = sw2017dat3,
FILENAME = 'G:\QD_MBISXX01_USER02\QD_MBISXX01\sw2017dat3.ndf',
SIZE = 10MB,
MAXSIZE = 250MB,
FILEGROWTH = 10MB
)
TO FILEGROUP sw2017fg;
GO
ALTER DATABASE Database_BI
ADD FILE
(
NAME = sw2018dat4,
FILENAME = 'G:\QD_MBISXX01_USER02\QD_MBISXX01\sw2018dat4.ndf',
SIZE = 10MB,
MAXSIZE = 250MB,
FILEGROWTH = 10MB
)
TO FILEGROUP sw2018fg;
GO
ALTER DATABASE Database_BI
ADD FILE
(
NAME = sw2019dat5,
FILENAME = 'G:\QD_MBISXX01_USER02\QD_MBISXX01\sw2019dat5.ndf',
SIZE = 10MB,
MAXSIZE = 250MB,
FILEGROWTH = 10MB
)
TO FILEGROUP sw2019fg;
GO
ALTER DATABASE Database_BI
ADD FILE
(
NAME = sw2020dat6,
FILENAME = 'G:\QD_MBISXX01_USER02\QD_MBISXX01\sw2020dat6.ndf',
SIZE = 10MB,
MAXSIZE = 250MB,
FILEGROWTH = 10MB
)
TO FILEGROUP sw2020fg;
GO
ALTER DATABASE Database_BI
ADD FILE
(
NAME = sw2021dat7,
FILENAME = 'G:\QD_MBISXX01_USER02\QD_MBISXX01\sw2020dat7.ndf',
SIZE = 10MB,
MAXSIZE = 250MB,
FILEGROWTH = 10MB
)
TO FILEGROUP sw2021fg;
GO
-- F Creates a partition function called myDateRangePF1 that will partition a table into six partitions
/* NOTE: must have more or less than the same number of filegroups to files to get past creation error */
--DROP PARTITION FUNCTION myDateRangePF1 ;
CREATE PARTITION FUNCTION myDateRangePF1 (int) /* interger date used */
AS RANGE LEFT FOR VALUES (2015, 2016, 2017, 2018, 2019, 2020) ; -- note 2021 is not here, as in minus one so 6
GO
-- S Creates a partition scheme called myDateRangePS1 that applies myDateRangePF1 to the 7 filegroups created above
CREATE PARTITION SCHEME myDateRangePS1 --S
AS PARTITION myDateRangePF1 --F
TO (sw2015fg, sw2016fg, sw2017fg, sw2018fg, sw2019fg, sw2020fg, sw2021fg) ;
GO
-- Create partitioned tables called BI_Sub_Actvt/Rev/Usg that uses myDateRangePS1 to partition actvt_ym
/* NOTE: After table creation, add clustered index on actvt_ym and actvt_dt on ec tbl, then compress clustered indexs, compress tables */
-- Tables for new cube
CREATE TABLE [dbo].[BI_table1](
[actvt_ym] [int] null,
[actvt_dt] [date] NULL,
...
) ON myDateRangePS1 (actvt_ym)
GO
CREATE TABLE [dbo].[BI_table2](
[actvt_ym] [int] NULL,
[actvt_dt] [date] NULL,
...
) ON myDateRangePS1 (actvt_ym)
GO
CREATE TABLE [dbo].[BI_table3](
[actvt_ym] [int] NULL,
[actvt_dt] [date] NULL,
...
) ON myDateRangePS1 (actvt_ym)
GO
-- EOL
Good news, I was able to clear the files and filegroups by dropping the tables, schema and the functions, then did a shrink on the remaining filegroups, then removed the filegroups and files. I've now cleared out everything.
So I'm now at my problem of running out of space as I was loading the tables with the above partitioning settings. I did notice that I had to change fixed sizes to unlimited with SSMS after I had run the code, I would like to do that in the T-SQL code if possible.
I'm thinking of changing from my Int which is an Integer Date, which is YYYYMM, that is also the 1st column, the second column is DATE, and is YYYYMMDD. Thoughts? Int may be throwing all data to be loaded to the wrong partition and not the right one as it's not using a DATE datatype.
Also, what about the naming of my files, and filegroups, does this look solid or could a better numbering/pattern be had? As in, am I adding a incremented letter or number that doesnt need to be?
Thanks

I've tweaked the code more, and have a few questions.
Changed my partition to actvt_dt (date) and not actvt_ym (int) to get away from the int, and changed the col order on that in the create table.
Changed from Range Left to Range Right, as I understand it all the data for the same year will now be in the same partition filegroup which is what I want/need..
How can I specify, code syntax wise, not having data to laod earlier than 2015, to specify Primary as the first filegroup since the first partition will be unused as I understand it?

Thank you

USE Database_BI;
GO

-- Adds six new filegroups to the Database_BI database
ALTER DATABASE Database_BI
ADD FILEGROUP sw2015fg;
GO
ALTER DATABASE Database_BI
ADD FILEGROUP sw2016fg;
GO
ALTER DATABASE Database_BI
ADD FILEGROUP sw2017fg;
GO
ALTER DATABASE Database_BI
ADD FILEGROUP sw2018fg;
GO
ALTER DATABASE Database_BI
ADD FILEGROUP sw2019fg;
GO
ALTER DATABASE Database_BI
ADD FILEGROUP sw2020fg;
go
ALTER DATABASE Database_BI
ADD FILEGROUP sw2021fg;

-- Adds one file for each filegroup. for 6 filegroups
ALTER DATABASE Database_BI
ADD FILE
(
    NAME = sw2015dat1,
    FILENAME = 'G:\sw2015dat1.ndf',
    SIZE = 10MB,
    MAXSIZE = 250MB,
    FILEGROWTH = 10MB
)
TO FILEGROUP sw2015fg;
GO

ALTER DATABASE Database_BI
ADD FILE
(
    NAME = sw2016dat2,
    FILENAME = 'G:\sw2016dat2.ndf',
    SIZE = 10MB,
    MAXSIZE = 250MB,
    FILEGROWTH = 10MB
)
TO FILEGROUP sw2016fg;
GO

ALTER DATABASE Database_BI
ADD FILE
(
    NAME = sw2017dat3,
    FILENAME = 'G:\sw2017dat3.ndf',
    SIZE = 10MB,
    MAXSIZE = 250MB,
    FILEGROWTH = 10MB
)
TO FILEGROUP sw2017fg;
GO

ALTER DATABASE Database_BI
ADD FILE
(
    NAME = sw2018dat4,
    FILENAME = 'G:\sw2018dat4.ndf',
    SIZE = 10MB,
    MAXSIZE = 250MB,
    FILEGROWTH = 10MB
)
TO FILEGROUP sw2018fg;
GO

ALTER DATABASE Database_BI
ADD FILE
(
    NAME = sw2019dat5,
    FILENAME = 'G:\sw2019dat5.ndf',
    SIZE = 10MB,
    MAXSIZE = 250MB,
    FILEGROWTH = 10MB
)
TO FILEGROUP sw2019fg;
GO

ALTER DATABASE Database_BI
ADD FILE
(
    NAME = sw2020dat6,
    FILENAME = 'G:\sw2020dat6.ndf',
    SIZE = 10MB,
    MAXSIZE = 250MB,
    FILEGROWTH = 10MB
)
TO FILEGROUP sw2020fg;
GO

ALTER DATABASE Database_BI
ADD FILE
(
    NAME = sw2021dat7,
    FILENAME = 'G:\sw2021dat7.ndf',
    SIZE = 10MB,
    MAXSIZE = 250MB,
    FILEGROWTH = 10MB
)
TO FILEGROUP sw2021fg;
GO

-- F Creates a partition function called myDateRangePF1 that will partition a table into six partitions
CREATE PARTITION FUNCTION myDateRangePF1 (datetime)
AS RANGE RIGHT FOR VALUES (2015, 2016, 2017, 2018, 2019, 2020) ;
GO

-- S Creates a partition scheme called myDateRangePS1 that applies myDateRangePF1 to the six filegroups created above
CREATE PARTITION SCHEME myDateRangePS1 --S
AS PARTITION myDateRangePF1 --F
TO (sw2015fg, sw2016fg, sw2017fg, sw2018fg, sw2019fg, sw2020fg, sw2021fg) ;
GO

-- Create partitioned tables called BI_Sub_Actvt/Rev/Usg that uses myDateRangePS1 to partition actvt_ym
CREATE TABLE [dbo].[BI_Sub](
[actvt_dt] [date] NULL,
[actvt_ym] [int] null,
...
) ON myDateRangePS1 (actvt_dt)
GO

CREATE TABLE [dbo].[BI_Rev](
[actvt_dt] [date] NULL,
[actvt_ym] [int] NULL,
...
) ON myDateRangePS1 (actvt_dt)
GO

CREATE TABLE [dbo].[BI_Usg](
[actvt_dt] [date] NULL,
[actvt_ym] [int] NULL,
...
) ON myDateRangePS1 (actvt_dt)
GO