Happy to inform all my blog readers this particular post has won me GOLD in TechNet Guru Award under SQL Server General and Database Engine Technical Guru – January 2014.
Visit TechNet Guru Awards – January 2014 for winner list
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
CREATE DATABASE
Well, we all know this, but the question is where exactly this information is stored, can I change Initial size property post its 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 masterGOCREATE
DATABASEMyDBON( 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 DATABASEmydb 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 shrinked 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 DATABASEmydb 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
Let us check that.
DBCC SHRINKFILE('MyDB_dat'
, 2)
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.
ALTERDATABASE
mydb MODIFY
FILE ( NAME
= N'MyDB_dat',
SIZE
= 100MB )GODBCC SHRINKDATABASE('mydb')
DBCC SHRINKDATABASE has shrinked 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. Refer below articles. |
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 its shrink database if actual data size is less than MinSize.
DBCC SHRINKFILE can shrink files to less than its initial size.
sqlerrors 