4

I have a database called example.mdf with a total size of 1GB which suffers from performance issues. I checked the allocated hardware and it is higher than required, I double checked the design and every thing looks normal, when I look at the .mdf files in their physical location (C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\example.mdf) I found that the database is split into multiple files example_1.mdf, example_2.mdf, example_3.mdf, up to example_7.mdf.

I have another database file on the same SQL Server that has the same issue.

Why does this happen? Does this affect the performance? How can I prevent or stop SQL Server from splitting my .mdf files? Moreover I need to combine back the already split files.

Improve this question
5
  • 4
    SQL Server didn't do this on its own - it would only create multiple data files if you, or a script you ran, told it to.
    – Aaron Bertrand
    Commented Mar 22, 2013 at 1:48
  • it isnt my database, I mean I didnt create it, I only have to fix the issue!
    – Mohamed Sawaf
    Commented Mar 22, 2013 at 2:17
  • 2
    You may want to read Understanding Files and Filegroups for a better idea of what the issue might be.
    – HABO
    Commented Mar 22, 2013 at 2:37
  • 1
    What do you mean by the allocated hardware is higher than required? Typically having multiple data files isn't going to hurt your performance. Or increase your disk space usage unless you have a lot of empty space in each file.
    – Kenneth Fisher
    Commented Mar 22, 2013 at 3:28
  • Aside from the suspect naming of the files (all .mdf extensions), it's highly unlikely that multiple files are hurting performance for a 1 GB database. Unless your max server memory is way under 1 GB and the whole DB is on a single spindle, or something ridiculous like that. In any event, I would leave the files alone for now and look elsewhere for performance issues.
    – Jon Seigel
    Commented Mar 22, 2013 at 14:11

6 Answers 6

Reset to default
9

Who ever created the database did this on purpose. Assuming that all the database files are part of the same file group (the database properties will tell you this) then all you need to do is do a DBCC SHRINKFILE any use the EMPTYFILE parameter.

DBCC SHRINKFILE (Example_1, EMPTYFILE)
GO
ALTER DATABASE MyDatabase REMOVE FILE Example_1
GO

Do that for each of the files and all the data will be put back into a single file. You can then delete the extra files.

Now odds are this isn't hurting your performance any, so you'll need to continue investigating why you are having performance problems.

Improve this answer
2
  • There is a step missing. First run dbcc shrinkfile, then run alter database remove file, then delete the files in the OS. See example D at DBCC SHRINKFILE (Transact-SQL).
    – Greenstone Walker
    Commented Mar 25, 2013 at 4:47
  • Good point, added that in. Otherwise the data will end up moved all over the place.
    – mrdenny
    Commented Mar 25, 2013 at 20:53
4

These mdf files can also be of other databases...

to check your database files use script:

USE [DatabaseName]
GO

SELECT  name, physical_name
FROM    sys.database_files
GO
Improve this answer
3

You want to recombine the files because you think that is the cause of your performance issue. Do you have any proof that it actually is?

It is a lot more likely that the problem is somewhere else, as distributing a database across multiple files actually increases performance in many cases.

Things to look at first:

  1. do you have appropriate indexes
  2. are you using cursors in your queries
  3. are your queries written in a way that would prevent index usage
  4. is your server actually big enough to handle the load. However, this is a very small database, so it is likely that your hardware is not the problem.

If you looked at all that and you still think that the splitt file is your problem, you can follow these steps:

  1. Add a new file big enough to hold the entirety of the data.
  2. set all original files to not autogrow.
  3. shrink each original file 1 at a time with "release unused space"
  4. shrink each original file 1 at a time with "empty file and migrate data" and then drop the file before shrinking the next.
  5. Run an index rebuild on every index in the database to reduce fragmentation caused by the migration.

However, make sure you do not need the files. Someone might have created file groups to allow for partitioning. In that case you cannot combine the files without first moving all table and partitions from the other filegroups to the primary filegroup.

Improve this answer
0

There are couple of things to check

  1. Why is the database on System drive (C:\ ) ? Dont you have any other storage / SAN attached to the server ?
  2. Where is the log location ?
  3. How is the database being used - no of concurrent users, load on the server, RAM on the server, no of cpu's on the server ?
  4. Why is a 1 GB database split up into 7 files ? Do you really need the database having multiple files ? Also, note that you can have 1 primary filegroup(.mdf) and then rest are secondary filegroups (.*n*df).
  5. When you say "performance issues" what is actually happening - what is slow or not working ? Did you ran a short trace during the period when performance is degraded ?
Improve this answer
0

The .mdf extensions doesn't really matter and the same case with .ndf/.ldf extensions as well. Primary file/secondary file/log file can have any extension (something like .xyz)

And NO it is not SQL Server that created multiple data files.

For further clarity please refer here: Can a Database have more than one mdf File?

If you want to delete the extra data files/log files, please refer this post: How to Best Remove Extra TempDB Data Files and Log Files

Improve this answer
-2

Not sure if you can merge files in existing database but you can create new database and then use some of the third party database comparison tools to migrate data and structure to new database. Just be careful when migrating schema not to migrate the part that tells SQL Server to split files ;)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.