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 (https://blog.sqlauthority.com)
57 Comments. Leave new
hey its really helpful thank you very much
Thank you Pinal! That’s very helpful – but if you have large initial file size configured for your database (which is best practice), your script actually shows the initial file size and not the actual size used by the database. Can you shed light on how to get the actual disk space size of a database and not the disk space reserved for the database (because of the large initial file size)? There is the sp_spaceused command, the FILEPROPERTY(name, ‘SpaceUsed’) command – but they are very confusing ….. Wish somebody can explain them … Thank you so much again.
Thanks and Good Article Pinal..
Amol – I am glad that you liked it.
Hi Pinal , Is there way to find out log file size that taken by a table? Not total log file size of database. I ‘d like find out a table that how many long file size it takes when performing daily basic TRUNCATE and Bulk Copy(From C# to DB without TABLOCK option).
I don’t think there is any documented way. fn_dblog can show log records and has column for size and objectID. Can you try that?
IF OBJECT_ID(‘tempdb..#spacetable’) IS NOT NULL
DROP TABLE tempdb..#spacetable
create table #spacetable
(
database_name varchar(50) ,
total_size_data int,
space_util_data int,
space_data_left int,
percent_fill_data float,
total_size_data_log int,
space_util_log int,
space_log_left int,
percent_fill_log char(50),
[total db size] int,
[total size used] int,
[total size left] int
)
insert into #spacetable
EXECUTE master.sys.sp_MSforeachdb ‘USE [?];
select x.[DATABASE NAME],x.[total size data],x.[space util],x.[total size data]-x.[space util] [space left data],
x.[percent fill],y.[total size log],y.[space util],
y.[total size log]-y.[space util] [space left log],y.[percent fill],
y.[total size log]+x.[total size data] ”total db size”
,x.[space util]+y.[space util] ”total size used”,
(y.[total size log]+x.[total size data])-(y.[space util]+x.[space util]) ”total size left”
from (select DB_NAME() ”DATABASE NAME”,
sum(size*8/1024) ”total size data”,sum(FILEPROPERTY(name,”SpaceUsed”)*8/1024) ”space util”
,case when sum(size*8/1024)=0 then ”divide by zero” else
substring(cast((sum(FILEPROPERTY(name,”SpaceUsed”))*1.0*100/sum(size)) as CHAR(50)),1,6) end ”percent fill”
from sys.master_files where database_id=DB_ID(DB_NAME()) and type=0
group by type_desc ) as x ,
(select
sum(size*8/1024) ”total size log”,sum(FILEPROPERTY(name,”SpaceUsed”)*8/1024) ”space util”
,case when sum(size*8/1024)=0 then ”divide by zero” else
substring(cast((sum(FILEPROPERTY(name,”SpaceUsed”))*1.0*100/sum(size)) as CHAR(50)),1,6) end ”percent fill”
from sys.master_files where database_id=DB_ID(DB_NAME()) and type=1
group by type_desc )y’
select * from #spacetable
order by database_name
drop table #spacetable
https://gallery.technet.microsoft.com/SIZE-OF-ALL-DATABASES-IN-0337f6d5
Great. thanks for your comment.
I’m trying to script out sp_attach_db, I need the database name just once and then the data and log files after using dynamic sql. I tried this using a subquery in the select list but it blew up because I have databases with more then one data file. If anyone could help me with this I would greatly appreciate it.
In using the script from the following location I’d like to add “current_file_location” to the output. Anyone know how that script can be modified to get the file location as well? The following is what I have used to collect DB Names and file locations. Any help would be greatly appreciated. Thanks in advance.
SELECT name, physical_name AS current_file_location
FROM sys.master_files
https://gallery.technet.microsoft.com/SIZE-OF-ALL-DATABASES-IN-0337f6d5
Hi, I don’t know if these comments are monitored, but I have been using your site for a few years now, and I’ve always found the information in it to be clear, relevant, and most importantly, absolutely spot on! This particular entry was no different. It produced exactly what I needed.
Keep up the great work!
I’m a fan – and thank you for having this site :-)
Carrie P.
Hi Carrie, I read everything you said and you made my day!