Sql-server – why do I have three copies of data/log files per database on same server instance

sql serversql-server-2008

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.

Best Answer

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.

Related Question