Multiple MDF files

[micang]

SQL 2000

Hi All,

I have noticed that one of my databases has four mdf files (ADS.mdf,ADS_1.mdf,ADS_2.mdf,ADS_3.mdf).

Can anyone shed some light if this is "normal" or if something is not kosher?

Any info appreciated.

Thanks
Michael

 

[gmmastros]

Well.... There's nothing inherently wrong with this. In fact, it sometimes makes sense to use multiple MDF files so that you can put different tables and/or indexes on different hard drives. This usually improves performance alot.

Run this...

[COLOR=blue]select[/color] * [COLOR=blue]from[/color] sysfiles

You'll probably notice that each file is on a seperate hard drive.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[micang]

Hi George,

Thanks for the input. I see they are all on the same drive.

This is the result


fileid groupid size maxsize growth status perf name filename
1 1 6112752 -1 10 1081346 0 CarweBMasterDb_Live_VehicleMain_PRIMARY D:\Microsoft SQL Server\MSSQL$ADS\Data\ADS.mdf
2 0 63 -1 10 1081410 0 CarweBMasterDb_Live_VehicleMain_LOG D:\Microsoft SQL Server\MSSQL$ADS\Data\ADS_log.LDF
3 2 3705208 -1 10 1081346 0 CarweBMasterDb_Live_VehicleMain_MAIN D:\Microsoft SQL Server\MSSQL$ADS\Data\ADS_1.mdf
4 3 454560 -1 10 1081346 0 CarweBMasterDb_Live_VehicleMain_INDEX D:\Microsoft SQL Server\MSSQL$ADS\Data\ADS_2.mdf
5 4 475440 -1 10 1081346 0 CarweBMasterDb_Live_VehicleMain_SUPPORT D:\Microsoft SQL Server\MSSQL$ADS\Data\ADS_3.mdf

The vehiclemain is a database I restored from a supplier that supplied a back up of their database which I restored. So I assume that that is why I am getting the multiple mdf's, which is how they have it set-up?

Thanks for your input greatly appreciated.

Michael

 

[mrdenny]

Yes when you restore the database it will look exactly as it did when they backed it up, including the number of files.

As George said, typically these files will be on different hard drives. Also the files which are not the origional file are typically named with an ndf file extention instead of the mdf file extention so that they can be easily reconignized as secondary files.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[micang]

Hi Denny,

Thansk for the info, now it makes more sense and also, as you state, there are also some with an ndf extension that I have never seen before.

Thanks so much.

Michael