1

OS: Windows 7 Professional

Database: SQL Server 2008 R2 Standard Edition

Just detected by SpaceMonger that, each of my databases on test instance has three copies of data files (.mdf) and log files (.ldf).

they are located at three folders:

  • ..\DataFiles\Test
  • ..\LogFiles\Test
  • ..\Microsoft SQL Server\Mssql10_50.test\Mssql\Data

and they have exactly same file size and timestamp.

I confirmed that I have no mirroring setup for any of my databases, neither do I have any special setup to enforce the database to have files splitted.

The only suspicious setting I can find is:

  • the first two locations are the server level database default location (right click server in the MSSMS -> Properties -> Database Settings -> Database default locations): the first is for Data while the second is for Log,

  • and the last is each database's file location appearing in the MSSMS (right click a database -> Properties -> Files -> Database Files)

This annoying problem has made my server available disk space very low and forced me to do housekeeping very often.

any idea why does this happen?

any methodology i can follow to find out what has been wrong?

-- EDIT ---

Thank you all for the comments and reply.

We have identified the problem - not related to the sql database setting, but the folders themselves: the three folders have a setup to mirror each other. we have got our network team to investigate why it's so.

  • 1
    Are you using the User instances and AttachDBFileName parameters in your connection strings or Visual Studio projects? This is exactly how these things work - when you connect to a database this way, it creates a new copy of your database files. – Aaron Bertrand Oct 1 '13 at 14:17
3

It is possible that your databases were set up this way. To find out you can run this query:

SELECT DATABASE_NAME(database_id) DbName,* FROM sys.master_files ORDER BY 1;

It lists all files that belong to each of your databases.

If the databases in question list only one file each, then something outside of SQL Server is creating these copies.

Otherwise each database is just split into multiple files and actually taking up that space. In that case you can really only either delete databases or increase the disk size.

It can have performance benefits to split a database into multiple data files. However, splitting the log into multiple files is usually unnecessary as SQL Server is writing always only to one of them at a time.

  • Thanks for the reply. i did that query and thus confirmed I don't have any database mirroring/split setup. – Rex Oct 2 '13 at 1:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.