Archive

Posts Tagged ‘SQL – Data files’

SQL – Secondary data file

August 19, 2011 Leave a comment

Managing database files is one of the main responsibilities in DW maintenance space.
I have come across a scenario of adding and deleting the secondary data file (.ndf) to the database.
I am simulating the scenario using AdventureWorks database
 
Cases
1. Analysis of existing files
2. Adding new secondary data file
3. Removing secondary data file
 
Case 1: Analysis of existing files

USE [AdventureWorks]
EXEC sp_helpfile

 

DB files

DB files


 
As we could see, the database exists with one mdf and one ndf files
 
Case 2: Adding new secondary data file

USE AdventureWorks;
GO
-- Create a secondary data file i.e. ndf file.
ALTER DATABASE AdventureWorks 
ADD FILE (
    NAME = AdventureWorks_Data2,
    FILENAME = 'C:\Users\prav\Documents\BIpassion\SQL - Restore\AdventureWorks_Data.ndf',
    SIZE = 5MB
    );

 

Add .ndf file

Add .ndf file


 
Checking for the created AdventureWorks_Data2 ndf file

USE [AdventureWorks]
EXEC sp_helpfile

ndf file added

ndf file added


 
Case 3: Removing secondary data file
 
Removing secondary data file is a two-step process.
We need to move the existing data of the secondary data file to another file, here primary data file (mdf) and without doing the empty file, SQL engine doesn’t allow user to delete the secondary file.
 
Step 1: Empty secondary data file

USE [adventureworks]
DBCC SHRINKFILE (AdventureWorks_Data2, EMPTYFILE);
GO

 

Empty ndf file

Empty ndf file


 
DBCC SHRINKFILE is used to EMPTYFILE, Here the SQL Engine will transfer the data from ndf file to other files in the filegroup i.e. in this case it will transfer all the data of ndf file to mdf file.

Step 2: Remove secondary data file
 

ALTER DATABASE AdventureWorks
REMOVE FILE AdventureWorks_Data2;
GO

 

Remove ndf file

Remove ndf file


 
Using ALTER DATASE to remove the secondary data file from file group.
 
Checking for the remove process of AdventureWorks_Data2 ndf file

USE [AdventureWorks]
EXEC sp_helpfile

 

Check DB files

Check DB files


 
Points to consider
 
1. I haven’t show the error information upon deleting the ndf file without empty file process as I don’t have data in the ndf file
2. I don’t recommend this process in production as ndf files improve the performance of data access
3. I haven’t extend the presentation to moving the files options

Categories: SQL Server Tags: