Removing Additional (unwanted) MDF files

Question

Post reply

Removing Additional (unwanted) MDF files

wisl

SSCommitted

Points: 1554
More actions
May 23, 2016 at 10:21 am

#307985

I recently changed jobs and in my new company all our databases are completely maintained through Visual Studio and TFS Source Control. generally this works really well and we are very happy.
However in our UAT environment one of our databases has three data (.mdf) files, where we only have one data (.mdf) file for the same database in our TEST and LIVE environments.
Here is an example:
fileid,FILE_SIZE_MB,SPACE_USED_MB,FREE_SPACE_MB,FREE_PCT_MB,NAMEFILENAME
1,20736,10736.19,9999.81,0.48,EngineDb1,M:\MSSQL\Data
2,32856.19,47.95,32808.24,1,EngineDb_log,L:\MSSQL\Logs
3,20224,10335.63,9888.38,0.49,EngineDb2,M:\MSSQL\Data
4,20480,10528.44,9951.56,0.49,EDWEngineDb,M:\MSSQL\Data
I have been told that these additional data files were somehow created by Visual Studio during one of our releases. We would like to remove the additional data files but don't know how to go about this.
Question1:
Has anyone ever seen this behavior before in Visual Studio /TFS Source Control
Question2:
Can we remove these additional files, and if so how?

Viewing 7 posts - 1 through 6 (of 6 total)

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

eldosepd SSC Enthusiast Points: 168 More actions · Answer 1

You can remove the secondary datafiles using the below commands

USE DB1

DBCC SHRINKFILE('filename1', EMPTYFILE);

and then delete the file:

ALTER DATABASE DB1 REMOVE FILE filename1;

Lowell SSC Guru Points: 323504 More actions · Answer 2

if your filegroup is not empty, you might need to alter some indexes and recreate them in another file group.

this would show you theFileGroup Name and the file a given index might be stored inside:

SELECT OBJECT_NAME( i."id" ) AS TableName ,

i."Name" AS IndexName ,

FILEGROUP_NAME( i.groupid ) AS FileGroupName,

flz.[filename]

FROM sysindexes AS i

inner join sys.sysaltfiles flz ON i.groupid = flz.groupid

WHERE ( i.indid IN ( 0 , 1 ) Or i.indid < 255 ) And -- Tables & indexes only

OBJECTPROPERTY( i."id" , 'IsUserTable' ) = 1 And -- User tables only

OBJECTPROPERTY( i."id" , 'IsMSShipped' ) = 0 And -- No system tables

COALESCE( INDEXPROPERTY( i."id" , i."Name" , 'IsStatistics' ) , 0 ) = 0 And -- No Statistics / Auto-Create stats

COALESCE( INDEXPROPERTY( i."id" , i."Name" , 'IsHypothetical' ) , 0 ) = 0 -- No Hypothetical statistics

AND FILEGROUP_NAME( i.groupid ) <> 'PRIMARY'

ORDER BY FileGroupName , TableName , IndexName

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

wisl SSCommitted Points: 1554 More actions · Answer 3

Thank you - this was very helpful.

I ran your query and I can see that all the indexes are on the Primary filegroup.

I can also see that all the files are on the Primary filegroup, is this what you would expect?

FileGroupNamefilename

PRIMARYM:\MSSQL\DATA\EngineDb1.mdf

PRIMARYM:\MSSQL\DATA\EngineDb2.mdf

PRIMARYM:\MSSQL\DATA\EngineDb.mdf

eldosepd SSC Enthusiast Points: 168 More actions · Answer 4

As all the files are on primary file group, you can empty the below files using the command DBCC SHRINKFILE ('filename',EMPTYFILE) and remove the files using alter database remove file commands.

PRIMARY M:\MSSQL\DATA\EngineDb2.mdf

PRIMARY M:\MSSQL\DATA\EngineDb.mdf

Before emptying the data files make sure that EngineDb1.mdf is having enough space to occupy the data from EngineDb2 and EngineDb3.

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 5

I typically wouldn't expect more than one file in the primary filegroup, but there are certainly reasons to do this. Not sure you get benefits on the same drive like this, but it's not necessarily wrong. It is slightly complex, and unnecessary in most cases.

wisl SSCommitted Points: 1554 More actions · Answer 6

Steve Jones - SSC Editor (5/24/2016)
I typically wouldn't expect more than one file in the primary filegroup, but there are certainly reasons to do this. Not sure you get benefits on the same drive like this, but it's not necessarily wrong. It is slightly complex, and unnecessary in most cases.

I don't believe that it was a design choice to have three files , but rather when the Database project was deployed to UAT by TFS this was how the database was created.

So I was wondering if anyone else had seen similar behavior when deploying a database project with TFS?