Let's find out the relationships between 'unallocated space', 'available free space' and 'autogrowth'.
When a new database is created the initial size is always 2 MB with 'autogrowth' factor of 1 MB, unrestricted growth and 10% growth, unrestricted growth for log file of 1MB.
Refer to 'mssql-mar-2011-1.jpg','mssql-mar-2011-2.jpg'
Thus, the 'unallocated space' or 'available free space' is around 29% of 3MB (combination of data file and log file).
And if we try to increase the 'autogrowth' factor by 100MB, unrestricted growth, it will work as well.
Refer to 'mssql-mar-2011-3.jpg'
So, if we purposely increase the initial size to 500 MB , it will work as well, thus the 'Unallocated space' or 'available free space' will increase.
Refer to 'mssql-mar-2011-5.jpg','mssql-mar-2011-6.jpg'
Which means that 'unallocated space' is the same as 'available free space' and its value comes from manually allocation (by resetting the value for 'initial size' or by 'autogrowth').
Similar settings and alteration can be applied to existing database as well.
Thus, if we perform shrinking of the database. It should compress the file (by getting rid of unallocated space) and to depend on 'autogrowth' when additional size is needed during run-time. It will reset the initial size, which means that after you have compressed the database, the 'initial size' is the size of existing database.
So, this is how 'shrink database' work, just like zipping without data lost.
This also explains why the database size can be more than 4GB, but it can still be working for some cases of MSSQL express or community edition.
Refer to 'MSSQL 2005 Express Database Size (Observation #1)'
Because the size may not represent the actual space used, at least not before shrinking(compressing). If you do not shrink the database, you can make use of the indicator 'reserved' and 'data' for actual sizes.
where
The usefulness of such indicators which are related to database size is plenty.
For instance, when the following exception is encountered.
"Could not allocate new page for database [database name] . There are no more pages available in filegroup PRIMARY. Space can be created by dropping objects."
These are what needed to be done in order to find out the exact cause.
1.) Check if 'unallocated space' or 'available free space' is zero or very small value.
2.) and check if 'autogrowth' is set to unrestricted.
if both of the above conditions are true, then very high chances it is due to insufficient disk space at the physical drive.
So, check the space of the physical drive.
When a new database is created the initial size is always 2 MB with 'autogrowth' factor of 1 MB, unrestricted growth and 10% growth, unrestricted growth for log file of 1MB.
Refer to 'mssql-mar-2011-1.jpg','mssql-mar-2011-2.jpg'
Thus, the 'unallocated space' or 'available free space' is around 29% of 3MB (combination of data file and log file).
And if we try to increase the 'autogrowth' factor by 100MB, unrestricted growth, it will work as well.
Refer to 'mssql-mar-2011-3.jpg'
So, if we purposely increase the initial size to 500 MB , it will work as well, thus the 'Unallocated space' or 'available free space' will increase.
Refer to 'mssql-mar-2011-5.jpg','mssql-mar-2011-6.jpg'
Which means that 'unallocated space' is the same as 'available free space' and its value comes from manually allocation (by resetting the value for 'initial size' or by 'autogrowth').
Similar settings and alteration can be applied to existing database as well.
Thus, if we perform shrinking of the database. It should compress the file (by getting rid of unallocated space) and to depend on 'autogrowth' when additional size is needed during run-time. It will reset the initial size, which means that after you have compressed the database, the 'initial size' is the size of existing database.
So, this is how 'shrink database' work, just like zipping without data lost.
This also explains why the database size can be more than 4GB, but it can still be working for some cases of MSSQL express or community edition.
Refer to 'MSSQL 2005 Express Database Size (Observation #1)'
Because the size may not represent the actual space used, at least not before shrinking(compressing). If you do not shrink the database, you can make use of the indicator 'reserved' and 'data' for actual sizes.
where
- 'reserved'="Total amount of space allocated by objects in the database."
- 'data'="Total amount of space used by data."
The usefulness of such indicators which are related to database size is plenty.
For instance, when the following exception is encountered.
"Could not allocate new page for database [database name] . There are no more pages available in filegroup PRIMARY. Space can be created by dropping objects."
These are what needed to be done in order to find out the exact cause.
1.) Check if 'unallocated space' or 'available free space' is zero or very small value.
2.) and check if 'autogrowth' is set to unrestricted.
if both of the above conditions are true, then very high chances it is due to insufficient disk space at the physical drive.
So, check the space of the physical drive.
Comments