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 (http://blog.SQLAuthority.com)

About these ads

45 thoughts on “SQL SERVER – Find the Size of Database File – Find the Size of Log File

    • No it doesnt…..

      SP_Help doesnt give you any detail of the filegroups you have in your databases, but from this view you already get it (in case you use it).

  1. 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

  2. One Suggestion , you can get a Db,Table, Size calculator utility from your trainees.

    The user needs to give input of the no. of types of columns and they get the estimated size result….

    It will be a very good utility for many people …

  3. Hi Pinal,

    Can you please provide me the script to find the free space of all the databases in sql server 2005?

    Thanks in advance

  4. Hi,

    What is the recommended database file size in MS SQL 2005? as of now, one of my production database having one data file and size is 70 GB. And it is set to auto grow.

    will it cause any performance issue if it continiously grow? is there any recommended size we can restrict and create secondary file once it is reached that size?

    Can you give some tips to usage on data file size in MS SQL 2005 database? Thanks in advance!

    regards,
    Ratna Kumar.

  5. Hi,

    This gives the initial size not the current size of the log file.
    If you are looking for the initial size, isnt it better to right click, properties, select “file” and look for the initial size in the gui?

    What is the difference in using the Script?

    – Shiyam

  6. Hi Shiyam,

    You are right that you can Right Click and know the file size,
    but think of if you have 76 Databases on the instance and you want to send a report to your manager then would you click on each of the databases and copy /paste one by one? Obviously NOT,, then the the benefit of the script comes to play… just run the script and in few sec you have report for every database even for 347 databases on one instance..
    thanks Pinal,,, lots of SQL Server experts and instructors (even officially from Microsoft) suggest to go to MS website or their blogs, but my first choice is yours because I find answer which is very too the point and some time exact what I want. Great work.

    Shoaib
    from Canada

  7. It’s not clear in my earlier comment, but the period at the end of my sentence is supposed to be a decimal point. Let me try again: “(size*8)/1024.0″.

  8. And if you alter it just a little bit, you can have them summed by database:

    SELECT DB_NAME(database_id) AS DatabaseName,
    SUM((size*8)/1024) SizeMB
    FROM sys.master_files
    group by DB_NAME(database_id)
    order by DB_NAME(database_id)

  9. This list the files and sizes for all attached databases on server …

    SELECT db.name AS [DB Name]
    ,dbf.[physical_name] AS [File Name]
    ,dbf.[size] AS [File Size]
    ,db.[create_date] AS [Create Date]
    ,ss.[Last User Event]
    ,ss.[Last User Update]
    FROM sys.[databases] AS db
    LEFT OUTER JOIN sys.[master_files] AS dbf ON [db].[database_id] = [dbf].[database_id]
    LEFT OUTER JOIN (SELECT database_id,max(last_user_update) AS [Last User Update], ISNULL(ISNULL(max(last_user_update),max([last_user_seek])),max([last_user_lookup])) AS [Last User Event]
    FROM sys.[dm_db_index_usage_stats] GROUP BY database_id) AS ss ON ss.[database_id] = db.[database_id]
    ORDER BY db.[name],dbf.[physical_name]

    • This script is great. I am new to learning DBA role. Is the output for file size reflected in Kilobytes or Megabytes?

  10. we have sap server on msssql 2005 , 4 datafile (DATA1,DATA2…DATA4) with 26,880 MB each , unrestricted growth by 60 MB .Free space on Disk is 67,224 MB.Outof four data file , Three file status show ‘NO FREE SPACE IN FILE” and one shows autogrown with enough space to grow.
    Free space on these datafiles shows (PCT_FREE) – 0.

    Please advice why this message ,as our all data file are – autoextend enabled.
    what would be the max. size of datafile .whats the work around for this….

    thanks

  11. CREATE TABLE #f
    (
    name VARCHAR(255),
    rows INT ,
    reserved varchar(255),
    data varchar(255),
    index_size varchar(255),
    unused varchar(255)
    )

    INSERT into #f

    EXEC sp_MSForEachtable ‘sp_spaceused ”?”’

    SELECT * FROM #f

    DROP TABLE #f
    // Hope this will help

  12. Please prefer to use either sys.database_files or sys.sysfiles to get the db sizes. Try to avoid using sys.master_files dmv..

    To answer why .. See the results below ..

    USE tempdb
    GO

    SELECT SUM(SIZE)/128 FROM SYS.DATABASE_FILES;
    — 29754 MB

    SELECT SUM(SIZE)/128 FROM SYS.master_files
    WHERE database_id = DB_ID(‘TEMPDB’)
    — 18432 MB

  13. Hi Pinal,

    Putting new steps towards DBA very good script, Thank You.

    Just quick question, The ‘growth’ field in ‘sys.master_files’ table, does it stamp date or time as well, like growth of the TestDatabase since this time ??

    Thanks

  14. Pingback: SQL SERVER – Weekly Series – Memory Lane – #015 « SQL Server Journey with SQL Authority

  15. Hi Pinal-
    Actually I just wanted to know only the user database information. For that I just tweak your query a bit.
    SELECT Distinct DB.name As DatabaseName,
    DB.recovery_model,
    mf.name as logical_name,
    mf.physical_name,
    (size*8)/1024 SizeMB
    FROM sys.master_files mf
    join sys.databases DB
    on mf.database_id = DB.database_id
    WHERE mf.database_id > 4
    The reason I put WHERE mf.database_id > 4 is to get only user databases list.First 4 database_id’s are for system databases.

  16. Recovery Model: Simple
    DB Size: 70 GB
    Server Free Space: 145 GB
    Every evening when we leave office LOG File is 20-30 GB or Less. But at midnight the log file size grows and takes all free space. The applications stops. In morning we shrink the log file down to 1 MB. All the day it works ok. We don’t run any process at midnight. Please advice how to handle the situation

  17. I have list of filepaths in a column &
    i want to select filepath & size of physical file
    like
    List
    1.jpg
    2.jpg
    in
    D:/Project/P1 folder
    i want to display file name, extension, FileExist and size From sql query

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s