Sql-server – Running out of space, add new drive (add file to filegroup)

Do I need to add a new entry to Filegroups and then add to Files?

You can do one of two things here:

1. Add a new filegroup and at least one new data file
2. Add a new data file to an existing filegroup (PRIMARY in this case)

Is the PRIMARY type only supposed to be used for the first file?

No, the PRIMARY filegroup can contain multiple data files.

Once I add a file, does the DB engine automatically decide where to put data or do I have to go into my tables and specify this in some way?

Within the filegroup, SQL Server will manage the data dispersion across the data files through a proportional fill algorithm. Therefore, you will not need to manually administer where the data goes within a filegroup. But, if you create an additional filegroup with one or more data files, and you want data to live on that filegroup then you will need to explicitly state that (unless you set the new filegroup as the default filegroup).

For instance:

create table dbo.YourNewTable
(
    col1 int not null,
    col2 int not null
    -- so on and so forth
) on YourNewFilegroup;
go

To find out what your default filegroup currently is:

select
    name as default_filegroup_name
from sys.filegroups
where is_default = 1;

To change the default filegroup:

alter database YourDatabase
modify filegroup YourNewFilegroup
default;
go

The results indicate at some point an explicit NULL partition boundary was added to the function when the partition scheme NEXT USED filegroup was set to DailyAlbertFG30. Also, I don't see DailyAlbertFG2 used. Perhaps there was once a partition on that filegroup that was subsequently merged.

Below is a script that shows how a FG30 partition with the NULL boundary can be created. The NULL boundary might have been added accidentally.

CREATE PARTITION FUNCTION DailyAlbertPF1 (datetime2(3)) AS RANGE RIGHT FOR VALUES();
GO
CREATE PARTITION SCHEME DailyAlbertPS1 AS PARTITION DailyAlbertPF1 ALL TO ([DailyAlbertFG1]);
GO
CREATE TABLE dbo.FactAgentAlbertPortalSessionEntries
    (
      DateTimeColumn datetime2(3)
    )
ON  DailyAlbertPS1(DateTimeColumn);
GO

DECLARE @FileGroupNumber int = 1;
DECLARE @DateTimeBoundary datetime2(3) = '2015-04-15T00:00:00.000';
DECLARE @SQL nvarchar(MAX);
WHILE @DateTimeBoundary <= '2015-05-14T00:00:00.000'
BEGIN
    SET @SQL = N'ALTER PARTITION SCHEME DailyAlbertPS1 NEXT USED DailyAlbertFG' + CAST(@FileGroupNumber AS nvarchar(5)) + N';';
    EXEC(@SQL);
    ALTER PARTITION FUNCTION DailyAlbertPF1() SPLIT RANGE(@DateTimeBoundary);
    SET @DateTimeBoundary = DATEADD(day, 1, @DateTimeBoundary);
    SET @FileGroupNumber += 1;
END;
--add NULL boundary on DailyAlbertFG30
SET @DateTimeBoundary = NULL;
ALTER PARTITION SCHEME DailyAlbertPS1 NEXT USED DailyAlbertFG30;
ALTER PARTITION FUNCTION DailyAlbertPF1() SPLIT RANGE(@DateTimeBoundary);
GO