I encountered the situation recently where I needed to find the size of the log file. When I tried to find the script by using Search@SQLAuthority.com I was not able to find the script at all. Here is the script, if you remove the WHERE condition you will find the result for all the databases.
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'AdventureWorks'
GO
Reference: Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
Wonderful article Pinal.
Thanks for the script, any way to add the last backup of DB and log files to it?
Though sys.master_files has tons more info but I still like dbcc sqlperf (‘logspace’) if its only about getting the free log space.
Hi, Pinal
Seems sp_helpdb ‘AdventureWorks’ does the same job
Thanks for the script.
Hello Yusuf,
You are right, there is no better way to get log size of all databases. There are few other methos to know the size and free space in database files but there is no simple alternate for the output of sqlperf (‘logspace’).
Regards,
Pinal Dave
Thank you. This saves lot of my time. So far we are using DBCC SHOWFILESTATS.
Just realized. The above statement returns only allocated space of each file. But DBCC SHOWFILESTATS returns total the used extents.
Hi Pinal,
What about sp_databases. Is it not the same?
Thanks
Vivek