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)

, , ,
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

43 Comments. Leave new

  • Nice

    below script shows the files used frequency (by joining with DMV dm_io_virtual_file_stats)

    SELECT DB_NAME(mf.database_id) AS databaseName
    ,mf.physical_name
    ,num_of_reads
    ,num_of_bytes_read
    ,io_stall_read_ms
    ,num_of_writes
    ,num_of_bytes_written
    ,io_stall_write_ms
    ,io_stall
    ,size_on_disk_bytes
    FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
    JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id
    AND mf.file_id = divfs.file_id
    ORDER BY 3 DESC

    Reply
  • Hi

    I am trying to Find Current Location of Data and Log File of All the Database on SQL 2000 but getting errors. It seems the compiler does not like name after SELECT statement.

    Which word can I replace name with.

    Reply
  • I noticed we have to do both match of the two queries, pinal dave and jerry hung.

    In most of the production, the database name can be different and file names can also not be prefixed with database name.
    (because of real backup and restore scenarios).

    Also, I add is the size in KB, MB, GB … for better readability.

    Hope it is useful for others.

    SELECT
    DB_NAME(mf.database_id) AS databaseName,
    name as File_LogicalName,
    case
    when type_desc = ‘LOG’ then ‘Log File’
    when type_desc = ‘ROWS’ then ‘Data File’
    Else type_desc
    end as File_type_desc
    ,mf.physical_name
    ,num_of_reads
    ,num_of_bytes_read
    ,io_stall_read_ms
    ,num_of_writes
    ,num_of_bytes_written
    ,io_stall_write_ms
    ,io_stall
    ,size_on_disk_bytes
    ,size_on_disk_bytes/ 1024 as size_on_disk_KB
    ,size_on_disk_bytes/ 1024 / 1024 as size_on_disk_MB
    ,size_on_disk_bytes/ 1024 / 1024 / 1024 as size_on_disk_GB
    FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
    JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id
    AND mf.file_id = divfs.file_id
    ORDER BY num_of_Reads DESC

    Reply
    • Just wanted to say thanks for this query. It helped me build a PowerShell script to query a remote SQL for this info. I just need to filter the DBs returned, but that should be no big deal.

      Thanks again!

      Reply
    • Thanks a lot. Much more than what I want or what I was seeking for.

      Reply
    • Matthew Fritz
      June 15, 2015 4:24 pm

      Exactly what I was looking for. Thank you very much.

      Reply
  • Give Solutin For Reduce size of Log File:

    Reply
  • Vikram K Mahapatra
    November 1, 2010 6:08 pm

    We can also find the location of data file and log file by executing sp_helpfile.

    Reply
  • Hi! I had try it in Query, it work and give me result.
    I try to implement into vb.net with:

    Dim sqlStatement1 As SqlCommand
    sqlStatement1 = New SqlCommand(“SELECT name, physical_name AS current_file_location FROM sys.master_files Where name = ‘Ticket'”, mMain.Conn)

    Dim sdr1 As SqlDataReader = sqlStatement1.ExecuteReader()

    the reader return no result. why?

    Reply
  • how to do it in vb.net? i try use the reader but it faile to return any result

    Reply
  • Good Morning, Reading this website is a real pleasure, Many thanks !

    Reply
  • Hi Panal,
    I want a list of .mdf and .ldf filename,size,location and date modifed through query analyzer same as we check in the drive.how to write a T-Sql to get this information.
    Thanks in advance
    Shahab

    Reply
  • To achieve the same result in SQL 2000

    SELECT name, filename AS current_file_location
    FROM master..sysdatabases

    Reply
  • Thank you for this. With your reference i was able to do this

    select [Database], [1], [2]
    from
    (SELECT d.name [Database], mf.file_id,
    mf.physical_name AS [Path]
    FROM sys.master_files mf, sys.databases d
    where d.database_id = mf.database_id) AS st
    PIVOT
    (
    MAX([Path]) FOR file_id IN ([1], [2])
    ) AS PT

    To show database files based on file_id.

    Cheers,

    Epikto

    Reply
  • Thank you for this. With your query i was able to do the following:

    select [Database], [1], [2]
    from
    (SELECT d.name [Database], mf.file_id,
    mf.physical_name AS [Path]
    FROM sys.master_files mf, sys.databases d
    where d.database_id = mf.database_id) AS st
    PIVOT
    (
    MAX([Path]) FOR file_id IN ([1], [2])
    ) AS PT

    Reply
    • Hello Sir,

      Thank for the query, which is i am looking for. In the same query how I can get only the file path without the filenames.

      I am new to SQL server DBA, trying to find a way to get all the data and log file location(without the file names) for all the DBs, that will be used in DB restore process for dev & test enviroment.

      Thank you

      Reply
  • Dewesh Pushkar
    June 6, 2012 9:36 am

    Hi Sir,
    How can I delete any file which is in hard disk using sql.

    Reply
    • You can use xp_cmdshell system procedure with DEL command. But why do you want to do this from sql?

      Reply
      • Vijay Anand Madhuranayagam
        August 7, 2012 11:45 am

        When we auto-schedule the backups we may need to delete the files from a particular folder. As per your suggestion we can use xp_cmdshell to do that.

  • Vineeth george
    January 12, 2013 5:59 pm

    I am not able to delete nfsmini.mdf
    please tell me is there any way to delete it!!!

    Reply
  • Great blog. As a beginner sql server admin, I have benefitted much from it. Thought I contribute a little back.

    For SQL Server 2000: Get both data and log files.
    SELECT name, filename
    FROM master..sysaltfiles
    ORDER BY name

    Reply
  • Hi Sir…
    How can i get the location of sql server log file… N even i want to known sql server log file format….

    Reply
    • U can get the location of log files by executing below query:

      select a.name, b.name as ‘Logical filename’, b.filename from sys.sysdatabases a
      inner join sys.sysaltfiles b
      on a.dbid = b.dbid where fileid = 2

      Reply
  • Hi pinal i am the regular reader of yours page.
    can u let me know what is the script to find the data & log files path in sqlserver2000

    Reply
  • Hello Sir
    I have a monitoring syatem, in which data stored in sql from. But I don’t know the location of data sotre.How I can find location of data? Please help mw.

    Reply
  • This seems more use:
    SELECT d.name, f.name, f.physical_name
    FROM sys.databases d JOIN sys.master_files f ON (d.database_id = f.database_id)
    order by 1,2

    Reply
  • Thanks for this post — exactly what I was looking for.

    Jobi

    Reply

Leave a Reply

Menu