SQL SERVER – Find Current Location of Data and Log File of All the Database

As I am doing lots of experiments on my SQL Server test box, I sometime gets too many files in SQL Server data installation folder – the place where I have all the .mdf and .ldf files are stored. I often go to that folder and clean up all unnecessary files I have left there taking up my hard drive space. I run following query to find out which .mdf and .ldf files are used and delete all other files. If your SQL Server is up and running OS will not let you delete .mdf and .ldf files any way giving you error that file already in use. This list also helps sometime to do documentation of which files are in being used by which database.

SELECT name, physical_name AS current_file_location
FROM sys.master_files

Following is the output of files used by my SQL Server instance.

SQL SERVER - Find Current Location of Data and Log File of All the Database mastefiles

Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Backup and Restore, SQL Scripts, SQL Server Security, SQL System Table
Previous Post
SQL SERVER – List All Server Wide Configurations Values
Next Post
SQLAuthority Author Visit – A True Outsourcing Giant and Technology Leader DigiCorp in Ahmedabad India

Related Posts

46 Comments. Leave new

  • I got a big application with lots of databases, multiple filegroups on each database and multiple data files for each filegroup. How can I find out which indexes are in which files?

    Reply
  • Hi Mr.Pinal,
    When i am creating the new DB in my sql server 2008, it’s create with existing tables of other DB. i changes the MDF and LFD file directory. Even thoe i am facing the same.
    can recommend some options.

    Reply
  • Hi,
    I needed datafiles sapce usage percentage

    Reply
  • I’m hoping to get help with a slight variant of the original post. I have a SQL Server 2008 R2 server with about 20 databases. Though database properties show one mdf/ldf pair, when I run

    select name, physical_name from sys.master_files order by name

    I have 6 database names with multiple physical_name entries. This is a DEV database server and often bak files with different names are restored to the various databases. I am unable to delete the mdf/ldf entries that appear to not be in use. Action can’t be completed because the file is open in SQL Server (MSSQLSERVER).

    I tried detaching and then reattaching the database to see if SQL Server would update its sys.master_files information but the same multiple entries remain.

    Any ideas about how I can resolve this would be greatly appreciated.

    Reply
  • It amazes me how often, when I’m scrambling for a quick SQL answer, I end up finding it here… along with tons of other real-world solutions to everyday SQL Server questions. Thanks for the great site!

    Reply
  • Tajuddin Siddique
    November 8, 2015 7:25 pm

    hi sir
    how to find log file
    pls reply me

    Reply
  • There are many places to find information on the Net but your site always gives solid answers that make my job much easier. Thank you for your efforts and please keep up the great work!

    Reply
    • I appreciate your kind words.

      Reply
      • Hi Pinal, Thank for the info. Can you please provide same for SQL managed instance and Azure SQL database. It shows some this like this C:\WFRoot\DB64C.4\Fabric… what is this path and is it in blob storage? I will be happy to get any kind of info.

  • Wow and great achivement

    Reply
  • Scott Edwards
    May 24, 2017 10:20 pm

    You just saved me a heap of trouble, thank you!!

    Reply
  • Thank you so much for sharing such a kind of information.

    Reply
  • Need same information for Azure managed instance and Azure sql. Please help to provide some info.

    Reply

Leave a Reply