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 (https://blog.sqlauthority.com)
46 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
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.
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
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!
Thanks a lot. Much more than what I want or what I was seeking for.
Exactly what I was looking for. Thank you very much.
Matthew – I am glad that you were able to find it.
Give Solutin For Reduce size of Log File:
We can also find the location of data file and log file by executing sp_helpfile.
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?
How did you confirm the reader did not return data?
how to do it in vb.net? i try use the reader but it faile to return any result
Good Morning, Reading this website is a real pleasure, Many thanks !
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
Make use of this post
To achieve the same result in SQL 2000
SELECT name, filename AS current_file_location
FROM master..sysdatabases
Thank you :)
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
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
Hi Sir,
How can I delete any file which is in hard disk using sql.
You can use xp_cmdshell system procedure with DEL command. But why do you want to do this from sql?
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.
I am not able to delete nfsmini.mdf
please tell me is there any way to delete it!!!
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
Hi Sir…
How can i get the location of sql server log file… N even i want to known sql server log file format….
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
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
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
Thanks for this post — exactly what I was looking for.
Jobi
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