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
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
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?
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.
Hi,
I needed datafiles sapce usage percentage
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.
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!
hi sir
how to find log file
pls reply me
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!
I appreciate your kind words.
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
Thanks Pradeep.
You just saved me a heap of trouble, thank you!!
Thank you so much for sharing such a kind of information.
Need same information for Azure managed instance and Azure sql. Please help to provide some info.