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)
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
[...] 19, 2010 by pinaldave Few days ago, I wrote about SQL SERVER – Find Current Location of Data and Log File of All the Database. There was very interesting conversation in comments by blog readers. Blog reader and SQL Expert [...]
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
http://beyondrelational.com/blogs/madhivanan/archive/2009/05/07/how-to-find-size-of-backup-files-in-a-folder.aspx
To achieve the same result in SQL 2000
SELECT name, filename AS current_file_location
FROM master..sysdatabases
[...] quickly searched on the Internet and I ended up on a very interesting blog post written by me SQL SERVER – Find Current Location of Data and Log File of All the Database. I have previously written how I can find the location of all the files of the [...]