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.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

36 thoughts on “SQL SERVER – Find Current Location of Data and Log File of All the Database

  1. 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

  2. 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.

  3. 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

  4. Pingback: SQL SERVER – Find Most Active Database in SQL Server – DMV dm_io_virtual_file_stats Journey to SQL Authority with Pinal Dave

  5. 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?

  6. 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

  7. Pingback: SQL SERVER – Finding Location of Log File when Primary Datafile is Crashed Journey to SQLAuthority

  8. 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

  9. 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

    • 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

  10. 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

    • 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

  11. 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

  12. Pingback: SQL SERVER – Weekly Series – Memory Lane – #017 | SQL Server Journey with SQL Authority

  13. 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.

  14. 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

  15. I want to send mail with attachments, But my file reside on another machine how to provide path of that file in sql mail send querry

  16. 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?

  17. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s