SQL SERVER – Find the Size of Database File – Find the Size of Log File

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)

Solarwinds
, ,
Previous Post
SQL SERVER – SQL Server 2008 R2 Update for Developers Training Kit
Next Post
SQL SERVER – Brief Note about StreamInsight – What is StreamInsight

Related Posts

57 Comments. Leave new

  • Swapnil Raorane.
    December 3, 2014 12:29 pm

    hey its really helpful thank you very much

    Reply
  • 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.

    Reply
  • Thanks and Good Article Pinal..

    Reply
  • 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).

    Reply
    • 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?

      Reply
  • 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

    Reply
  • 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.

    Reply
  • 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

    Reply
  • 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.

    Reply

Leave a Reply

Menu