SQL Server: Misleading Database Initial Size Label

  • Article

Introduction

In SQL Server every database has a property Initial Size (MB) which can be seen in database properties. It's the size that is specified during creation of the database. You can either explicitly specify this in the CREATE DATABASE statement, or you can have SQL Server implicitly copy it from the model database. Please refer to the CREATE DATABASE topic in BOL.

Well, we all know this, but the question is where exactly this information is stored, can I change Initial size property after database creation, can we shrink a database below its initial size.

In my view, the "Initial size" label that you see when you look at the file properties in SSMS is itself misleading, let us examine it.

Performing the test

Create the database as below.

USE master

GO

CREATE DATABASE MyDB

ON

( ``NAME = MyDB_dat,

FILENAME = ``'c:\MyDB.mdf'``,

SIZE = 10MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB )

LOG ``ON

( ``NAME = MyDB_log,

FILENAME = ``'c:\MyDB.ldf'``,

SIZE = 3MB,

MAXSIZE = 25MB,

FILEGROWTH = 5MB )

Now check the initial size of mydb (Right click on mydb ->Properties - > files)

Data file has initial size of 10 mb and log file 3 mb as specified in our CREATE DATABASE statement, now alter the database file as below.

ALTER DATABASE mydb ``MODIFY FILE ( NAME = N``'MyDB_dat'``, ``SIZE = 100MB )

Again check the initial size of mydb(Right click on mydb ->Properties - > files)

Data file initial file size is changed to 100 MB which is current size not initial size, from the above result, it is clear that there is no such thing as “initial size" there is only one property visible and that is the actual size(current size).

Even the "Initial size" property in SSMS just shows actual size, not the initial size.

Now let me shrink the database and examine to what size it will shrink.

  DBCC SHRINKDATABASE(  'MyDB'  )

From above result we can clearly make out database has shrank to the size specified during its creation which is 10 MB.

How does database know its initial size?

Well how come that DBCC SHRINKDATABASE knows the initial size then? Even after you have changed the size.

The first page of a database file is the file header page,and it stores information of various properties.

 

Note

By default, the output of DBCC PAGE is sent to the errorlog. If you want the output to come back to your current connection, need to turn on trace flag 3604, DBCC TRACEON(3604), refer how to use DBCC Page.

DBCC Page would return various properties, we need to concentrate on Size, MaxSize & MinSize, at the creation of database all three properties are set as per the sizes you specified, in our case Size is 10 mb, MaxSize is 100 Mb and MinSize is also 10 MB which we had specified during database creation.
In the above result all three sizes are in terms of data pages, Size & MinSize is 1280 pages corresponds to 10 MB and MaxSize is 12800 which is 100 MB.

Calculation: 1280 = (1280*8)/1024 = 10 MB 1280 - total no of pages. 8 - 1 page is 8 KB 1024 - 1MB is 1024 KB

Let me alter the file size and see what will happen to these three values.

ALTER DATABASE mydb ``MODIFY FILE ( NAME = N``'MyDB_dat'``, ``SIZE = 100MB )

as you can see that size property has changed to reflect the new size which is 12800 data pages (100 mb). However MinSize still holds the initial size and it is the minimum size to which DBCC SHRINKDATABASE can go to.

Can I shrink database below Initial size?

The question is can I change this MinSize. DBCC SHRINKDATABASE command will shrink database to a size which is specified during database creation which is MinSize, but DBCC SHRINKFILE reduces the size of a file to smaller than its originally created size. The minimum file size for the file is then reset to the newly specified size. Refer DBCC shrinkfile BOL topic.

Let us check that.

DBCC SHRINKFILE(``'MyDB_dat' , 2)

I try and shrink data file to 2 mb and DBCC PAGE MinSize should reflect this new MinSize.
   

MinSize has new value now, and henceforth this value will be the reference point for Database shrink, let me modify data file to 100 mb and then shrink database and will see to what size database will shrink.

ALTER DATABASE  mydb MODIFY  FILE ( NAME  = N'MyDB_dat', SIZE  = 100MB )
GO
DBCC SHRINKDATABASE('mydb')

DBCC SHRINKDATABASE has shrank data files to 2 mb, that means we have successfully changed MinSize property to new value.

Caution
If you are shrinking files outside of an emergency event, you need to change the way you're doing things. Please check these articles.
Also this article uses undocumented command like DBCC PAGE. Undocumented command like DBCC PAGE is safe to use in production but any support related to this command cannot be claimed from Microsoft.It also means that any change can be made to this command without officially notifying end user so unexpected results might come if used on same environemnt after some time  . Although their usage is safe, keep in mind to use it in test environments only to avoid any impact to production processes.

Conclusion

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.
Minimum size is stored in Page header and DBCC SHRINKDATABASE refers to that MinSize property when it is shrinking database if actual data size is less than MinSize.
DBCC SHRINKFILE can shrink files to less than its initial size.

References

I want to thank Edward Dortland for demystifying DBCC PAGE and describing Page Header Properties.