Blog Detail

SQL SERVER: Find Current Location of Log File(s) with File Size of All the Database
20 Apr 2015
Tejas Shah
/Project/ImageURL/SQL_Yoga_Find_Current_Location_of_Log_File(s)_of_All_the_Database_thumb[14].png
As we are mostly engaged in SQL Assignments, we come to the place where we found that there are lots of ldf files which occupies lots of the space on the DISK. To continue my work, I have to make a room on the same DISK. I found that there are lots of log files (LDF) on the disk which occupies more than 1 TB (1024 GB). This is the development server and I found and confirmed that we can have “Recovery Option” as “SIMPLE” too. The tricky part over here is, these databases are already created and Log file names are not same as database name. So, first of all I need to find out the database name to which I can make Recovery Option as Simple. Finally, I got following query which helps me to continue my assignment as follows: SELECT    d.name As DatabaseName,
        m.name As FileName,
        m.physical_name As PhysicalFileName,
        (m.size * 8.0) / 1024 / 1024 As SizeInGB
FROM sys.master_files m
INNER JOIN sys.databases d ON d.database_id = m.database_id 
WHERE m.Type = 1 
It will give us the list as follows:

Tags

SQL Log file size SQL Server SQL Tips Current location of log files

About me

User

Tejas Shah

Microsoft Certified Professional Expert. I have 14+ years of experience in Design and Develop 100+ .Net applications using Asp.Net, C#, Asp.Net Core, VB.Net, SQL Server, MVC, AngularJS, JavaScript, Azure, HTML5 and CSS3.

Read More

Categories

Recent Post