Tips and Tricks about Files and Filegroups in the SQL Server NO 2 (ANO11)

#Filegroups?in the SQL Server,?#Readonly?FileGroup?#Tips?and Tricks is SQL server

This article is a continuation of article number 10 (ANO10), if haven't read it please study that and then back to this one.

How to remove a datafile and a filegroup:

To remove a filegroup, it's necessary to first remove all dependencies. For example, datafile/s, objects such as tables, and indexes on it, and then remove the filegroup. Now I want to remove FG1 ( these objects are created in the article ANO10)

SP_HELPFILE
GO
ALTER DATABASE FGDB??
REMOVE FILE FGDB_Data1 ;?
GO
        

As you see we cannot remove 'FGDB_Data1' data file, first of all we have to empty this file group. To check what is in this data file use the following script:

SELECT [objects].[name]? ?AS TableName,
? ? [index].[name]? ? ?AS IndexName,?
? ? [filegroup].[name] AS FileGroupName?
FROM? ?sys.indexes [index]?
? ? INNER JOIN sys.filegroups [filegroup]?
? ? ? ? ? ? ON [index].data_space_id = [filegroup].data_space_id?
? ? INNER JOIN sys.all_objects [objects]?
? ? ? ? ? ? ON [index].[object_id] = [objects].[object_id]?
WHERE? [index].data_space_id = [filegroup].data_space_id?
? ? AND [filegroup].[name] <> 'PRIMARY'??        

there is an index inside FG1, we have to send it to the second data file(FGDB_Data2) to do this use this script:

DBCC SHRINKFILE (FGDB_Data1, EMPTYFILE)        

Now we are allowed to remove FGDB_Data1

ALTER DATABASE FGDB?
REMOVE FILE FGDB_Data1 ;?
GO?        

in this step, all data are recorded in FGDB_Data2, to remove this data file it's necessary to move all objects to another data file, to do this we have two approaches:

1: Use a script to make a copy of our table in the other filegroup, then drop it from the current filegroup

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_Employee
	(
	ID int NOT NULL IDENTITY (1, 1),
	Name char(50) NULL,
	FullName char(7000) NULL
	)? ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Employee SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_Employee ON
GO
IF EXISTS(SELECT * FROM dbo.Employee)
	 EXEC('INSERT INTO dbo.Tmp_Employee (ID, Name, FullName)
		SELECT ID, Name, FullName FROM dbo.Employee WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_Employee OFF
GO
DROP TABLE dbo.Employee
GO
EXECUTE sp_rename N'dbo.Tmp_Employee', N'Employee', 'OBJECT'?
GO
ALTER TABLE dbo.Employee ADD CONSTRAINT
	PK__Employee__3214EC27B0D5A4F4 PRIMARY KEY CLUSTERED?
	(
	ID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX IX01 ON dbo.Employee
	(
	Name
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON FG_Index
GO
COMMIT

        

2: Use a stored procedure to move it to another filegroup, download it from :

After downloading the stored procedure copy it into new query and run it to create a stored procedure, this sp requires a number of arguments:

@SourceFileGroupID, @TargetFileGroupID , @TableToMove,

@TableSchema, @MovePKAndAllUniqueConstraints, @MoveAllNonClusteredIndexes

Use this command to find the filegroup ID for SourceFileGroupID and TargetFileGroupID

SP_HELPFILEGROUP        

we want to move the Employee table from FG1 to the primary filegroup, therefore our SourceFileGroupID is 2(green) and target is 1(blue), Oue table is 'Employee',

Schema:'dbo', and if you want to move PK And AllUniqueConstraints set 1 otherwise set 0, and if you want to move All NonClusteredIndexes set 1/0

SP_HELPFILEGROU
EXEC dbo.SP_MOVE_TABLES
	@SourceFileGroupID =2,
	@TargetFileGroupID =1,
	@TableToMove ='Employee ',
	@TableSchema ='dbo',
	@MovePKAndAllUniqueConstraints =1,
	@MoveAllNonClusteredIndexes =0
GOP        

Now we can remove datafile FGDB_Data2 and then remove filegroup FG1

ALTER DATABASE FGDB?
REMOVE FILE FGDB_Data2 ;?
GO


ALTER DATABASE FGDB??
REMOVE FILEGROUP FG1 ;??
GO
SP_HELPFILEGROUP?        

Notice: if we want to remove a read-only filegroup, before using 'DBCC SHRINKFILE (FGDB_Data1, EMPTYFILE)' it's necessary to change it to READ_WRITE mode

ALTER DATABASE FGDB MODIFY FILEGROUP FG_ReadOnlyData READ_WRITE WITH ROLLBACK IMMEDIATE        

In the next article, I will explain other uses of filegroup in the SQL Server.

if this article was useful please like it.