Database is using Primary filegroup for partitions instead of the configured default filegroup

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

UIM DB keeps using the Primary filegroup instead of the filegroup that is set as default.

This has caused issues where the DB has run out of space when it still actually has plenty of space left. 

 

We are using MSSQL Server 2017 on Windows Server 2016 OS.

 

Environment

Release : 20.3

Component : UIM - DATA_ENGINE

Cause

Stored procedures in the UIM database have been hardcoded to use the Primary filegroup.

Resolution

You can change the stored procedures, but please involve your DBA in following these instructions.  This process can take a long time depending upon the size of the tables/partitions.  The new filegroup should already be created as these steps will not create it.

 

The following stored procedures need to be edited:

spn_de_PartitionAdmin__PartitionTable.sql
spn_de_PartitionAdmin__CreateIndexes.sql
spn_de_PartitionAdmin__CreateInitialIndexes.sql
spn_de_PartitionAdmin__ShiftTable

Replace where the PRIMARY filegroup is referenced to the desired filegroup name.

 

 

Then, the following needs to be done:

• Before executing below stored procedures please
  • Disable data maintenance job. 
  • Validate whether data maintenance job disabled or not. 
  • Check in data_engine.cfg, if the value set to data_management_active=no which meant data maintenance job disabled. 
  • Make sure there are no index maintenance jobs are running during this activity.
• Run below stored procedures manually.  Follow the sequence and order listed, waiting for each previous instance to finish before beginning the next.  This will recreate partitions on the desired filegroup
• Once the below steps are done, enable data maintenance.

 

 

1. first run the below stored procedure

DECLARE        @return_value int,
        @pErrorMessage ndtLongString,
        @table_name varchar(17),
        @table_type varchar(2),
        @current_time datetime;

SET @table_type = 'RN';
DECLARE RN_tblname CURSOR FOR
        SELECT name from sys.objects WHERE name like 'RN_QOS_DATA%' order by name asc

OPEN RN_tblname;
     FETCH NEXT FROM RN_tblname INTO @table_name

WHILE (@@FETCH_STATUS = 0)

    BEGIN
          /* Un-partitioning */

               EXEC    @return_value = [dbo].[spn_de_PartitionAdmin__UnpartitionTable]
                    @pTableName = @table_name,
                    @pTableType = @table_type,
                    @pLogLevel = 5

        RAISERROR('TableName %s Un-partitioning done. rc = %d. error = %s.',0,1,
            @table_name,
            @return_value,
            @pErrorMessage) WITH NOWAIT

        /* partitioning */

               SELECT    @current_time = GETDATE();
               EXEC    @return_value = [dbo].[spn_de_PartitionAdmin__PartitionTable] 
                @pTableName = @table_name, 
                    @pTableType = @table_type,
                    @pCurrentTime = @current_time, 
                    @pLogLevel = 5

        RAISERROR('TableName %s partitioning done. rc = %d. error = %s.',0,1,
            @table_name,
            @return_value,
            @pErrorMessage) WITH NOWAIT

        FETCH NEXT FROM RN_tblname INTO @table_name
    END

CLOSE RN_tblname;
DEALLOCATE RN_tblname;

2. after completion of the above stored procedure, run below stored procedure:

DECLARE        @return_value int,
        @pErrorMessage ndtLongString,
        @table_name varchar(17),
        @table_type varchar(2),
        @current_time datetime;

SET @table_type = 'HN';

DECLARE HN_tblname CURSOR FOR
        SELECT name from sys.objects WHERE name like 'HN_QOS_DATA%' order by name asc

OPEN HN_tblname;
     FETCH NEXT FROM HN_tblname INTO @table_name

WHILE (@@FETCH_STATUS = 0)

    BEGIN
          /* Un-partitioning */

               EXEC    @return_value = [dbo].[spn_de_PartitionAdmin__UnpartitionTable]
                    @pTableName = @table_name,
                    @pTableType = @table_type,
                    @pLogLevel = 5

        RAISERROR('TableName %s Un-partitioning done. rc = %d. error = %s.',0,1,
            @table_name,
            @return_value,
            @pErrorMessage) WITH NOWAIT

        /* partitioning */

               SELECT    @current_time = GETDATE();
               EXEC    @return_value = [dbo].[spn_de_PartitionAdmin__PartitionTable] 
                @pTableName = @table_name, 
                    @pTableType = @table_type,
                    @pCurrentTime = @current_time, 
                    @pLogLevel = 5

        RAISERROR('TableName %s partitioning done. rc = %d. error = %s.',0,1,
            @table_name,
            @return_value,
            @pErrorMessage) WITH NOWAIT

        FETCH NEXT FROM HN_tblname INTO @table_name
    END

CLOSE HN_tblname;
DEALLOCATE HN_tblname;

3. upon completion of above stored procedure, run below stored procedure:

DECLARE        @return_value int,
        @pErrorMessage ndtLongString,
        @table_name varchar(17),
        @table_type varchar(2),
        @current_time datetime;

SET @table_type = 'DN';

DECLARE DN_tblname CURSOR FOR
        SELECT name from sys.objects WHERE name like 'DN_QOS_DATA%' order by name asc

OPEN DN_tblname;
     FETCH NEXT FROM DN_tblname INTO @table_name

WHILE (@@FETCH_STATUS = 0)

    BEGIN
          /* Un-partitioning */

               EXEC    @return_value = [dbo].[spn_de_PartitionAdmin__UnpartitionTable]
                    @pTableName = @table_name,
                    @pTableType = @table_type,
                    @pLogLevel = 5

        RAISERROR('TableName %s Un-partitioning done. rc = %d. error = %s.',0,1,
            @table_name,
            @return_value,
            @pErrorMessage) WITH NOWAIT

        /* partitioning */

               SELECT    @current_time = GETDATE();
               EXEC    @return_value = [dbo].[spn_de_PartitionAdmin__PartitionTable] 
                @pTableName = @table_name, 
                    @pTableType = @table_type,
                    @pCurrentTime = @current_time, 
                    @pLogLevel = 5

        RAISERROR('TableName %s partitioning done. rc = %d. error = %s.',0,1,
            @table_name,
            @return_value,
            @pErrorMessage) WITH NOWAIT

        FETCH NEXT FROM DN_tblname INTO @table_name
    END

CLOSE DN_tblname;
DEALLOCATE DN_tblname;

4. upon completion of above script, please execute below stored procedure.

DECLARE        @return_value int,
        @pErrorMessage ndtLongString,
        @table_name varchar(17),
        @table_type varchar(2),
        @current_time datetime;

SET @table_type = 'BN';

DECLARE BN_tblname CURSOR FOR
        SELECT name from sys.objects WHERE name like 'BN_QOS_DATA%' order by name asc

OPEN BN_tblname;
     FETCH NEXT FROM BN_tblname INTO @table_name

WHILE (@@FETCH_STATUS = 0)

    BEGIN
          /* Un-partitioning */

               EXEC    @return_value = [dbo].[spn_de_PartitionAdmin__UnpartitionTable]
                    @pTableName = @table_name,
                    @pTableType = @table_type,
                    @pLogLevel = 5

        RAISERROR('TableName %s Un-partitioning done. rc = %d. error = %s.',0,1,
            @table_name,
            @return_value,
            @pErrorMessage) WITH NOWAIT

        /* partitioning */

               SELECT    @current_time = GETDATE();
               EXEC    @return_value = [dbo].[spn_de_PartitionAdmin__PartitionTable] 
                @pTableName = @table_name, 
                    @pTableType = @table_type,
                    @pCurrentTime = @current_time, 
                    @pLogLevel = 5

        RAISERROR('TableName %s partitioning done. rc = %d. error = %s.',0,1,
            @table_name,
            @return_value,
            @pErrorMessage) WITH NOWAIT

        FETCH NEXT FROM BN_tblname INTO @table_name
    END

CLOSE BN_tblname;
DEALLOCATE BN_tblname;

.