What are these extra files?

Question

What are these extra files?

kurtwest

SSC Veteran

Points: 237
More actions
May 10, 2012 at 10:08 am

#268640

I have one database on my server that has more than just mdf and ldf files. There is a bunch of files with a .db extension. I don't understand what they are for. Can someone explain?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

kurtwest (5/10/2012)
I have one database on my server that has more than just mdf and ldf files. There is a bunch of files with a .db extension. I don't understand what they are for. Can someone explain?

This database has multiple data files and file groups. The extension .DB is not standard as these would normally be .ndf files. To start you may want to do some research in Books Online.

Steve Jones - SSC Editor SSC Guru Points: 736363 More actions · Answer 2

You need to read about files and filegroups in Books Online. By default, a database as one MDF file for data, and one LDF file for logging. You can add additional files and filegroups to spread out the storage across multiple files and even disks for performance or scalability.

Filegroups are logical containers for files, and you can have partial backups (filegroup backups) or partial restores within these that can aid with recovery.

There was some advice given years ago that having a separate file for each processor (or core) would speed I/O. That's not completely true, but many people implemented it. It looks like you have extra files for no real reason. It could be for paritioning, and future planning, so you'd have to check the db tables to see if you have partitions spread out here in some reason.

If not, you can move objects off filegroups by rebuilding them on another filegroup. There is an ON clause in the ALTER INDEX statement. You can also empty filegroups and remove them from the db.

http://msdn.microsoft.com/en-us/library/ms188388.aspx

KarenM SSChasing Mays Points: 608 More actions · Answer 3

Could your db have been imported from some other dbms? Might explain those extra files.

Regardless as Steve already advised those additional filegroups can be removed.

Jeff Moden SSC Guru Points: 1004393 More actions · Answer 4

KarenM (5/11/2012)
Could your db have been imported from some other dbms? Might explain those extra files.
Regardless as Steve already advised those additional filegroups can be removed.

Uhm... make sure you do a backup first.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)