Finding initial size of database datafiles

Question

Hi everybody,

Is there a way to determine the initial size of the data files of databases in MS SQL Server? I mean when the data files size is defined with "create database" statement or a backup was restored from another server, or the size of datafiles of the model database have changed after the creation of the database. I know some difficult, indirect and not out of the box approach for the log file but not data files. It may also be determined through current size and history records of autogrowth, but I guess with the restart of SQL Server service, all the autogrowth history will be deleted. I guess the answer is No. It would be very useful for me if there is a solution. I appreciate your help, thanks.

Best regards,
Ali

Answer 1

That information does not exist, only the current size.

Answer 2

Hi @amomen-8749
As far as I know, the size you originally provided to the database file is not stored anywhere. I think the "initial size" that you can see when you right-click the database and then click properties and then click file is actually the current size.
Basically, database initial size is just a concept, from a DBA perspective there is no such thing as “initial size" there is only one property visible for a DBA and that is the current size or actual size.
Please refer to this document for more details: SQL Server: Misleading Database Initial Size Label

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Is there a way to determine the initial size of the data files of databases in MS SQL Server?

It's nowhere stored.
If you open database properties in SSMS, it show's a column name "initial size", but that's a known bug in SSMS, it's the current size.

Why do you what to know that?

Answer 4

Hi,

Is there a way to determine the initial size of the data files of databases in MS SQL Server?

Here are some options to get the size of the files of the database

/**************** get database files size ***********/

--> using sys.master_files
use master
GO
SELECT DB_NAME(database_id) AS database_name, 
    type_desc, 
    name AS FileName, 
    size/128.0 AS CurrentSizeMB
FROM sys.master_files
GO

-- Using sys.database_files : connect to the specific database first
USE AdventureWorks2019
GO
SELECT DB_NAME() AS DbName, 
    name AS FileName, 
    type_desc,
    size/128.0 AS CurrentSizeMB,  
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1);
GO

-- Use sp_spaceused
USE AdventureWorks2019;  
GO  
EXEC sp_spaceused;  
GO 

-- using sp_helpdb
EXEC sp_helpdb N'AdventureWorks2019';
GO

-- using sp_databases
EXEC sp_databases 
GO