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.
Best Answer
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.
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.