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:
Reference: Tejas Shah (www.SQLYoga.com)
