That information does not exist, only the current size.
Finding initial size of database datafiles
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
3 additional answers
Sort by: Most helpful
-
LiHong-MSFT 10,051 Reputation points
2022-04-08T04:07:51.04+00:00 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 LabelBest 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. -
Ronen Ariely 15,191 Reputation points
2022-04-07T15:17:27.853+00:00 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
-
Olaf Helper 44,501 Reputation points
2022-04-08T06:01:31.22+00:00 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?