none
data and log file size RRS feed

  • Question

  • Using SQL Server 2014 standard edition.

    On database properties -> Files -> size
    I set up the data and log file size larger then it was originally. Now, how do I figure out what is the actual data size?

    I need to know so I can estimate additional disc space required.

    Thanks

    Thursday, July 11, 2019 11:10 PM

Answers

  • Try below query,

    Use 'DBName

    Go

    if convert(varchar(20),SERVERPROPERTY('productversion')) like '8%' SELECT [name], fileid, filename, [size]/128.0 AS 'Total Size in MB', [size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Used Space In MB', (100-((([size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0)/([size]/128.0))*100.0)) AS 'percentage Used' FROM sysfiles else SELECT @@servername as 'ServerName',db_name() as DBName,[name], file_id, physical_name, [size]/128 AS 'Total Size in MB', [size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Used Space In MB', (100-((([size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0)/([size]/128.0))*100.0)) AS 'percentage Used' FROM sys.database_files go



    Regards;
    Vivek Janakiraman
    ----------------------------
    My Blog
    My Linkedin Page
    ----------------------------

    • Marked as answer by kvd123 Friday, July 12, 2019 2:06 AM
    Friday, July 12, 2019 12:37 AM

All replies

  • Try below query,

    Use 'DBName

    Go

    if convert(varchar(20),SERVERPROPERTY('productversion')) like '8%' SELECT [name], fileid, filename, [size]/128.0 AS 'Total Size in MB', [size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Used Space In MB', (100-((([size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0)/([size]/128.0))*100.0)) AS 'percentage Used' FROM sysfiles else SELECT @@servername as 'ServerName',db_name() as DBName,[name], file_id, physical_name, [size]/128 AS 'Total Size in MB', [size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Used Space In MB', (100-((([size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0)/([size]/128.0))*100.0)) AS 'percentage Used' FROM sys.database_files go



    Regards;
    Vivek Janakiraman
    ----------------------------
    My Blog
    My Linkedin Page
    ----------------------------

    • Marked as answer by kvd123 Friday, July 12, 2019 2:06 AM
    Friday, July 12, 2019 12:37 AM
  • Hi kvd123 ,

    I suppose you have setup an initial file size of DB files.

    This means that actual size of file will be equal to or more than this set value. 

    But the actual occupied space could be less or more than this. you can find it by running below queries

    sp_spaceused

    SELECT df.file_id AS File_Id,
        df.name as fileName,
       df.physical_name,
       CAST(df.size AS FLOAT) * 8.00 * 1024 AS Size_On_Disk_Bytes,
       ROUND((CAST(df.size AS FLOAT) * 8.00/1024)/1024,3) AS Actual_File_Size,
       ROUND(CAST((df.size) AS FLOAT)/128,2) AS Reserved_MB,
       ROUND(CAST((FILEPROPERTY(df.name,'SpaceUsed')) AS FLOAT)/128,2) AS Used_MB,
       ROUND((CAST((df.size) AS FLOAT)/128)-(CAST((FILEPROPERTY(df.name,'SpaceUsed'))AS FLOAT)/128),2) AS Free_MB
    FROM sys.database_files df 
        LEFT JOIN sys.filegroups fg
        ON df.data_space_id = fg.data_space_id

    Thanks

    please mark as answer if it resolves your query

    Friday, July 12, 2019 12:46 AM
  • Great! Thank You Vivek and A2605. Both queries are giving same result.

    Friday, July 12, 2019 2:07 AM